csv part 3

13 thoughts on “csv part 3”

1. Use Sql*Plus May 8, 2007 at 17:37

   What is wrong in running from Sql*Plus

   set colsep “;”
   select * from emp;

   Will display the same result..
2. Laurent Schneider Post authorMay 8, 2007 at 18:01

   sqlplus will use much more place and plenty of spaces. it will generate very long lines
3. a single clob May 8, 2007 at 19:30

   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 Post authorMay 8, 2007 at 20:22

   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;

5. Jeremy May 8, 2007 at 21:46

   Thanks… that was easy, wish I had seen that.
6. Karl Reitschuster May 9, 2007 at 06:08

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

   Karl
7. Laurent Schneider Post authorMay 9, 2007 at 06:22

   yes!
8. Laurent Schneider Post authorMay 9, 2007 at 09:11

   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 9, 2007 at 10:17

   oh yes!
   that’s a good idea!
   Karl
10. Vadim Tropashko May 9, 2007 at 17:44

    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 9, 2007 at 17:45

    text not test:-)

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

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

Comments are closed.