CODEWITHSUNDEEP
oracle-databasesqlplus
user20084750
user20084750

Reputation: 45

Arguments passed to SQL*Plus script override the arguments of script which invoked it

file2.sql 

set serveroutput on
exec dbms_output.put_line('invoke file2.sql');

file1.sql 

exec dbms_output.put_line('&1');
@file2.sql 'col2' 'col1'
exec dbms_output.put_line('&1');

I am executing @file1.sql 'col1'. Please find the output below.

@f1.sql col1

col1

PL/SQL procedure successfully completed.

invoke file2.sql

PL/SQL procedure successfully completed.

col2

PL/SQL procedure successfully completed.

SQL>

I am expecting to print 'col2' after invoking file2.sql but its gets overriden with the value passed to file2.sql. How to overcome this?

Upvotes: 1

Views: 191

Answers (1)

William Robertson
William Robertson

Reputation: 16001

You can save the original argument in a new variable:

file1.sql:

prompt Argument 1 = &1
define somevar = &1

@file2.sql col2 col1

prompt Original argument 1 = &somevar

Output:

SQL> @file1.sql col1
Argument 1 = col1
This is file2.sql
Original argument 1 = col1

Upvotes: 2

Related Questions