Solved

Spooled SQL script from DYNAMIC SQL should only have the output of the DYNAMIC SQL and nothing else

Posted on 2008-10-28
7
386 Views
Last Modified: 2013-12-18
Spooled SQL script from DYNAMIC SQL should only have the output of the DYNAMIC SQL and nothing else

My code is as follows:

spool c:\yy.sql

set heading off

SELECT 'AUDIT INSERT, UPDATE, DELETE on '||tablename||' ;
FROM xxxxxxxx
ORDER BY tablename;

spool off

I run the above dynamic SQL statement  in SQL*PLus and spool the output to a SQL script yy.sql.

When I open the SQL script, it has the query at the top, then the output of the dynamic statement, number of rows retrieved, and spool off.

I need the following:

When the above SQL script is created, it should only have the output of the dynamic SQL statement.  It should not have the query at the top, and number of rows retrieved, and spool off after the output.

How do I do this in SQL*Plus?

Thanks.

This is important and urgent.  I will give a 1000 points to anyone who gives me the correct solution.
0
Comment
Question by:as93is
  • 3
  • 3
7 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 22820680
Before the spool-command you need the following:

SET NEWPAGE 0 -- supress headings from btitle
SET LINESIZE 200 -- make the line long enough
SET PAGESIZE 0 -- supress form feeds
SET ECHO OFF   -- supress the output of the statement
SET FEEDBACK OFF -- supress the 'rows selected'-line
SET VERIFY OFF  -- supress output of variable verification
SET HEADING OFF -- set heading off
SET TRIMSPOOL ON -- make sure that lines are only as long as needed
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 500 total points
ID: 22820707
Furthermore, put the statement in an sql-file to supress the output of the lines entered between the spool and the spool off

so your erstelle_file.sql should look like the following:

SET NEWPAGE 0 -- supress headings from btitle

SET LINESIZE 200 -- make the line long enough

SET PAGESIZE 0 -- supress form feeds

SET ECHO OFF   -- supress the output of the statement

SET FEEDBACK OFF -- supress the 'rows selected'-line

SET VERIFY OFF  -- supress output of variable verification

SET HEADING OFF -- set heading off

SET TRIMSPOOL ON -- make sure that lines are only as long as needed
 

spool c:\yy.sql

SELECT 'AUDIT INSERT, UPDATE, DELETE on '||tablename||' ;

FROM xxxxxxxx

ORDER BY tablename;
 

spool off

Open in new window

0
 

Author Comment

by:as93is
ID: 22821164
What is the SET command that will remove/delete the SQL query at the top in the spooled file yy.sql?

Will your SET commands ONLY HAVE THE OUTPUT OF THE DYNAMIC SQL IN THE SPOOLED FILE?

This is the IMPORTANT REQUIREMENT.

This is an urgent task that I'm working on.

Thanks!!!

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:MarkusId
ID: 22821250
The script I offered above, if put in an file and started using start filename, should have only the output (ie the 'AUDIT INSERT, UPDATE, DELETE on tablename'-statements.
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22821440
first, your sql is missing a single-quote on the end..  I hope that's a bad cut/paste

second, don't spool...  just run it in a loop


set serveroutput on

begin

dbms_output.enable(1000000);

for x in (

SELECT 'AUDIT INSERT, UPDATE, DELETE on '||tablename run_me

FROM xxxxxxxx

ORDER BY tablename

)

loop

  

  execute immediate x.run_me;

-- if you really want to spool, uncomment the next line

--  dbms_output.put_line(x.run_me);
 

end loop;

end;

/

Open in new window

0
 

Author Comment

by:as93is
ID: 22828054
This was an excellent, timely solution.
I would like to award this solution 1000 points. I don't how to do this.

Moderator, please award this solution 1000 points.

Thanks.
0
 

Author Closing Comment

by:as93is
ID: 31510683
Markusid's was an excellent, timely solution.
I would like to award 1000 points for this solution.  I don't know how to do this.
Moderator, please award this solution 1000 points and close this questions.

Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now