CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle12c
agim
agim

Reputation: 1841

Pass a variable from one sql script to another as parameter

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.

Upvotes: 1

Views: 2786

Answers (1)

Tad Harrison
Tad Harrison

Reputation: 1293

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.

Upvotes: 2

Related Questions