Help with SQL*PLUS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help with SQL*PLUS

  1. #1
    Join Date
    May 2001
    Posts
    28

    Question

    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
    Location
    Czechia
    Posts
    712
    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

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