Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Arrow Unanswered: oracle pl/sql multiple spool help required

    Now Im not an expert or anything with SQL so bear with me...

    I have 3 seperate queries that each use the spool command to write to a file on the server.
    e.g. i use the following construct in each of the three sql files:

    set blah
    set blah
    spool /blah/blah.rpt
    script
    spool off


    I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?

    I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...

    Any help would be great. Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, you can. For example,
    PHP Code:
    spool a1.txt
    select count
    (*) from tab;
    spool off;

    spool a2.txt
    select sysdate from dual
    ;
    spool off;

    spool a3.txt
    select 
    'x' dummy from dual;
    spool off
    will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...

  3. #3
    Join Date
    Mar 2004
    Posts
    9
    I did try it and it didnt work the way I expected...

    I have the three spools and also I have a title for each of the three files:

    e.g.
    ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2

    I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.

    I was wanting to make sure that I was using the spool correctly so thats why i asked.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.

    PHP Code:
    SPOOL a1.txt
      TTITLE 
    'First top title' skip 2 
      BTITLE 
    'First bottom title'
      
    COLUMN dummy noprint;
      
    SELECT 'x' dummy FROM DUAL;

      
    SELECT COUNT (*) FROM tab;
    SPOOL off;

    SPOOL a2.txt
      TTITLE 
    'Second top title' skip 2 
      BTITLE 
    'Second bottom title'
      
    COLUMN dummy noprint;
      
    SELECT 'x' dummy FROM DUAL;
     
      -- 
    this query returns no rows 
      SELECT 
    'x' FROM dual WHERE sysdate sysdate 1;
    SPOOL off

  5. #5
    Join Date
    Mar 2004
    Posts
    9
    thanks for the help, ill give that a try.

Posting Permissions

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