Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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

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
as93is
Asked:
as93is
  • 3
  • 3
1 Solution
 
MarkusIdCommented:
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
 
MarkusIdCommented:
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
 
as93isAuthor Commented:
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
Industry Leaders: 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!

 
MarkusIdCommented:
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
 
dbmullenCommented:
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
 
as93isAuthor Commented:
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
 
as93isAuthor Commented:
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

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!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now