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!

Passing Arguments to Anonymous Block

BipulOct 20 2010 — edited Oct 20 2010
I have a unix script which is invoking a sql file/anonymous block.
The sql file read a flat file through UTL_FILE utility and insert/update those data to table.
While invoking SQL script I put
 'SET DEFINE OFF' 
to get rid of the *'&'* char in the string (input file).
Now I have to process multiple files on a single run, I am invoking the SQL script by passing arguments like :

Invoke :
start xx.sql FILE_NAME
Inside xx.sql accessing the arguments like :
declare
y varchar2(20);
begin
dbms_output.put_line('Y= '||y);
y:='&1';
dbms_output.put_line('Y= '||y);
end;
/
It only works if I put
 'SET DEFINE ON' 
In this case any *'&'* char being treated as read from standard input.
Creating procedure/function is out of scope.
Can anyone suggest ... what should be the approach and is there a way to manage it ?
This post has been answered by Frank Kulash on Oct 20 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 17 2010
Added on Oct 20 2010
3 comments
4,760 views