Help with SQL*PLUS

[agilentfg]

We have Oracle running on a Unix box enviroment. I have a SQL script that is build dynamically with DDL commands to analyzse schemas. I have already figured out how to create a dynamic name for the SPOOL file that will hold the trace information that is generated when the script runs. My questions are as follows:

1) how do i generate within SQLPLUS a timetamp (ccyymmddhhmmss, without passing it as a parameters) so that I can append it to the spool file name?

2) my spool file is not capturing All the trace that is being generated. I've used SET SERVEROUTPUT ON SIZE 1000000 but it still not capturing all the trace information.

3) If I define a variabe DEFINE xyz = "abc", what is the command to see/show all variables that I have define within SQLPLUS?

Thanks for your help.

[ales]

For 1) and 2) please send more info and/or your script since
the task you described can be done in many ways.

3)
SQL> help DEF

DEFINE
------

Specifies a user variable and assigns it a CHAR value, or
lists the value and variable type of a single variable or all
variables.

DEF[INE] [variable]|[variable = text]

For detailed information on this command, see the SQL*Plus User's
Guide and Reference.

Regards,
Ales

[programmer]

Try this sql to get the time stamp to your spool file.

set echo off
set feedback off
set heading off
set verify off
set termout off
define FILE_DATE=TO_CHAR(SYSDATE,'ccyymmddhhmiss')
spool temp_sql
select 'set echo off' from dual;
select 'set feedback off' from dual;
select 'set heading off' from dual;
select 'spool final_spool_file_name_'||&FILE_DATE from dual;
select '@d:\temp\' from dual;
select 'spool off' from dual;
spool off
@ temp_sql
set echo on
set feedback on
set heading on
set verify on
set termout on


Hope this will help you