?
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
Medium Priority
?
403 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

752 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