DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SPOOL IN SQL PLUS

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    Hi all,

    In SQL plus, when you use SPOOL option to spool out the output to a file, is it possible for us to have the date concatnate with the file name.

    We don't want to hard code the file name and date. what I am trying to do is if I spool this file today it will stamp today date on my file name and let's say tomorrow I run it again it will stamp the date of tomorrow on the file instead of overwriting today's file.

    Any advises???
    Thanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    SQL> col today new_value mydate
    SQL> set escape /
    SQL> select sysdate today from dual;

    TODAY
    ---------
    2002SEP12

    SQL> spool c:\&mydate/SPOOL.txt
    SQL> select 1 from dual;

    1
    ----------
    1

    SQL> spool off

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Try this ( if u are using shell script )

    set `date`

    ORACLE_SID=orcl
    export ORACLE_SID
    sqlplus system/xxxx < spool temp1$2.$3log
    select sysdate from dual ;
    spool off
    !

    echo "\nJob Over"


    siva prakash
    DBA

  4. #4
    Join Date
    Jan 2002
    Posts
    474
    Pando,

    thanks so much and your scripts work great; however, quick question.

    what is the "set ESCAPE /" do???


    I am trying to find out why I build your scripts into mine and it broke

    thanks

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if i do

    spool c:\&mydateSPOOL.txt

    it will askme to enter a value for mydatespool, it will think my variable is mydateSPOOL instead of mydate. If I set escape / I can use / to seperate variable name and literals

    just like UNIX

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Here's how I did (I'm not SQL expert!)

    In the script I did ...


    spool spoolfilename.sql

    SELECT 'spool C:\Dest1\'||''File1_'||sysdate
    FROM dual;
    spool off

    @spoolfilename

    This spooled output to a File called, for example
    File1_12-May-02
    in my C:\Dest folder .


  7. #7
    Join Date
    Jan 2002
    Posts
    474
    Pando,

    I have a quick question. I am trying to build your scripts with mine and I had some problem. Can you help me out???

    Let's say if I am in the database A and run the below SQL:

    SQL> col today new_value mydate
    SQL> set escape /
    SQL> select sysdate today from dual;

    TODAY
    ---------
    2002SEP12

    SQL> spool c:\&mydate/SPOOL.txt
    SQL> select 1 from dual;

    1
    ----------
    1
    and then I have this SQL statement to connect to another database as follow:

    SQL> connect system/manager@DATABASE_B
    Enter password:

    it asked me for the password. seem like I missed soemthing here.

    Do you have any advises???

    Thanks

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    then set something else for escape or unset it easy as that

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You have set your escape character to "/". So when you do
    CONNECT SYSTEM/MANAGER@DATABASE_B, Oracle treats character '/' as an escape character, so it thinks you want to connect as a user with username SYSTEMMANAGER and asks for a password for that user.

    So before isuing CONNECT command, you must disable escape character or set it to some other character, using either
    SET ESCAPE OFF or SET ESCAPE # (where you can replace # with any suitable character).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jan 2002
    Posts
    474
    thanks Pando and Jmodic



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