csv part 3

In part 1, I tried with pure SQL+XML, but empty columns were missing. In Part 2, I had a mix between PL/SQL and XML functions. Now I would like to publish the solution Vadim Tropashko posted in the OTN Forums. It is a NO XML pipelined function. I did a bit formatting in it. And I pipe clobs.

Update: I added the column header


create or replace type strings as table of clob;
/
 
create or replace function CSV(txt varchar2)
return strings pipelined is 
  tmp clob;
  curid NUMBER; 
  desctab DBMS_SQL.DESC_TAB; 
  colcnt NUMBER; 
  namevar VARCHAR2(4000); 
begin
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, txt, dbms_sql.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
  FOR i IN 1 .. colcnt LOOP
    DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
  END LOOP;
  if DBMS_SQL.execute(curid) = 0 THEN
    dbms_lob.createtemporary(tmp, true);
  FOR i IN 1 .. colcnt LOOP
    if (i>1) then
        dbms_lob.writeappend(tmp, 1, ';');
      end if;
      dbms_lob.writeappend(tmp, 
        length(desctab(i).col_name), 
        desctab(i).col_name);
    END LOOP;
    pipe row(tmp);
  dbms_lob.freetemporary(tmp);
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    dbms_lob.createtemporary(tmp, true);
    FOR i IN 1 .. colcnt LOOP
      if (i>1) then
      dbms_lob.writeappend(tmp, 1, ';');
        end if;
      DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
      if (namevar is not null) 
    then
      dbms_lob.writeappend(tmp, length(namevar), 
                    namevar);
    end if;
    END LOOP;
    pipe row(tmp);
    dbms_lob.freetemporary(tmp);
  END LOOP;
  END IF;
  DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

select * from table(csv('select * from emp'));

EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO
7369;SMITH;CLERK;7902;17-DEC-80;800;;20
7499;ALLEN;SALESMAN;7698;20-FEB-81;1600;300;30
7521;WARD;SALESMAN;7698;22-FEB-81;1250;500;30
7566;JONES;MANAGER;7839;02-APR-81;2975;;20
7654;MARTIN;SALESMAN;7698;28-SEP-81;1250;1400;30
7698;BLAKE;MANAGER;7839;01-MAY-81;2850;;30
7782;CLARK;MANAGER;7839;09-JUN-81;2450;;10
7788;SCOTT;ANALYST;7566;19-APR-87;3000;;20
7839;KING;PRESIDENT;;17-NOV-81;5000;;10
7844;TURNER;SALESMAN;7698;08-SEP-81;1500;0;30
7876;ADAMS;CLERK;7788;23-MAY-87;1100;;20
7900;JAMES;CLERK;7698;03-DEC-81;950;;30
7902;FORD;ANALYST;7566;03-DEC-81;3000;;20
7934;MILLER;CLERK;7782;23-JAN-82;1300;;10

13 thoughts on “csv part 3”

  1. Laurent,

    As you have it, this works great, thanks!

    I have tried to modify it so that the CSV is selected into a single CLOB, and I am not having much luck.

    For example, take a select statement, pass it to the procedure just like you do, but instead of having the data piped by row, instead have the data returned after all the rows are retrieved. For example (not real code)

    declare
    csv_clob clob;

    begin
    select csv_output into csv_clob from table(csv_into_clob(‘SELECT * FROM EMP’));

    –now do something with the clob, email it, write it to a file, something like that.

    end;

  2. a single clob

    you just have to change my function to make it return a single clob

    
    create or replace function CSV_clob(txt varchar2)
    return clob is
      tmp clob;
      curid NUMBER;
      desctab DBMS_SQL.DESC_TAB;
      colcnt NUMBER;
      namevar VARCHAR2(4000);
    begin
      dbms_lob.createtemporary(tmp, true);
      curid := dbms_sql.open_cursor;
      dbms_sql.parse(curid, txt, dbms_sql.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
      FOR i IN 1 .. colcnt LOOP
        DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
      END LOOP;
      if DBMS_SQL.execute(curid) = 0 THEN  
      FOR i IN 1 .. colcnt LOOP
        if (i>1) then
            dbms_lob.writeappend(tmp, 1, ';');
          end if;
          dbms_lob.writeappend(tmp,
            length(desctab(i).col_name),
            desctab(i).col_name);
        END LOOP;
        WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
          if (i>1) then
          dbms_lob.writeappend(tmp, 1, ';');
            end if;
          DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
          if (namevar is not null)
        then
          dbms_lob.writeappend(tmp, length(namevar),
                        namevar);
        end if;
        END LOOP;
      END LOOP;
            dbms_lob.writeappend(tmp, 1, chr(10));
      END IF;
      DBMS_SQL.CLOSE_CURSOR(curid);
      return tmp;
      dbms_lob.freetemporary(tmp);
    end;
    /
    
    select csv_clob('select * from emp') from dual;
    

  3. For resulting lines smaller 4000 bytes would not be an array of varchar2 as returntype dramatically faster?

    Karl

  4. also possible is to use dbms_output, in 10gR2, you can have unlimited output and 32767 char/line

    
    CREATE OR REPLACE procedure CSV(query varchar2) IS
      curid NUMBER; 
      desctab DBMS_SQL.DESC_TAB; 
      colcnt NUMBER; 
      namevar VARCHAR2(4000); 
    begin
      curid := dbms_sql.open_cursor;
      dbms_sql.parse(curid, query, dbms_sql.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
      FOR i IN 1 .. colcnt LOOP
        DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
      END LOOP;
      IF DBMS_SQL.execute(curid) = 0 THEN
        FOR i IN 1 .. colcnt LOOP
          IF (i>1) then
            dbms_output.put(';');
          end IF;
          dbms_output.put(desctab(i).col_name);
        END LOOP;
        dbms_output.new_line;
        WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
          FOR i IN 1 .. colcnt LOOP
            IF (i>1) then
              dbms_output.put(';');
            end IF;
            DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
            IF (namevar IS NOT NULL) 
            then
              dbms_output.put(namevar);
            end IF;
          END LOOP;
          dbms_output.new_line;
        END LOOP;
      END IF;
    end;
    /
     
    execute csv('select * from emp');
     
    EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO
    7369;SMITH;CLERK;7902;17-DEC-80;800;;20
    7499;ALLEN;SALESMAN;7698;20-FEB-81;1600;300;30
    7521;WARD;SALESMAN;7698;22-FEB-81;1250;500;30
    7566;JONES;MANAGER;7839;02-APR-81;2975;;20
    7654;MARTIN;SALESMAN;7698;28-SEP-81;1250;1400;30
    7698;BLAKE;MANAGER;7839;01-MAY-81;2850;;30
    7782;CLARK;MANAGER;7839;09-JUN-81;2450;;10
    7788;SCOTT;ANALYST;7566;19-APR-87;3000;;20
    7839;KING;PRESIDENT;;17-NOV-81;5000;;10
    7844;TURNER;SALESMAN;7698;08-SEP-81;1500;0;30
    7876;ADAMS;CLERK;7788;23-MAY-87;1100;;20
    7900;JAMES;CLERK;7698;03-DEC-81;950;;30
    7902;FORD;ANALYST;7566;03-DEC-81;3000;;20
    7934;MILLER;CLERK;7782;23-JAN-82;1300;;10
    

  5. Which brings us back to the question why did the OP want concatenated columns output SQL query in the first place? He was unlikely going to use it relationally (that is join/project/select), anyway? A procedure that streams the test into dbms output is just as good as table function…

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>