DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: procedure call from SQL*Plus in Korn Shell Script

  1. #1
    Join Date
    Oct 2006
    Posts
    1

    procedure call from SQL*Plus in Korn Shell Script

    I am trying to excute a procedure from sqlplus, truncate_audit_table. I doesn't execute. If I do it manually
    it executes. I have tried multiple ways, as below. This is inside a Korn Shell script that creates the procedure
    and counts the aud$ table. All that works, but does not truncate the aud$ table. It does not do it, no error, just
    passes it by. Does anybody know why?



    ${ORACLE_HOME}/bin/sqlplus -s / << EOF | tee -a ${LOG}
    set feedback on
    @/backup/oracle/orcl/audit/truncate_audit_table.sql
    select count(*) from sys.aud$;
    exec truncate_audit_table;
    exit
    EOF


    ${ORACLE_HOME}/bin/sqlplus -s / << EOF | tee -a ${LOG}
    set feedback on
    @/backup/oracle/orcl/audit/truncate_audit_table.sql
    select count(*) from sys.aud$;
    begin
    truncate_audit_table;
    end;
    exit
    EOF

    This is the procedure that is being called:

    create procedure truncate_audit_table
    as
    begin
    execute immediate 'truncate table sys.aud$';
    end;
    /

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    Put all things sql in the sql file and run.
    BTW why do you need the count(*) for?

    Also read this article by Pete Finnigan
    http://www.petefinnigan.com/weblog/a...s/00000003.htm

    -dharma

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    In the second example there is no slash after the PL/SQL block. I'm not sure what the two versions represent though. Are you saying neither works?

  4. #4
    Join Date
    Nov 2006
    Posts
    6
    Are you executing shell as well as sqlplus with same user account or different users?
    Jayg

    Fun Programming with Oracle & Visual C++, Author of www.lightsql.com.

Posting Permissions

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


Click Here to Expand Forum to Full Width