Home » SQL & PL/SQL » Client Tools » Results_Set output through the spool (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
Results_Set output through the spool [message #685676] Thu, 03 March 2022 10:09 Go to next message
saipradyumn
Messages: 416
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;

Execution

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

Present output


SQL> print sr

CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);
SQL> spool off

REquired Output


CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);

Thanks
Sai Pradyumn
Re: Results_Set output through the spool [message #685678 is a reply to message #685676] Thu, 03 March 2022 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have the input line because you type the command yourself.
Put the commands in a script file with "set echo off" and it will be OK.

Re: Results_Set output through the spool [message #685679 is a reply to message #685678] Thu, 03 March 2022 11:28 Go to previous messageGo to next message
saipradyumn
Messages: 416
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel,

Thanks for your help .But l already tried with set echo off as mentioned in the script file
Re: Results_Set output through the spool [message #685680 is a reply to message #685679] Thu, 03 March 2022 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, if you execute the commands CALLING a script file and NOT typing them you'd be OK.
"set echo off" is ignored in interactive mode (you have to see what you type doesn't it).

Re: Results_Set output through the spool [message #685681 is a reply to message #685680] Thu, 03 March 2022 12:42 Go to previous messageGo to next message
saipradyumn
Messages: 416
Registered: October 2011
Location: Hyderabad
Senior Member
Got it Michel . Created the script and exexuted that file.Now heading are not displaying . But I got the following output.


CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);
SQL> exit


Now trying to elimate the last line as well
Re: Results_Set output through the spool [message #685682 is a reply to message #685681] Thu, 03 March 2022 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You forgot the "spool off" in your script.

Re: Results_Set output through the spool [message #685683 is a reply to message #685679] Fri, 04 March 2022 05:30 Go to previous message
saipradyumn
Messages: 416
Registered: October 2011
Location: Hyderabad
Senior Member


Thank you very Michel.It went fine
Previous Topic: Client Install
Next Topic: error when passing values to the query
Goto Forum:
  


Current Time: Sat Jun 25 06:47:18 CDT 2022