DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    May 2001


    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.

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

    SQL> help DEF


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

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

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


  3. #3
    Join Date
    Jun 2001
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width