Re: Passing Parameter to SQL+ script containing PL/SQL block..

From: Anton Buijs <aammbuijs_at_xs4all.nl>
Date: Thu, 16 May 2002 21:16:56 +0200
Message-ID: <ac10hp$3jm$1@news1.xs4all.nl>

Don't see the problem. Tested next on my Windows Personal Oracle V7.3.4 and V8.0.5 and V8.1.7.3 on HP-UX
db with script test3.sql.
It works with and without the parameter passed enclosed in ".

set serveroutput on
begin
  if ('&1') = 'abc' then
    dbms_output.put_line('Yes, it is abc (&1)');   else
    dbms_output.put_line('No, you passed &1');   end if;
end;
/
exit

D:\>sqlplus -S scott/tiger @test3 "abc"

old   2:   if ('&1') = 'abc' then
new   2:   if ('abc') = 'abc' then
old   3:     dbms_output.put_line('Yes, it is abc (&1)');
new   3:     dbms_output.put_line('Yes, it is abc (abc)');
old   5:     dbms_output.put_line('No, you passed &1');
new   5:     dbms_output.put_line('No, you passed abc');

Yes, it is abc (abc)

PL/SQL procedure successfully completed.

D:\>sqlplus -S scott/tiger @test3 xyz

old   2:   if ('&1') = 'abc' then
new   2:   if ('xyz') = 'abc' then
old   3:     dbms_output.put_line('Yes, it is abc (&1)');
new   3:     dbms_output.put_line('Yes, it is abc (xyz)');
old   5:     dbms_output.put_line('No, you passed &1');
new   5:     dbms_output.put_line('No, you passed xyz');

No, you passed xyz

PL/SQL procedure successfully completed.

R Chin <rchin_at_panix.com> schreef in berichtnieuws ac0td8$e4m$1_at_reader1.panix.com...
| this line in UNIX shell script starts SQL+ to run a script (REPORT.sql)
and
| pass it a parameter:
|
| sqlplus -S user/pwd @REPORT.sql "MAINREPORT"
|
|
|
| --this is REPORT.sql
|
| BEGIN
| if '&1' = 'MAINREPORT'
| then
| CORP_PKG.MAINREPORT;
| else
| ....
|
| END;
| /
|
| Can I use '&1' in the sql script to receive the parameter ?
| It does not seem to work....
|
| Thanks
| Rob
|
|
Received on Thu May 16 2002 - 14:16:56 CDT