PLSQL Passing Variables to External .SQL Scripts

Question

I have 2 scripts, Script1.sql & Script2.sql. Script1.sql retrieves some data from a table in the database which I want to then pass to Script2.sql to use.

Script1.sql is as below:

SET SERVEROUTPUT ON;

DECLARE
  FundRecord Test_Table%ROWTYPE;

  CURSOR Fund_Cursor  IS SELECT Code, YOURNAME FROM Test_Table;

BEGIN 
  OPEN Fund_Cursor;
  LOOP
    FETCH Fund_Cursor INTO FundRecord;
    EXIT WHEN Fund_Cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Script1: ' || FundRecord.Code);
  END LOOP;
  CLOSE Fund_Cursor;
END;
/
@C:\Temp\Script2.sql FundRecord.Code;

And Script2.sql is as below:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Script 2:' || ' ' || '&1');
END;
/

The output from Script1.sql and Script2.sql is as follows:

Script1: ABDCE
Script2: FundRecord.Code

Why is the output of Script2 FundRecord.Code and not 'ABCDE' as I would expect?

How do I pass this in to ensure that Script2 is getting 'ABCDE' as the parameter?

Thanks

William Robertson · Accepted Answer · 2017-05-04 12:12:43Z

1

The record FundRrecord only exists within the PL/SQL block. You need to declare a separate variable that you can use outside the block:

set autoprint on serverout on

var somevalue varchar2(20)
col somevalue new_value somevalue

begin
    for r in (
        select dummy from dual
    )
    loop
        dbms_output.put_line('Script 1: ' || r.dummy);
        :somevalue := r.dummy;
    end loop;
end;
/

@C:\Temp\Script2.sql &somevalue

The column ... new_value ... syntax is provided in SQL*Plus for page headers and footers in reports, but it is also very useful in scripts as it sets a define variable from the (last) result of a query. set autoprint on tells SQL*Plus to print the values of any bind variables (the ones with a leading :) after each PL/SQL block, and helpfully it does this by generating a query, allowing us to set up a column ... new_value and capture the result in a substitution variable.

Edit: regarding SQL Developer compatibility, I'll try some things out when I get a chance, but you might try adding something along the lines of

select :somevalue as somevalue from dual;

after the PL/SQL block, in case the column ... new_value construction works the same as in SQL*Plus but autoprint does not.

edited May 4, 2017 at 12:12

answered May 3, 2017 at 7:19

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

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

3 Comments

Steeden Over a year ago

I've tried using the code you've posted above and the same thing happens, only the literal name of the variable is picked up in Script2 rather than the actual value assigned to the parameter. I just get "Script 1: X" "Script 2: :somevalue" printed to DMBS_Output window

Steeden Over a year ago

Thanks William the edited code seems to work now within SQL Plus but does not work when using SQL Developer. I guess I can use SQL Plus instead. Thanks for your help

William Robertson Over a year ago

I've added a suggestion, though untested as I'll have to find some time to dig out my copy of SQl Dev.

Jacob · Accepted Answer · 2017-05-04 04:47:37Z

1

You can try as

@Script2.sql param1

and in Script2 SQL file, refer the parameter as

&1

Update 1

Here is my test case which works fine.

SELECT SYSDATE FROM &1;

This SQL statement is saved as Test.sql and it is invoked from SQLPLUS as

@D:\Test.sql dual

where dual is the parameter which been passed to Test.sql file

Result is displayed in the below screenshot

edited May 4, 2017 at 4:47

answered May 3, 2017 at 5:02

Jacob

14.8k69 gold badges215 silver badges323 bronze badges

2 Comments

Steeden Over a year ago

Tried that as well and also only the literal name of the variable is picked up in Script2 rather than the actual value assigned to the parameter

William Robertson Over a year ago

The challenge here though is for the top-level script to capture the value to pass. The OP originally tried passing FundRecord.Code which was part of a record within the PL/SQL block.

Collectives™ on Stack Overflow

PLSQL Passing Variables to External .SQL Scripts

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related