This discussion is archived
7 Replies Latest reply: Nov 22, 2012 8:51 PM by BillyVerreynne RSS

spool into multiple files

Smidreb Newbie
Currently Being Moderated
i have a table which has various customers information for each and every customer id .. I am calling a function that generates records of these customer using dbms_out.put_line.
Now I want to spool the data into a file if the function returns true and the file name should be each customer id how can i spool multiple files in pl/sql ?
Set serveroutput OFF 
   set echo off 
 set head off 
 set scan off 
 set feedback off 
 set linesize 1000 
 set verify off 
 Set serveroutput ON  
  declare 
     f_t_flg    BOOLEAN; 
     v_cnt number :=1; 
   BEGIN 
     FOR i in ( 
        SELECT distinct cust_id FROM customer  WHERE region ='NEA')  

     LOOP 
             BEGIN 
            f_t_flg := fn_comp_cst(i.cust_id); 
             END; 
     
         IF f_t_flg  THEN
         Set serveroutput ON
        SPOOL  i cust_id code..log
       
        end if;  
     END LOOP; 
   
  END; 
 /  
 spool off 


 
CREATE OR REPLACE FUNCTION fn_comp_cst( p_cst_id_num IN VARCHAR2)
 RETURN  BOOLEAN   
 IS  
 TYPE cstCurTyp IS REF CURSOR;
   cst_info   cstCurTyp;
     cust_rec   customer%ROWTYPE;

    v_count NUMBER;
    sql_stmnt varchar2(4000);
    
   BEGIN
          
                 sql_stmnt:='select cst_adc_code, cst_num, cst_addr_code, country, cst_typ, csr_perform_desc, cst_add_desc, cust_name, cust_full_name ,NULL,NULL 
from  ref_data.cust_info  where cst_num LIKE  :i'
                  || '  UNION ALL '
|| ' select cst_adc_code, cst_num, cst_addr_code, country, cst_typ, csr_perform_desc, cst_add_desc, cust_name, cust_full_name ,NULL,NULL 
       from ref_data.cust_hist WHERE cst_num LIKE  :j';
               dbms_output.put_line('cst_adc_code' ||chr(9)||'cst_num'     
           
                ||chr(9)||'cst_addr_code'||chr(9)||'Country'||chr(9)||'cst_typ'||chr(9)||'csr_perform_desc'||chr(9)||'cst_add_desc'||
                  chr(9)||'cust_name'||chr(9)||'cust_full_name');

             OPEN cst_info FOR sql_stmnt USING  p_cst_id_num;
                LOOP
                     FETCH cst_info INTO cst_rec;
                    EXIT WHEN cst_info%NOTFOUND;
                  dbms_output.put_line(cst_rec.csr_perform_desc ||chr(9)||cst_rec.cst_num||chr(9)||cst_rec.cst_addr_code||chr(9)|| cst_rec.country||chr(9)||cst_rec.cust_name||chr(9)||cst_rec.cust_full_name);    
                       -- process record
                END LOOP;
            CLOSE cst_info;  
     IF v_count > 0  
     THEN
       RETURN TRUE;
     ELSE
       RETURN FALSE;
     END IF;
           
   END;
/
thanks
  • 1. Re: spool into multiple files
    sb92075 Guru
    Currently Being Moderated
    do you know & understand that SQL, PL/SQL & SQL*Plus are separate & distinct from each other?
    What is valid in one context may not be valid in either of the other two.
    "SPOOL" is a SQL*Plus directive & is unknown & invalid in PL/SQL.

    If you need to write to multiple different files from PL/SQL, you need to use UTL_FILE.
  • 2. Re: spool into multiple files
    Smidreb Newbie
    Currently Being Moderated
    Thanks ..
    I don't have access to the server, so writing files to the server would probably not work.
    is there anyother way I could do this? .... does not have to be necessarliy how I am doing it..
  • 3. Re: spool into multiple files
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    I'm not sure I understand the problem.
    I'm pretty certain that, whatever it is, it will be simpler, more efficient and more robust if you could use dbms_output to create files on the database server's file system, as suggested earlier. Given that you can't do that, here's an alternative:

    If you want to run a query (or a procedure, or a script, or whatever) N times (where N is determined at run time), with N different sets of parameters, and have the output go to N different spool files, then you can do something like this in SQL*Plus:

    First, create a script that does what you want 1 time, with 1 given set of parameters, and have the ouput go to 1 file.
    For example, say we want a little information about employees in a given department in scott.emp, we could write a script like this:
    --     Single_Dept.sql - Report for 1 Department
    
    --     Use:
    --         SQL>  @single_dept     10
    
    --     shows information about employees in deptartment_10.
    --     Output is spooled to dept_10.txt
    
    SPOOL     &all_dept_dir\dept_&1..lst
    
    SELECT     empno, ename, hiredate, deptno
    FROM     scott.emp
    WHERE     deptno     = &1
    ;
    
    SPOOL     OFF
    Once you have that working correctly, write another script that creates, then runs, a script like this:
    @c:\dept_reports\single_dept         10
    @c:\dept_reports\single_dept         20
    @c:\dept_reports\single_dept         30
    Such a script might look like this:
    --     All_Dept.sql     - Create all_dept_subscript.sql that contains
    --               @&single_dept  x
    --               statements for each x=deptno, and runs it
    
    DEFINE     all_dept_dir     = c:\dept_reports
    SET     VERIFY          OFF
    
    PROMPT     ======  Creating All_Dept_Subscript.sql  =====
    
    -- Turn off features that help people read output, but confuse scripts
    SET     FEEDBACK        OFF
    SET     PAGESIZE        0
    SET     TRIMSPOOL     ON
    
    SPOOL     &all_dept_dir\all_dept_subscript.sql
    
    SELECT DISTINCT
           '@&all_dept_dir\single_dept'
    ,       deptno
    FROM       scott.emp
    ORDER BY  deptno
    ;
    
    SPOOL     OFF
    
    
    -- Turn on features that help people read output, but confuse scripts
    SET     FEEDBACK        5
    SET     PAGESIZE        50
    
    
    PROMPT     =====  Running All_Dept_Subscript.sql  =====
    
    @&all_dept_dir\all_dept_subscript
  • 4. Re: spool into multiple files
    Smidreb Newbie
    Currently Being Moderated
    Thanks a lot it worked like a charm... the only thing now is conditional spooling.. if the function returns true spool it to a file else don't.. is there any trick to do this as well?
    Thanks once again .. I really appreciate ur help...
  • 5. Re: spool into multiple files
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Smidreb wrote:
    Thanks a lot it worked like a charm... the only thing now is conditional spooling.. if the function returns true spool it to a file else don't.. is there any trick to do this as well?
    Sorry, I don't understand. Post a concrete example, with the oputput you want, and an explanation of why you want that output. E.g. "FILE_1.TXT should contain ... because ... There should not be a FILE_2.TXT because ...".

    See {message:id=9597683} for some ideas about how to do flow-of-control in SQL*Plus.
  • 6. Re: spool into multiple files
    Smidreb Newbie
    Currently Being Moderated
    In my example above if the fucntion fn_comp_cst(i.cust_id) returns TRUE only then I want to spool into the file if it returns false I don;t need to spool it.
  • 7. Re: spool into multiple files
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    SQL*Plus does not support conditional branching. It needs to be hacked.

    1 script containing one part of the branch. E.g. if an IF-THEN-ELSE branch, then script1.sql for the TRUE branch and script2.sql for the FALSE branch.

    SQL is then used to apply the condition and outputs the branch (script name) to follow. This is then stored in a substitution variable in SQL*Plus and executed.

    Approach/hack explained in more detail in {message:id=4105290}.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points