Home > Blogroll, sql > csv part 3

csv part 3

May 8th, 2007
Goto comments Leave a comment

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'));

<b>EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO</b>
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

Blogroll, sql

  1. Use Sql*Plus
    May 8th, 2007 at 17:37 | #1
    Reply | Quote

    What is wrong in running from Sql*Plus

    set colsep “;”
    select * from emp;

    Will display the same result..

  2. Laurent Schneider
    May 8th, 2007 at 18:01 | #2
    Reply | Quote

    sqlplus will use much more place and plenty of spaces. it will generate very long lines

  3. a single clob
    May 8th, 2007 at 19:30 | #3
    Reply | Quote

    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;

  4. Laurent Schneider
    May 8th, 2007 at 20:22 | #4
    Reply | Quote

    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&gt;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&gt;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;

  5. Jeremy
    May 8th, 2007 at 21:46 | #5
    Reply | Quote

    Thanks… that was easy, wish I had seen that.

  6. Karl Reitschuster
    May 9th, 2007 at 06:08 | #6
    Reply | Quote

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

    Karl

  7. Laurent Schneider
    May 9th, 2007 at 06:22 | #7
    Reply | Quote

    yes!

  8. Laurent Schneider
    May 9th, 2007 at 09:11 | #8
    Reply | Quote

    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

  9. Karl Reitschuster
    May 9th, 2007 at 10:17 | #9
    Reply | Quote

    oh yes!
    that’s a good idea!
    Karl

  10. Vadim Tropashko
    May 9th, 2007 at 17:44 | #10
    Reply | Quote

    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…

  11. Vadim Tropashko
    May 9th, 2007 at 17:45 | #11
    Reply | Quote

    text not test:-)

  1. May 11th, 2007 at 17:41 | #1
    Pythian Group Blog » Log Buffer #44: a Carnival of the Vanities for DBAs