Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to pass a procedure with arguments to sqlplus script and execute it

Sanjeev ChauhanOct 5 2009 — edited Oct 5 2009
I'd like to pass a procedure as a parameter to a sqlplus file and execute the procedure.

The sqlplus file, test.sql, looks like:
set serveroutput on
set verify off
set feedback off
set echo off

spool output.txt
begin
  dbms_output.put_line('Start~&1'||'~'||user);
  &1;
  dbms_output.put_line('END~&1');
end;
/
spool off
A simple procedure:
create or replace procedure proc1( p_arg in varchar2 default 'n' )
as
begin
  if nvl(p_arg,'n') = 'n' then
    dbms_output.put_line('Success');
  else
    dbms_output.put_line('Failed'); 
  end if;
end proc1;
/
In sqlplus:
SQL> @test.sql proc1  --This works
SQL> @test.sql proc('x')  --This doesn't work
SQL> @test.sql proc(''x'') --This also doesn't work
I'd appreciate any help or alternatives.
This post has been answered by Frank Kulash on Oct 5 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 2 2009
Added on Oct 5 2009
10 comments
1,161 views