Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Unanswered: how to use SPOOL in a stored Procedure?

    How can I use SPOOL Command in a stored procedure to divert the output of a select statement to a file?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to use SPOOL in a stored Procedure?

    You can't. You could use UTL_FILE to write to a file on the server. Or, if there is not too much output, you could use DBMS_OUTPUT in the stored procedure and SET SERVEROUT ON in SQL Plus before running it.

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Re: how to use SPOOL in a stored Procedure?

    Originally posted by andrewst
    You can't. You could use UTL_FILE to write to a file on the server. Or, if there is not too much output, you could use DBMS_OUTPUT in the stored procedure and SET SERVEROUT ON in SQL Plus before running it.
    Sorry dear friend by using ref cursor it's possible.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to use SPOOL in a stored Procedure?

    Originally posted by amit_krai
    Sorry dear friend by using ref cursor it's possible.
    You think so? OK, if you can make the SQL Plus "SPOOL" command work from a stored procedure using a REF CURSOR, please share your code!

  5. #5
    Join Date
    Aug 2001
    Posts
    66
    Well if you mean something like this...
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> CREATE OR REPLACE PROCEDURE procedure_name (
      2    column_name IN VARCHAR2,
      3    ref_cursor OUT SYS_REFCURSOR)
      4  IS
      5  BEGIN
      6    OPEN ref_cursor FOR 
      7      ' SELECT SUM (sal), ' || column_name || 
      8      ' FROM emp GROUP BY ' || column_name; 
      9  END;
     10  /
    
    Procedure created.
    
    SQL> SET AUTOPRINT ON;
    SQL> VARIABLE ref_cursor REFCURSOR;
    SQL> SPOOL emp_groups.lst
    SQL> EXEC procedure_name ('DEPTNO', :ref_cursor);
    
    PL/SQL procedure successfully completed.
    
    
      SUM(SAL)     DEPTNO
    ---------- ----------
          8750         10
         10875         20
          9400         30
    
    SQL>
    ...then that is not calling SPOOL from a PL/SQL procedure - the stored procedure has finished executing, the output is being SPOOLed by SQL*Plus, not by PL/SQL. Perhaps we are splitting hairs - can the original poster confirm whether this is what they meant?

    Padders
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  6. #6
    Join Date
    Apr 2004
    Posts
    2

    Spool File

    For Spooling, create a batch file and call it into your procedure.

Posting Permissions

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