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. a single clob

    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. Laurent Schneider Post author

    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. Karl Reitschuster

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

    Karl

  4. Laurent Schneider Post author

    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. Vadim Tropashko

    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…

  6. Pingback: Pythian Group Blog » Log Buffer #44: a Carnival of the Vanities for DBAs

  7. Pingback: Log Buffer #44: A Carnival of the Vanities for DBAs

Comments are closed.