Pass a variable from one sql script to another as parameter

Question

I am trying to call a sql script from another script, and encountering some issues when trying to pass a variable as parameter.

When defining a constant, I can pass it like this:

DEFINE MY_PARAM = "test"
BEGIN
    @mysqlscript.sql "&MY_PARAM"
END;
/

However I want to call the script with a variable, should look something like this:

DECLARE 
v_MY_PARAM VARCHAR(32) := 'test'
BEGIN
    @mysqlscript.sql :V_MY_PARAM
END;
/

I have tried different ways (i.e. "&V_MY_PARAM", '&V_MY_PARAM', ':V_MY_PARAM', ...) but no luck so far.

Tad Harrison · Accepted Answer · 2020-05-08 15:21:16Z

2

Getting values from a variable to a DEFINE is a bit of a chore and requires a convoluted process. Create a bind variable in SQL*Plus and do whatever you want to get a value into that bind variable. Now, use the COLUMN command with NEW_VALUE to define a column and a define variable that it will go into. Then use a SELECT to get the bind variable into the new define variable.

You may wish to add some SQL*Plus tweaks to minimize the output from some of these activities.

Here's a go at it:

script.sql

set serveroutput on size unlimited

-- use a bind variable defined at SQL*Plus level for this
variable v_MY_PARAM VARCHAR2(32)

BEGIN
    -- can use SELECT to do this too
    :v_MY_PARAM := 'test';
END;
/

-- Here is the magic that converts a bind variable to a DEFINE variable
column mycol new_value myvar
select :v_MY_PARAM as mycol from dual;

BEGIN
    @mysqlscript.sql &myvar
END;
/

mysqlscript.sql

dbms_output.put_line('Working? &1');
-- But wait!!!
dbms_output.put_line('This works too: ' || :v_MY_PARAM);

The first option uses a proper argument, handled as a SQL*Plus define, swapped in verbatim.

The second option uses the bind variable directly in your second file. The bind variable is global to SQL*Plus and can be seen by all scripts. The down side of this second approach is that your second script needs to know the name of the bind variable, and will likely be too closely coupled with the caller. Better to use the command-line argument approach outlined above.

edited May 8, 2020 at 15:21

answered Jun 20, 2019 at 12:13

Tad Harrison

1,2937 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

agim Over a year ago

Thank you, it took me quite some time to find it out :-)

Jagadeesh Over a year ago

Is their any option to Pass a variable from one sql script to another as parameter with postgres sql files.

Collectives™ on Stack Overflow

Pass a variable from one sql script to another as parameter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related