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
397 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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
This video shows how to recover a database from a user managed backup

734 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