Solved

Spooling in Toad

Posted on 2008-06-23
7
3,510 Views
Last Modified: 2012-06-27
how do i spool  the output data from a storeprocedure to File.i am using toad with oracle
0
Comment
Question by:Manjula_Hari
7 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 21844589
Copy/pasting the output.

Also you can use SQL*Plus or iSQLPLUS and
spool the output:

spool c:\File.i
execute my_pocedure;
spool off
0
 
LVL 18

Expert Comment

by:sventhan
ID: 21844840
In Toad write your script as below

spool c:\temp\outputfile.txt
exec your_sp;
spool off

Then press F5 or, goto sql editor then select "execute as a script".
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 21845821
set serveroutput on

or, in the "editor"
        right click on the bottom "data grid" tab
        make sure the DBMS_OUTPUT tab is enabled   (ctrl-alt-D)
        click the "green" go button

       
--  then do as sventhan has posted above
spool c:\temp\outputfile.txt
exec your_sp;
spool off
Then press F5 or, goto sql editor then select "execute as a script".
0
 

Author Comment

by:Manjula_Hari
ID: 21896948
i need a stored procedure in side which the select statements should be present.i want spool the output of sp to file.is it possible
eg,
PACKAGE PKG_SPOOL IS
 TYPE OUTPUTCURSOR IS REF CURSOR;
 PROCEDURE PARTLIST(OUT_PARTLIST OUT OUTPUTCURSOR);
 procedure SPOOldata(OUT_PARTLIST OUT OUTPUTCURSOR);
END PKG_SPOOL;
/
Create or replace package body PKG_SPOOL as
Procedure PARTLIST(OUT_PARTLIST OUT OUTPUTCURSOR)
AS
BEGIN
open OUT_PARTLIST for SELect VIEW_CODE FROM BILL;
END PARTLIST;
Procedure SPOOldata(OUT_PARTLIST OUT OUTPUTCURSOR)
spool PARTLIST
exec PARTLIST
spool off
end SPOOldata
END PKG_SPOOL;
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 21897236
-- ************************************************************
-- CSV_INVOICES.PRC   Procedure to create text file for an
--                    invoice
--
-- Inputs:   BGWAPP_INVOICE
--           BGWAPP_INVOICE_LINE
--           BGWAPP_CUSTOMER
--           BGWAPP_PRODUCT
--           BGWAPP_ORDER
--
-- Parameters:  in_sequence     - file number
--              in_file_name    - file name
--              in_customer_id  - Customer Number
--              in_invoice_id   - Invoice Number
--              in_start_date   - Date Range - Start Date
--              in_end_date     - Date Range - End   Date
--
-- There will be headings at the top
--
-- Invoice Header information
--     Invoice #, Invoice Date, Customer #,
--     Customer Name, Order #, Order Date, Status,
--     Total Lines, Total Amount
--
-- Invoice Detail information
--     Invoice Line #, Product #, Description, Quantity,
--     Unit of Measure, Unit Price, Amount, Status
-- ************************************************************


PROCEDURE CSV_INVOICES
   (in_sequence      IN NUMBER,
    in_file_name     IN VARCHAR2,
    in_customer_id   IN NUMBER,
    in_invoice_id    IN NUMBER,
    in_start_date    IN DATE,
    in_end_date      IN DATE)
is

out_file UTL_FILE.FILE_TYPE;
out_counter   INTEGER := 0;
out_line      INTEGER := 0;
v_invoice_id  number(8);
v_file_name   varchar2(40);
out_record    varchar2(2000);

CURSOR crs_cust_inv_date (c_cust varchar2, c_inv number, c_start date, c_end date) IS
   SELECT a.customer_id,
          a.invoice_id,
          a.invoice_date,
          a.order_id,
          d.order_date,
          c.name,
          a.status,
          COUNT(*) total_lines,
          SUM(b.amount) total_amount
   FROM   bgwapp_order         d,
          bgwapp_customer      c,
          bgwapp_invoice_line  b,
          bgwapp_invoice       a
   WHERE  a.customer_id = nvl(c_cust,a.customer_id)
   AND    a.invoice_id  = nvl(c_inv,a.invoice_id)
   AND    a.invoice_date between
              nvl(c_start,a.invoice_date) and
              nvl(c_end,a.invoice_date)
   AND    a.invoice_id  = b.invoice_id
   AND    a.customer_id = c.customer_id
   AND    a.order_id    = d.order_id(+)
   GROUP BY
          a.customer_id,
          a.invoice_id,
          a.invoice_date,
          a.order_id,
          d.order_date,
          c.name,
          a.status;

CURSOR crs_invoice_detail IS
   SELECT a.invoice_id,
          a.invoice_line_id,
          a.product_id,
          b.name,
          a.quantity_invoiced,
          a.unit_of_measure,
          a.unit_price,
          a.amount,
          a.status
   FROM   bgwapp_product b,
          bgwapp_invoice_line a
   WHERE  a.invoice_id = v_invoice_id
     and  a.product_id = b.product_id;

BEGIN

 BEGIN
    select machine_port, directory, app_identifier, file_location
      into c_mach_port, c_dir, v_identifier, v_location
      from ediapp_control;
 EXCEPTION
   when OTHERS then
      htp.p('Error in accessing EDIAPP_CONTROL!! - '||SQLERRM);
      goto skip;
 END;

--
-- Open Output File
--
       
 v_file_name := in_file_name;

 out_file := UTL_FILE.FOPEN ( v_location, v_file_name, 'W' );

 out_record := '"File Name","'||v_file_name||'",,,,,,,,,,,,,,,," "';
 UTL_FILE.PUT_LINE ( out_file, out_record );

 out_record := '" ",,,,,,,,,,,,,,,,," "';
 UTL_FILE.PUT_LINE ( out_file, out_record );


 out_record := '"INVOICE #","INVOICE DATE","CUSTOMER #","NAME","ORDER #","ORDER DATE",'||
               '"STATUS","TOTAL LINES","TOTAL AMOUNT",'||
               '"INVOICE LINE #","PRODUCT #","PRODUCT NAME","QTY","UOM",'||
               '"UNIT PRICE","AMOUNT","STATUS"';
 UTL_FILE.PUT_LINE ( out_file, out_record );

 out_record := '" ",,,,,,,,,,,,,,,,," "';
 UTL_FILE.PUT_LINE ( out_file, out_record );

 For crs_invoice in crs_cust_inv_date
        (in_customer_id, in_invoice_id, in_start_date, in_end_date)
        LOOP

--
-- INVOICE HEADER / DETAIL Output Line
--

     v_invoice_id := crs_invoice.invoice_id;
     out_counter  := out_counter + 1;
     out_line     := 0;      

     For crs_detail in crs_invoice_detail LOOP

        out_record := null;
        out_line   := out_line + 1;
        out_record := to_char(crs_invoice.invoice_id) ||',"'||
                      to_char(crs_invoice.invoice_date,'YYYY-MM-DD') ||'",'||
                      to_char(crs_invoice.customer_id) ||',"'||
                      crs_invoice.name ||'",'||
                      to_char(crs_invoice.order_id) ||',"'||
                      to_char(crs_invoice.order_date,'YYYY-MM-DD') ||'","'||
                      crs_invoice.status ||'",'||
                      to_char(crs_invoice.total_lines) ||','||
                      to_char(crs_invoice.total_amount)||','||
                      to_char(out_line) ||','||
                      to_char(crs_detail.product_id) ||',"'||
                      crs_detail.name ||'",'||
                      to_char(crs_detail.quantity_invoiced) ||',"'||
                      crs_detail.unit_of_measure ||'",'||
                      to_char(crs_detail.unit_price) ||','||
                      to_char(crs_detail.amount) ||',"'||
                      crs_detail.status ||'"';
        UTL_FILE.PUT_LINE ( out_file, out_record );
     END LOOP;
 END LOOP;
 UTL_FILE.FCLOSE ( out_file );
 DBMS_OUTPUT.PUT_LINE ( 'Total Invoices:  '||to_char(out_counter) );

<< skip >>
 null;

EXCEPTION
 when UTL_FILE.INVALID_PATH then
      DBMS_OUTPUT.PUT_LINE('Invalid Path:  '||v_location);
      htp.p('Invalid Path:  '||v_location);
 when UTL_FILE.INVALID_MODE then
      DBMS_OUTPUT.PUT_LINE('Invalid Mode:  '||SQLERRM);
      htp.p('Invalid Mode:  '||SQLERRM);
 when UTL_FILE.INVALID_FILEHANDLE then
      DBMS_OUTPUT.PUT_LINE('Invalid File Handle: '||v_file_name);
      htp.p('Invalid File Handle: '||v_file_name);
 when UTL_FILE.INVALID_OPERATION then
      DBMS_OUTPUT.PUT_LINE('Invalid Operation: '||SQLERRM);
      htp.p('Invalid Operation: '||SQLERRM);
 when UTL_FILE.READ_ERROR then
      DBMS_OUTPUT.PUT_LINE('Read Error: '||SQLERRM);
      htp.p('Read Error: '||SQLERRM);
 when UTL_FILE.WRITE_ERROR then
      DBMS_OUTPUT.PUT_LINE('Write Error: '||SQLERRM);
      htp.p('Write Error: '||SQLERRM);
 when UTL_FILE.INTERNAL_ERROR then
      DBMS_OUTPUT.PUT_LINE('Internal Error: '||SQLERRM);
      htp.p('Internal Error: '||SQLERRM);
 when OTHERS then
      DBMS_OUTPUT.PUT_LINE('Other '||SQLERRM);
      htp.p('Other '||SQLERRM);
END CSV_INVOICES;

... in the INIT.ORA, here is the exact statement:

UTL_FILE_DIR = C:\DATA\ORACLE\DATAFILES

Hope you can use this...
--------------------------------------------------------------------
I am writing a simple sample procedure:

CREATE OR REPLACE
Procedure     TEST_CSV IS

tst_file UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('F:\UTL_FILE_DIR', 'TESTCSV.CSV', 'w');
invalid_path exception;
invalid_mode exception;
invalid_filehandle exception;
invalid_operation exception;
read_error exception;
write_error exception;
internal_error exception;

cursor cur_csv IS
select replace(tname, ',','')||','||  -- Use replace to
      replace(tabtype,',','')||','   -- get rid of commas
      replace(clusterid,',','')||',' ln -- in text fields.
FROM tab;

rec_csv cur_csv%rowtype;


BEGIN

for rec_csv in cur_csv loop
   utl_file.put_line(tst_file, rec_csv.ln);
end loop;

   utl_file.fclose(tst_file);
EXCEPTION
   when invalid_path then
      dbms_output.put_line('invalid path: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when invalid_mode then
      dbms_output.put_line('invalid mode: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when invalid_filehandle then
      dbms_output.put_line('invalid filehandle: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when invalid_operation then
      dbms_output.put_line('invalid operation: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when read_error then
      dbms_output.put_line('invalid read error: '||to_char(sqlcode)||' - '||sqlerrm) ;
  when write_error then
      dbms_output.put_line('write error: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when internal_error then
      dbms_output.put_line('internal_error: '||to_char(sqlcode)||' - '||sqlerrm) ;
   WHEN others THEN
      dbms_output.put_line('Error Code: '||to_char(sqlcode)||' - '||sqlerrm) ;
END; -- Procedure TEST_UTL
/

Starting from 9i you have to  use Oracle directories to place the files:

create or replace directory XMLDIR as 'E:\ICW_TASKS\XML\SOURCE\';
grant read on directory xmldir to my_user with grant option;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now