OraFAQ Forum: Client Tools » Results_Set output through the spool

Home » Other » Client Tools » Results_Set output through the spool (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 :

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

SQL> print sr

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

REquired Output

Code: [Select all] [Show/ hide]

CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);

Thanks
Sai Pradyumn

Report message to a moderator

Re: Results_Set output through the spool [message #685678 is a reply to message #685676]

Thu, 03 March 2022 11:23

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Results_Set output through the spool [message #685679 is a reply to message #685678]

Thu, 03 March 2022 11:28

saipradyumn
Messages: 419
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

Report message to a moderator

Re: Results_Set output through the spool [message #685680 is a reply to message #685679]

Thu, 03 March 2022 12:02

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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).

Report message to a moderator

Re: Results_Set output through the spool [message #685681 is a reply to message #685680]

Thu, 03 March 2022 12:42

saipradyumn
Messages: 419
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.

Code: [Select all] [Show/ hide]

CREATE TABLE TEST_TABLE_OUT(NUM1 NUMBER);
SQL> exit

Now trying to elimate the last line as well

Report message to a moderator

Re: Results_Set output through the spool [message #685682 is a reply to message #685681]

Thu, 03 March 2022 12:54

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You forgot the "spool off" in your script.

Report message to a moderator

Re: Results_Set output through the spool [message #685683 is a reply to message #685679]

Fri, 04 March 2022 05:30

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thank you very Michel.It went fine

Report message to a moderator

Previous Topic:	Client Install
Next Topic:	error when passing values to the query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 09 02:15:10 CDT 2025