[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Spooling in Toad

Posted on 2008-06-23
7
Medium Priority
?
5,001 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

650 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