4 Replies Latest reply on Jul 5, 2017 7:13 AM by AndrewSayer Branched from an earlier discussion.

    Create CSV File

    a142e0a5-9115-47cf-9223-62c65d8382bd

      How to export the output of procedure to csv file automatically with dynamic file name using toad. please update the below procedure if u known the answer which was very helpful to me.

       

       

       

       

       

      create or replace procedure compare_tables(t1 in varchar2, t2 in varchar2, p_column in varchar2)
      is
          v_qry          varchar2(10000);
          TYPE T_MY_LIST IS TABLE OF VARCHAR2(32000);
          v_cols         T_MY_LIST;  -- list of columns
          v_types        T_MY_LIST;  -- list of columns' type
          v_cmp_cols     T_MY_LIST;  -- list of distinct
          v_col_t1_t2    T_MY_LIST;  -- t1 minus t2 - value of lines
          v_pk_t1_t2     T_MY_LIST;  -- associated PKs in t1 minus t2
          v_col_t2_t1    T_MY_LIST;  -- t2 minus t1 - value of lines
          v_pk_t2_t1     T_MY_LIST;  -- associated PKs in t2 minus t1
          TYPE T_Y_ IS TABLE OF VARCHAR2(32000) index by varchar2(1000);
          v_s                                            varchar2(1000); -- for indexing
          v_t1_t2        T_Y_; -- list of distinct lines from t1 - t2 /indexed by PK
          v_t2_t1        T_Y_; -- list of distinct lines from t2 - t1 /indexed by PK
      begin
          -- the below assumes all tables have a PK called simply "PK".
          v_qry:=p_column||', ';
          execute immediate ' select COLUMN_NAME, DATA_TYPE '
                            ||' from ALL_TAB_COLUMNS where TABLE_NAME=upper('''||t1||''')'
                  bulk collect into v_cols, v_types;
          -- building query with list of columns:
          FOR I in 1..v_cols.count loop
              v_qry := v_qry||v_cols(i)||'||';
          end loop;
          v_qry := v_qry||'''''';
          execute immediate ' select '||v_qry||' from '||t1||' minus select '||v_qry||' from '||t2
                  bulk collect into v_pk_t1_t2, v_col_t1_t2;
          execute immediate ' select '||v_qry||' from '||t2||' minus select '||v_qry||' from '||t1
                  bulk collect into v_pk_t2_t1, v_col_t2_t1;

          -- build indexed structures that will help compare lines brought by "minus" queries
          FOR I in 1..v_pk_t1_t2.count loop
              v_t1_t2(v_pk_t1_t2(i)):=v_col_t1_t2(i);
          end loop;
          FOR I in 1..v_pk_t2_t1.count loop
              v_t2_t1(v_pk_t2_t1(i)):=v_col_t2_t1(i);
          end loop;

          v_s := v_t1_t2.FIRST;          -- Get first element of array
          WHILE v_s IS NOT NULL LOOP
              if (v_t2_t1.exists(v_s)) then
                  -- distinct rows on same PK
                  DBMS_Output.PUT_LINE (p_column ||'  :: '    ||  v_s || ' ::  -> ' || v_t1_t2(v_s));

                  -- loop on each column joined on PK:
                  FOR i in 1..v_cols.count
                  loop
                      v_qry:= 'select '''||v_cols(i)||':''||'||t1||'.'||v_cols(i)||'||''<>''||'||t2||'.'||v_cols(i)
                            ||'  from '||t1||','||t2
                            ||' where '||t1||'.'||p_column||'='||t2||'.'||p_column
                            ||'   and '||t1||'.'||p_column||'='||v_s
                            ||'   and '||t1||'.'||v_cols(i)||'<>'||t2||'.'||v_cols(i)
                      ;
                      execute immediate v_qry bulk collect into v_cmp_cols;
                      FOR j in 1..v_cmp_cols.count loop
                          DBMS_Output.PUT_LINE (v_cmp_cols(j));
                      end loop;
                  end loop;
              else
                  DBMS_Output.PUT_LINE (p_column|| '  ::  '   || v_s || ' ::  is Not Available in Table :  ' || t2);           
              end if;
            v_s := v_t1_t2.NEXT(v_s);    -- Get next element of array
          END LOOP;
          v_s := v_t2_t1.FIRST;          -- Get first
          WHILE v_s IS NOT NULL LOOP
              if (not v_t1_t2.exists(v_s)) then
                  DBMS_Output.PUT_LINE (p_column ||'  :: '    || v_s || ' :: is Not Available in Table : ' || t1);           
              end if;
            v_s := v_t2_t1.NEXT(v_s);    -- Get next
          END LOOP;
      end compare_tables;
      /

       

      Message was edited by: BluShadow - Subject of thread changed

        • 1. Re: Introduce yourself!
          Timo Hahn

          Hi a142e0a5-9115-47cf-9223-62c65d8382bd  welcome to the OTN communities!

           

          Your question is clearly product related and should be asked in the SQL & PL/SQL  forum to get better attention.

           

          Timo

          • 2. Re: Create CSV File
            BluShadow

            *** Moderator note: I've now branched this off from the "Introduce Yourself" thread in the Getting Started space and moved it over to the SQL and PL/SQL space (as Timo suggested) and given it a meaningful subject line.  -- Please ensure you pick an appropriate product space for asking product related questions in future.

            • 3. Re: Create CSV File
              BluShadow

              In answer to your question, here's a starting point for writing a query to a CSV file....

               

              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_file IN VARCHAR2
                                                  ) 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;
              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_file,'w',32767);
                FOR j in 1..col_cnt
                LOOP
                  v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
                END LOOP;
                UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                -- This part outputs the DATA
                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;
              /

              Example:

               

              SQL> exec run_query('select * from emp','TEST_DIR','output.csv');

              PL/SQL procedure successfully completed.

              Output.csv 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

               

              Adapt to output different datatypes and styles are required.

               

              This accepts a query as a string (so it's dynamic in that sense) and outputs the data in CSV format.  I'm not quite sure what your code is doing, but it looks as though you could build up a single query and just pass that to this procedure to generate the output.  Or if it doesn't quite do what you want, you can at least get an idea of how to write files with it, using UTL_FILE.

               

              An alternative is to collect the data in to a CLOB variable, and then you can write that out to a file in one go using DBMS_XSLPROCESSOR.CLOB2FILE ... for which you can easily look up the syntax and arguments required.

              • 4. Re: Create CSV File
                AndrewSayer

                Why aren't you just using dbms_comparison to compare data in tables? It would do it much more efficiently and is fully supported.

                 

                You can use utl_file to write to files within plsql to the db server, or you could return the results to the user with a ref cursor (rather than loading up all the rows into memory and spitting them out), it's not clear how a filename should be determined but if you make the decision in plsql then you could also return that to the user using a bind variable output or another ref cursor. You can script sqlplus to spool a filename based on a result (that you assign to a defined variable using the column new value syntax), then you would just tell sqlplus to print the results ref cursor.