-
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;
/
-
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
-
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?
-
Are you executing shell as well as sqlplus with same user account or different users?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|