Echo for SQL scripts

Hi all,

I want to see input SQL statements in the log file when I run the script in SQLPlus. I have used this set command "SET ECHO ON" for this. However, the log file looks like this -

drop table table_A
*
ERROR at line 1:
ORA-00942: table or view does not exist

7444 rows deleted.

Commit complete.

Thus, the SQL statement is not visible if it is error free. Is there a way to get around this?

ThanksDid you try to SPOOL the results?
This will do it:
SET ECHO ON
SPOOL logfile.log
@.MyScript
SPOOL OFF
;)|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

This error means what it means: your script executes a script that executes a script ...etc upto more that 20 levels deep.
:rolleyes:

Dynamically create SQL Scripts

I would like to be able to create SQL Scripts with a stored procedure (or some other means). I have looked all through the help files and even tried watching the profiler to see how EM does it. Any help would be appreciated.I've written a set of procedures that will generate INSERT/SELECT/UPDATE statements for a given table, plus more. Is that what you are looking for?|||Thanks for your reply.

I went back and read my post and I'm sorry, I should have been more clear. I am looking for a way to dynamically generate the scripts to create the objects themselves (Stored Procedures, Tables, Logins, etc.). I would like to have a job that would fire every week and write out the individual scripts for each object into a directory named the same as the database. Basically, I want to automate the "Generate SQL Scripts" menu option in EM.|||Don't know an easy way to do this, I don't think you can do it using DTS
at least not pre-2000.

The hard way is to write an SP which generates all the DDL using
sysobjects, syscolumns, systypes, sysindexes, syslogins, sp_helptext etc.

You can use sp_help code as a preliminary guide on how to extract the DDL.|||You can use SQLDMO to generate the script. What version of SQL Server are you using, I may have a VB program that does this and a Perl program.|||Thanks for the reply plus any help given, I am using 2000.|||achorozy, you mentioned I could use DMO (something I have zero experience with) or you might have something I can use. Can you help me?

Thanks|||I've attached a VB project that was written using SQLDMO to generate SQL script for a given database.

This script was originally for 6.5 but was converted to 7.0 and 2000 by changing the SQLDMO libray from SQLOLE to SQLDMO. I believe this code original came from an example on DEVX a few years ago. This is not my code but I did modify it to work with 7.0 and 2000.|||Attachment|||Thanks!!

That worked great.