Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    10

    Unanswered: How to execute a procedure or an anonymous pl?sql bloc in a batch file

    Hi,

    I prepared an anonymous PL/Sql bloc and i would like to execute it
    into a batch file (.sh) from unix.

    in a .sh file, i wrote

    export ORACLE_SID=SID
    sqlplus -s username/password <<!EOF
    declare
    cursor cur_tables is
    select table_name from user_tables;
    nom_table varchar2(30);
    req varchar2(255);
    begin
    open cur_tables;
    loop
    fetch cur_tables into nom_table;
    exit when cur_tables%NOTFOUND;
    EXECUTE IMMEDIATE 'analyze table ' || nom_table || ' compute statistics';
    end loop;
    close cur_tables;

    when i execute the .sh file, i received no errors but the statistics are not updated in my schema.

    any help is appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to execute a procedure or an anonymous pl?sql bloc in a batch file

    Your PL/SQL block is incomplete. It needs:

    END; -- to end it
    / -- to run it

    BTW, why go through all the pain of declaring, opening, fetching, testing NOTFOUND and closing cursor when this does the same job? :-

    BEGIN
    for r in (select table_name from user_tables) loop
    EXECUTE IMMEDIATE 'analyze table ' || r.table_name || ' compute statistics';
    end loop;
    END;
    /

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Smile

    It's missing also th eclosing part of this <<!EOF

    Add ! as the last lane fo your shell script.


    Hope that helps,

    clio_usa
    OCP - DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •