How to spool sql statement to log file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to spool sql statement to log file

  1. #1
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    5

    How to spool sql statement to log file

    Please kindly help me. I try to spool sql statement to log file by us
    set echo option but it can not.

    I wrote sql statement in shell script.

    Thank you very much.
    Plammy

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    do you mean spool the output of a script?

    In which case use SPOOL filename

    Or to save a sql script SAVE scriptname

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    5
    I mean that I want to spool sql statement and output also.
    Plammy

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    try this

    Code:
    SQL>SPOOL script.txt
    SQL>LIST
      1  SELECT
      2    dbid,
      3    name,
      4    created
      5  FROM
      6*   v$database
    SQL> /
    
          DBID NAME      CREATED
    ---------- --------- ---------
    2799508272 ZOIDBERG  10-JAN-02
    
    SQL> SPOOL OFF
    This gives you the script and the output in a single text file

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    5
    spool /home/xxx/update_xxx.log

    sqlplus -s set serveroutput on
    set echo on
    set time on
    set timing on

    select *
    from emp ;

    spool off;

    but it can not spool
    select *
    from emp;

    to /home/xxx/update_xxx.log

    Please kindly investigate my script

    Thank you very much.
    Plammy

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    I assume this is a unix platform, I think the following is more what your looking for, create a unix file with execute permissions:

    Code:
    sqlplus -s scott/tiger <<-! > /home/xxx/update_xxx.log
      set serveroutput on
      set echo on 
      set time on
      set timing on
    
      select *
      from emp ;
    
      list;
      !
    exit
    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  7. #7
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    change your script to this:




    sqlplus -s set serveroutput on
    set echo on
    set time on
    set timing on
    spool /home/xxx/update_xxx.log

    select *
    from emp ;

    spool off;

    Note: this will overwrite the "/home/xxx/update_xxx.log" each time you run the sql script.

  8. #8
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    5
    Thank you very much Jovery and Bgill .

    Right now I can do it as you suggest.
    Plammy

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