2 Replies Latest reply: May 16, 2013 1:25 AM by Senthilkumar S RSS

    SPOOL file for different sql with file name

    Senthilkumar S
      Hi

      I have below script to create spool file
      alter session set nls_date_format = 'DD-MON-YYYY-HH24-MI-SS';
      
      define file_time=date
      column tm new_value file_time
      select 'OTHER_TABLE_BATCH_'||SYSDATE||'.log' tm from dual;
      
      SPOOL C:\Users\sesampat\Desktop\'&file_time'
      
      @C:\Users\sesampat\Desktop\dual.sql
      @C:\Users\sesampat\Desktop\dual1.sql
      SPOOL OFF
      but its creating o/p as below
      DNAME                          ENAME                                 SAL
      ------------------------------ ------------------------------ ----------
      RESEARCH                       SMITH                                 800
      RESEARCH                       SMITH                                 800
      RESEARCH                       ADAMS                                1100
      RESEARCH                       JONES                                2975
      RESEARCH                       FORD                                 3000
      RESEARCH                       SCOTT                                3000
      SALES                          JAMES                                 950
      SALES                          MARTIN                               1250
      SALES                          WARD                                 1250
      SALES                          TURNER                               1500
      SALES                          ALLEN                                1600
      SALES                          BLAKE                                2850
      
      12 rows selected.
      
          DEPTNO DNAME          LOC          
      ---------- -------------- -------------
              10 ACCOUNTING     NEW YORK
              20 RESEARCH       DALLAS
              30 SALES          CHICAGO
              40 OPERATIONS     BOSTON
              50 HR_DEPT        INDIA
              60 HR\DEPT        INDIA
              70 HR/DEPT        INDIA
      
      7 rows selected.
      
      DNAME                          ENAME                                 SAL
      ------------------------------ ------------------------------ ----------
      RESEARCH                       SMITH                                 800
      RESEARCH                       SMITH                                 800
      RESEARCH                       ADAMS                                1100
      RESEARCH                       JONES                                2975
      RESEARCH                       FORD                                 3000
      RESEARCH                       SCOTT                                3000
      SALES                          JAMES                                 950
      SALES                          MARTIN                               1250
      SALES                          WARD                                 1250
      SALES                          TURNER                               1500
      SALES                          ALLEN                                1600
      SALES                          BLAKE                                2850
      
      12 rows selected.
      
          DEPTNO DNAME          LOC          
      ---------- -------------- -------------
              10 ACCOUNTING     NEW YORK
              20 RESEARCH       DALLAS
              30 SALES          CHICAGO
              40 OPERATIONS     BOSTON
              50 HR_DEPT        INDIA
              60 HR\DEPT        INDIA
              70 HR/DEPT        INDIA
      
      7 rows selected.
      But i need to create o/p(with file individual *.sql and each SQL o/p in spool file) as below
      dual.sql
      
      DNAME                          ENAME                                 SAL
      ------------------------------ ------------------------------ ----------
      RESEARCH                       SMITH                                 800
      RESEARCH                       SMITH                                 800
      RESEARCH                       ADAMS                                1100
      RESEARCH                       JONES                                2975
      RESEARCH                       FORD                                 3000
      RESEARCH                       SCOTT                                3000
      SALES                          JAMES                                 950
      SALES                          MARTIN                               1250
      SALES                          WARD                                 1250
      SALES                          TURNER                               1500
      SALES                          ALLEN                                1600
      SALES                          BLAKE                                2850
      
      12 rows selected.
      
          DEPTNO DNAME          LOC          
      ---------- -------------- -------------
              10 ACCOUNTING     NEW YORK
              20 RESEARCH       DALLAS
              30 SALES          CHICAGO
              40 OPERATIONS     BOSTON
              50 HR_DEPT        INDIA
              60 HR\DEPT        INDIA
              70 HR/DEPT        INDIA
      
      7 rows selected.
      
      
      dual1.sql
      
      DNAME                          ENAME                                 SAL
      ------------------------------ ------------------------------ ----------
      RESEARCH                       SMITH                                 800
      RESEARCH                       SMITH                                 800
      RESEARCH                       ADAMS                                1100
      RESEARCH                       JONES                                2975
      RESEARCH                       FORD                                 3000
      RESEARCH                       SCOTT                                3000
      SALES                          JAMES                                 950
      SALES                          MARTIN                               1250
      SALES                          WARD                                 1250
      SALES                          TURNER                               1500
      SALES                          ALLEN                                1600
      SALES                          BLAKE                                2850
      
      12 rows selected.
      
          DEPTNO DNAME          LOC          
      ---------- -------------- -------------
              10 ACCOUNTING     NEW YORK
              20 RESEARCH       DALLAS
              30 SALES          CHICAGO
              40 OPERATIONS     BOSTON
              50 HR_DEPT        INDIA
              60 HR\DEPT        INDIA
              70 HR/DEPT        INDIA
      
      7 rows selected.