Home » SQL & PL/SQL » SQL & PL/SQL » procedure call from SQL*Plus in Korn Shell script
procedure call from SQL*Plus in Korn Shell script [message #199889] Thu, 26 October 2006 10:43 Go to next message
djehres
Messages: 3
Registered: June 2005
Location: Garland, TX
Junior Member

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 scripts 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;
/
Re: procedure call from SQL*Plus in Korn Shell script [message #199897 is a reply to message #199889] Thu, 26 October 2006 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>select count(*) from sys.aud$;select count(*) from sys.aud$;
should be sys.aud\$
The dollar sign character is a meta-character to the shell & must be escaped
Re: procedure call from SQL*Plus in Korn Shell script [message #199901 is a reply to message #199889] Thu, 26 October 2006 11:43 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
In addition to what anacedent mentioned, you should also change SYS to SYSTEM:

create procedure truncate_audit_table
as
begin
execute immediate 'truncate table system.aud$';
end;
/
Previous Topic: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process
Next Topic: Oracle Stored Procedure - Must Be Delcared?
Goto Forum:
  


Current Time: Sun Dec 04 04:47:10 CST 2016

Total time taken to generate the page: 0.09790 seconds