7 Replies Latest reply: Nov 22, 2012 10:51 PM by Billy~Verreynne RSS

    spool into multiple files

    Smidreb
      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
          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
            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
              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
                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
                  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
                    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
                      Billy~Verreynne
                      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}.