10 Replies Latest reply on Apr 18, 2013 5:56 AM by Karthick2003

    how to generate spool files in this way in sql

    NSK2KSN
      Hi,

      I have two tables

      create table nsk_t1 (a1 number, b1 number, ddl_action char(1), ddl_date date);

      create table nsk_t2 (a2 number, b2 number, ddl_action char(1), ddl_date date);

      insert into nsk_t1 values (1,10, 'I', sysdate);
      insert into nsk_t1 values (2,10, 'U', sysdate);
      insert into nsk_t1 values (3,10, 'D', sysdate);

      insert into nsk_t1 values (10,10, 'I', sysdate);
      insert into nsk_t1 values (20,10, 'U', sysdate);
      insert into nsk_t1 values (30,10, 'D', sysdate);

      I need to get the 6 spool files as nsk_t1_insert, nsk_t1_update, nsk_t1_delete, nsk_t2_insert, nsk_t2_update, nsk_t2_delete

      for every table I need to get 3 files for insert, update and delete

      what is the best way to achieve it.
        • 1. Re: how to generate spool files in this way in sql
          Karthick2003
          Spool is a SQL Plus command. The best way for you would be to use UTL_FILE and generate the files.
          • 2. Re: how to generate spool files in this way in sql
            BluShadow
            NSK2KSN wrote:
            Hi,

            I have two tables

            create table nsk_t1 (a1 number, b1 number, ddl_action char(1), ddl_date date);

            create table nsk_t2 (a2 number, b2 number, ddl_action char(1), ddl_date date);

            insert into nsk_t1 values (1,10, 'I', sysdate);
            insert into nsk_t1 values (2,10, 'U', sysdate);
            insert into nsk_t1 values (3,10, 'D', sysdate);

            insert into nsk_t1 values (10,10, 'I', sysdate);
            insert into nsk_t1 values (20,10, 'U', sysdate);
            insert into nsk_t1 values (30,10, 'D', sysdate);

            I need to get the 6 spool files as nsk_t1_insert, nsk_t1_update, nsk_t1_delete, nsk_t2_insert, nsk_t2_update, nsk_t2_delete

            for every table I need to get 3 files for insert, update and delete

            what is the best way to achieve it.
            What have you tried yourself?
            (and should I also ask why you have mutiple tables of the same structure? that looks like poor database design)
            • 3. Re: how to generate spool files in this way in sql
              NSK2KSN
              BluShadow,

              In reality I don't have table structures like that, so it's a good database design, posted here is only as an example.

              I have zero knowledge and this requirement is completely new to me and so have posted an example, and from few of the forum posts I have gone through and going through utl_file package,
              • 4. Re: how to generate spool files in this way in sql
                Marwim
                Hello,

                correct me when I'm wrong, but I assume that you want a file for each different action on each table.
                Do you want to generate the files when the DML is done or once each day?
                How is the DML triggered? Do you want to catch changes from within an application?

                Please give us more information.

                Regards
                Marcus
                • 5. Re: how to generate spool files in this way in sql
                  Karthick2003
                  Marwim wrote:
                  Hello,

                  correct me when I'm wrong, but I assume that you want a file for each different action on each table.
                  Do you want to generate the files when the DML is done or once each day?
                  How is the DML triggered? Do you want to catch changes from within an application?

                  Please give us more information.

                  Regards
                  Marcus
                  I never support NON-Transactional operation (Sending Email, Creating File etc) on a trigger. What if the transaction fails!! We still end up having a file created.
                  • 6. Re: how to generate spool files in this way in sql
                    BluShadow
                    Ok, so you're saying that your table structures may be different and you just need to output the rows within them to different files based upon whether a row contains a "U", "I" or "D".

                    Because your tables are different you'll want to be able to get out a varying amount of columns, so to do that you'll probably need to use something like DBMS_SQL and pass the queries/table names to it, get that to query the data and read it out of each column based on the position of the column, and then output it to a file (or multiple files in your case) as you need.

                    Here's one of my standard library answers for using DBMS_SQL to write out a CSV file, which you could use as a starting point...

                    -----

                    As sys user:
                    CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
                    /
                    GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
                    /
                    As myuser:
                    CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                                         ,p_dir IN VARCHAR2
                                                         ,p_header_file IN VARCHAR2
                                                         ,p_data_file IN VARCHAR2 := NULL) IS
                      v_finaltxt  VARCHAR2(4000);
                      v_v_val     VARCHAR2(4000);
                      v_n_val     NUMBER;
                      v_d_val     DATE;
                      v_ret       NUMBER;
                      c           NUMBER;
                      d           NUMBER;
                      col_cnt     INTEGER;
                      f           BOOLEAN;
                      rec_tab     DBMS_SQL.DESC_TAB;
                      col_num     NUMBER;
                      v_fh        UTL_FILE.FILE_TYPE;
                      v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
                    BEGIN
                      c := DBMS_SQL.OPEN_CURSOR;
                      DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
                      d := DBMS_SQL.EXECUTE(c);
                      DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
                      FOR j in 1..col_cnt
                      LOOP
                        CASE rec_tab(j).col_type
                          WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                          WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
                          WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
                        ELSE
                          DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                        END CASE;
                      END LOOP;
                      -- This part outputs the HEADER
                      v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
                      FOR j in 1..col_cnt
                      LOOP
                        v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
                      END LOOP;
                      --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                      UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                      IF NOT v_samefile THEN
                        UTL_FILE.FCLOSE(v_fh);
                      END IF;
                      --
                      -- This part outputs the DATA
                      IF NOT v_samefile THEN
                        v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
                      END IF;
                      LOOP
                        v_ret := DBMS_SQL.FETCH_ROWS(c);
                        EXIT WHEN v_ret = 0;
                        v_finaltxt := NULL;
                        FOR j in 1..col_cnt
                        LOOP
                          CASE rec_tab(j).col_type
                            WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                                        v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                            WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                                        v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
                            WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                                        v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
                          ELSE
                            DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                            v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                          END CASE;
                        END LOOP;
                      --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                        UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                      END LOOP;
                      UTL_FILE.FCLOSE(v_fh);
                      DBMS_SQL.CLOSE_CURSOR(c);
                    END;
                    This allows for the header row and the data to be written to seperate files if required.

                    e.g.
                    SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
                     
                    PL/SQL procedure successfully completed.
                    Output.txt file contains:
                    empno,ename,job,mgr,hiredate,sal,comm,deptno
                    7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
                    7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
                    7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
                    7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
                    7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
                    7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
                    7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
                    7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
                    7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
                    7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
                    7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
                    7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
                    7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
                    7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
                    The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.

                    Adapt to output different datatypes and styles are required.

                    -----

                    So, from that you should be able to adapt it so you can call it with your select statements or table names, and open up multiple files so you can write the "I", "U" and "D" records to each one as you query the data.
                    • 7. Re: how to generate spool files in this way in sql
                      NSK2KSN
                      Hi Marwim,

                      Thanks for your reply.

                      We have a cut off time every day say 5:30PM IST and after that we want to run these queries which will generate files.

                      These queries will run every day once
                      • 8. Re: how to generate spool files in this way in sql
                        NSK2KSN
                        Thanks for you reply BluShadow, will refer to your example and get back to you if I have any queries
                        • 9. Re: how to generate spool files in this way in sql
                          Marwim
                          I never support NON-Transactional operation (Sending Email, Creating File etc) on a trigger. What if the transaction fails!! We still end up having a file created.
                          I don't deny this. My question was which event will trigger the creation of the file.
                          I would never suggest to create a non transactional object from within a trigger {noformat};-){noformat}
                          • 10. Re: how to generate spool files in this way in sql
                            Karthick2003
                            Marwim wrote:
                            I never support NON-Transactional operation (Sending Email, Creating File etc) on a trigger. What if the transaction fails!! We still end up having a file created.
                            I don't deny this. My question was which event will trigger the creation of the file.
                            I would never suggest to create a non transactional object from within a trigger {noformat};-){noformat}
                            Sorry, I guess I wrongly interpreted your earlier post!!