Solved

Spooling in Toad

Posted on 2008-06-23
7
3,839 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 48

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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - insert string with special characters 8 80
SQL Query 34 116
Oracle function to insert records? 15 48
minium over 4 numeric columns for each row in oracle 2 29
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

828 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