Home » SQL & PL/SQL » SQL & PL/SQL » Access variables from different script (Oracle 11.2.0.2)
Access variables from different script [message #648237] Fri, 19 February 2016 02:27 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

I've 6 queries, each is more than 4000 characters size. I would like to pass each query to a function as an argument.
The function executes the query and generate a CSV file using UTL_FILE.


I am assigning each query to a variable inside a .sql file.
Then, I would call the .sql script in another script to access the variable.
I am hit by the issue when I try to access the bind variable in another script.

Please advice how to get through it.

Sample code is as follows.
----------------
-- @/home/sr/test/clob_query.sql
----------------
variable c clob;
c := q'[select * from dual];

---------------
-- clob_exec.sql
---------------
set serveroutput on
@/home/sr/test/clob_query.sql
DECLARE
   l_x VARCHAR2(1000);
BEGIN
   select :c into l_x
     from dual;
   dbms_output.put_line('here is the value: '||l_x);
END;
/

Regards,
Pointers

[Updated on: Fri, 19 February 2016 02:28]

Report message to a moderator

Re: Access variables from different script [message #648241 is a reply to message #648237] Fri, 19 February 2016 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Script clob_query.sql is wrong, it should be:
variable c clob;
exec :c := q'[select * from dual]';

SQL> variable c clob;
SQL> exec :c := q'[select * from dual]';

PL/SQL procedure successfully completed.

SQL> print c
C
-----------------------------------------
select * from dual

In script clob_exec.sql you can just use:
dbms_output.put_line('here is the value: '||:c);

No need of local variable.
SQL> set serveroutput on
SQL> exec dbms_output.put_line('here is the value: '||:c);
here is the value: select * from dual

PL/SQL procedure successfully completed.


The bind variable (:c) is in your SQL*Plus session, you can access it in any script.

[Updated on: Fri, 19 February 2016 02:53]

Report message to a moderator

Re: Access variables from different script [message #648253 is a reply to message #648241] Fri, 19 February 2016 06:49 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you Micheal for your time.

Regards,
Pointers
Previous Topic: Sql Query between String
Next Topic: Oracle Dates query
Goto Forum:
  


Current Time: Thu Mar 28 23:55:52 CDT 2024