Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    In PL/SQL, how to SPOOL my output to files?

    I am not sure whether I can use SPOOL in PL/SQL or not? Basically I need to put my output in files. There is a UTL_FILE package for this, but we are not allowed to set this in init.ora file. Anyone has idea? Thanks so much.

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: In PL/SQL, how to SPOOL my output to files?

    UTL_FILE is the way for large data.

    But if you have small set of data, you can use dbms_output.put_line to write data out which can be spooled to a file.

    Originally posted by aloe9451
    I am not sure whether I can use SPOOL in PL/SQL or not? Basically I need to put my output in files. There is a UTL_FILE package for this, but we are not allowed to set this in init.ora file. Anyone has idea? Thanks so much.

  3. #3
    Join Date
    Jun 2003
    Posts
    3
    Thanks for replying.

    Here is my code:

    SPOOL test_file
    dbms_output.put_line(rec_spe.mkt_seqnum || ',' || rec_spe.market_id);
    SPOOL OFF;

    I got error for the above code, it complains that I am using SPOOL there.

    Your help will be much appreciated.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by aloe9451
    Thanks for replying.

    Here is my code:

    SPOOL test_file
    dbms_output.put_line(rec_spe.mkt_seqnum || ',' || rec_spe.market_id);
    SPOOL OFF;

    I got error for the above code, it complains that I am using SPOOL there.

    Your help will be much appreciated.
    SPOOL is a SQL Plus command, it means nothing inside a PL/SQL block (it is a syntax error there).

    Do this:

    SQL> SPOOL test_file
    SQL> BEGIN -- PL/SQL begins
    ...
    49 dbms_output.put_line(rec_spe.mkt_seqnum || ',' || rec_spe.market_id);
    ...
    98 END; -- PL/SQL ends
    99 /

    PL/SQL procedure successfully completed.

    SQL> SPOOL OFF

  5. #5
    Join Date
    Jun 2003
    Posts
    3
    I see. Thanks for the explanation. It is what I want.

  6. #6
    Join Date
    Aug 2007
    Posts
    1

    hi

    hi
    Actually I want to use spool command inside loop of PL/SQL.
    But its giving error.

    Any advice plz?

    create or replace procedure mane as
    BEGIN
    DECLARE
    TYPE v_arr IS VARRAY(3) of VARCHAR2(10);
    v1 v_arr;
    count NUMBER(2);
    BEGIN
    v1 := v_arr('mae','bbb','ddd');
    cnt := 1;
    LOOP
    spool long_cnt.lst
    dbms_output.put_line('The Value of v1('||cnt||'i) is ' || v1(cnt));
    count := cnt + 1;
    spool off;
    EXIT when cnt > 3;
    end loop;
    END mane;

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,004
    SPOOL is SQL*Plus command and can not be used in PL/SQL. So, either use SPOOL outside of PL/SQL or use UTL_FILE. Here's a quick spooling example:
    Code:
    SQL> spool test.txt
    SQL> set serveroutput on
    SQL>
    SQL> begin
      2    dbms_output.put_Line('Hello!');
      3  end;
      4  /
    Hello!
    
    PL/SQL procedure successfully completed.
    
    SQL> spool off;

Posting Permissions

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