| Access variables from different script [message #648237] |
Fri, 19 February 2016 02:27  |
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  |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
