Q:

PLSQL Passing Variables to External .SQL Scripts

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

A:

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.

