Laurent Schneider

Laurent,

This is great - I tried the funcation out on some of our little larger tables and got an End of Communication Channel error message. We are not 9.2.0.6. Is there something I am missing?

Thanks,
Vidya

Laurent,

sorry about the type - I meant “Function” and got an
ERROR at line 1:
ORA-03113: end-of-file on communication channel

I can see the function being of immese help , for a lot of requests we get to generate data as coma delimited files.

Thanks for sharing this with us.

Can’t use large tables here - this is limited to row widths of 4000 including commas (due to the type tv).

CLOBs anyone?

create or replace
function CSV(tblName varchar2)
return strings pipelined is
tmp varchar2(1000);
i integer;
cursor c1 is select column_name from all_tab_columns where table_name = upper(tblName);
TYPE any_curtype IS REF CURSOR;
c2 any_curtype;
begin
i := 0;

FOR col in c1
LOOP
if i = 0 then
tmp := col.column_name;
else
tmp := tmp||’||’||col.column_name;
end if;
i := i+1;
END LOOP;

open c2 for ’select ‘||tmp||’ from ‘||tblName;
loop
FETCH c2 INTO tmp;
–EXIT WHEN c2%NOT_FOUND;
pipe row(tmp);
end loop;
end;

Create temporary view, read column info in dictionary, build dynamic SQL?

Seriously, I can’t imagine any application that would need comma separated list of columns. Relational databases work conveniently with data structured into the table format…

OK, this problem is actually easy. What would you do in Java/JDBC? Open a cursor, extract the first row, iterate through all the fields while concatenating them — pretty straightforward.

So why don’t we do the same in PL/SQL? All what is required is to be able to iterate through cursor fields, and

DBMS_SQL.COLUMN_VALUE(curid, i, namevar);

is what enables it.

I had to do this to figure out what was going on.

let me try again


<xsl:stylesheet version=”1.0″ xmlns:xsl =”http://www.w3.org/1999/XSL/Transform”&rt;
<xsl:output method=”text”&rt;</xsl:output&rt;
<xsl:variable name=”new_line” select=”‘&#xA;’”&rt;</xsl:variable&rt;
<xsl:template match=”ROWSET”&rt;
<xsl:apply-templates select=”ROW”&rt;</xsl:apply-templates&rt;
</xsl:template&rt;
<xsl:template match=”ROW”&rt;
<xsl:for-each select=”*”&rt;
<xsl:value-of select =”.”&rt;</xsl:value-of&rt;
<xsl:if test=”position() != last()”&rt;
<xsl:value-of select=”‘,’”&rt;</xsl:value-of&rt;
</xsl:if&rt;
</xsl:for-each&rt;
<xsl:value-of select=”$new_line”&rt;</xsl:value-of&rt;
</xsl:template&rt;
</xsl:stylesheet&rt;


CREATE OR REPLACE PACKAGE cvs_export IS
TYPE t_rec is record (
c_value varchar2(4000)
);
TYPE t_cvs IS TABLE OF t_rec;
FUNCTION cvs (p_stmt VARCHAR2)
RETURN t_cvs pipelined;
END;

CREATE OR REPLACE PACKAGE BODY cvs_export IS
FUNCTION cvs (p_stmt VARCHAR2)
RETURN t_cvs PIPELINED IS
l_cur INTEGER;
l_col VARCHAR2 (2000);
l_tmp NUMBER;
l_col_cnt INTEGER;
l_desc DBMS_SQL.desc_tab;
l_rec cvs_export.t_rec;
BEGIN
l_cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse (l_cur, p_stmt, DBMS_SQL.native);
l_col_cnt := 0;
FOR i IN 1 .. 255 LOOP
BEGIN
DBMS_SQL.define_column (l_cur, i, l_col, 2000);
l_col_cnt := i;
EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -1007) THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;
DBMS_SQL.define_column (l_cur, 1, l_col, 2000);
–
l_tmp := DBMS_SQL.EXECUTE (l_cur);
DBMS_SQL.describe_columns (l_cur, l_col_cnt, l_desc);
LOOP
EXIT WHEN DBMS_SQL.fetch_rows (l_cur)

FOR nn IN l_desc.FIRST .. l_desc.LAST LOOP
DBMS_SQL.column_value (l_cur, nn, l_col);
l_rec.c_value := l_rec.c_value || l_col || ‘;’;
END LOOP;
PIPE ROW (l_rec);
l_rec.c_value := NULL;
END LOOP;
DBMS_SQL.close_cursor (l_cur);
RETURN;
END;
END;

select * from table(cvs_export.cvs(’select * from all_objects’))

PS

Thanks to T.Kyte for DBMS_SQL.parse trick

Perhaps I’m missing something here but SQL Developer has this functionality built right in. Just right click on your result and choose Export > CSV.

hahah….

Sometimes, it’s all about the journey, not the destination eh…

Thanks

Or if you want to create CSV of data without a tool thru sqlplus……

Hi,

Is data can be downloaded using different charcter other than ‘,’ using this solution ?

Sorry for my limited knowledge about XMLDB.

thanks & regards
PJP

hi
I want to convert .csv file into xml format
could you pls help me, it is very urgent

i am new to this,could Pls guide me

could you Pls send one perl or shell script that convert Csv into Xml format

i am getting syntax error at awk -F
could you Pls guide me

i am running this in Perl, i am getting below error

Unrecognized file test: -F

my o.s is unix , i copied your awk program into as Xml2Csv.sh and ran it
but i am getting the below error like

(this is the script)

#!/usr/bin/ksh +x

awk -F’;’ ‘NR==1{print “”;F=NF;for(i=1;i”;for(i=1;i”"”;
print “”}END{print “”}’ x.csv

(these are error i am getting)

awk: syntax error near line 2
awk: illegal statement near line 2
awk: syntax error near line 3
awk: illegal statement near line 3

excellent, perl scripting is working fine.
your out put is
Laurent
1000
Marc
2000
Jack
1500

but i want like this

Laurent
1000
Marc
2000
Jack
1500

could you pls guide me

sorry
pls ignore my previus mail

Laurent
1000
Marc
2000
Jack
1500

i want like this

Laurent
1000
Marc
2000
Jack
1500

sorry i am not able sent ,
i will explain
i want output like

Laurent
1000
Marc
2000
Jack
1500

i want out put like
xml version=1
table
NAME>Laurent

excellent , both sh and perl is working fine but i want step by step ,
not in single line, the first line is coming in single line .
could pls guide me
pls help

tanks for ur help

Laurent Schneider,
could you Pls do a favor for me,
we have lot of functions in oracle like attribute, element,xmlforest ..etc
like this, is there any functions to convert csv to xml by using functions.
Pls guide me with examples

Laurent,
we aren’t using oracle, so we can’t use as u mentioned,
so Pls advice me.