Capturing script output (without the junk)

From: Tarby777 <nick_williamson_at_mentorg.com>
Date: 28 Sep 2005 08:26:00 -0700
Message-ID: <1127921160.395830.55320@f14g2000cwb.googlegroups.com>

Hi all,

I have a SQL script that I'm running in SQLPLUS Worksheet to generate some DDL. It's like this:

---

spool mynewscript.sql;

select "CREATE SEQUENCE " || sequence_name (etc etc) from user_sequences;

spool off;

---

As you can see, I'm generating DDL to create sequences like existing sequences, reason being that I need to move a bunch of them from one schema to another and AFAIK, there's no straightforward way to do it; they have to be recreated rather than moved. The problem I'm having is that as well as the generated DDL, the SELECT statement itself and the SPOOL command is getting written to mynewscript.sql, and all I want in there are the CREATE SEQUENCE statements.

I've managed to stop it telling me how many rows were returned but despite playing about with various SET options, I still have the SELECT and the SPOOL in the resultant script. No doubt there's a better way to do this - perhaps using DBMS_OUTPUT... but I don't know what it is. Can anyone help?

TIA
Nick Received on Wed Sep 28 2005 - 10:26:00 CDT