| Results_Set output through the spool [message #685676] |
Thu, 03 March 2022 10:09  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi
I need to capture resulet set returned by procedure into .SQL File. I am uisng the SPOOL Option. Its working fine but having the extra information.
How can I eleminate all those un necdssary data.
I need only SCRIPT which is generated by REFCURSOR .
Example :
create or replace PROCEDURE P_GET_TABLE_INSERTIONS(P_OWNER IN VARCHAR2 DEFAULT NULL, P_TABLE IN VARCHAR2 DEFAULT NULL, P_SCHEMA IN VARCHAR2 DEFAULT NULL, P_RESULTS OUT SYS_REFCURSOR) AS
BEGIN
OPEN P_RESULTS FOR SELECT 'CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);' RESULTS FROM DUAL;
END P_GET_TABLE_INSERTIONS;
SQL> var sr refcursor
SQL> EXEC P_GET_TABLE_INSERTIONS(P_OWNER => null,P_TABLE => null,P_SCHEMA => null,P_RESULTS => :SR);
PL/SQL procedure successfully completed.
SQL> set feedback off
SQL> set trimspool on
SQL> set heading off
SQL> set echo off
SQL> spool req.sql
SQL> print sr
CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);
SQL> spool off
SQL> print sr
CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);
SQL> spool off
CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);
Sai Pradyumn
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
