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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

damorgan
Tom Kyte has said it repeatedly ... I will repeat it here for you.

The fact that Oracle allows you to build object tables is not an indication that you should.
Store your data relationally and build object_views on top of them.
http://www.morganslibrary.org/reference/object_views.html

If you model properly, and store properly, you don' have any issues.
3520
Yep I know it
I said that to our developers as well
Problem is, that they have created such constructions before

On the other hand creating functionality that only works sometimes or partially is also at least weird not to say neglectful. Unfortunately this more and more happens with Oracle...
1 - 2
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,060 views