Forum Home » Database » SQL and PL/SQL

Thread: Using XMLSEQUENCE to give a single, csv list of column names

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 8 - Pages: 1 - Last Post: Aug 30, 2007 3:19 AM Last Post By: cubittm
cubittm

Posts: 485
Registered: 04/16/03
Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 2:23 AM
Click to report abuse...   Click to reply to this thread Reply
Using this query:
select 

column_name 

from 

cols 

where 

table_name = 'PAYMENT_PROPOSAL_REP'

order by

column_id

I can see a list of columns in a table.

Rather than a long list going down the page:
COL1
COL2
COL3
COL4

I would like to create a single list, separated by commas like this:
COL1, COL2, COL3, COL4

I am sure XMLSEQUENCE can be used but would like guidance please.

I have read up and still have no joy!
michaels2

Posts: 6,119
Registered: 09/24/06
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 2:48 AM   in response to: cubittm in response to: cubittm
Click to report abuse...   Click to reply to this thread Reply
No need for xmlsequence so far:

michaels>  select rtrim (xmlagg (xmlelement (c, column_name || ',')).extract ('//text()'), ',') csv
  from cols
 where table_name = 'EMP'
/ 
CSV         
--------------------
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
cubittm

Posts: 485
Registered: 04/16/03
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 2:54 AM   in response to: michaels2 in response to: michaels2
Click to report abuse...   Click to reply to this thread Reply
Thank you (again!) Michaels, however..

the columns are listed in a different order.

Although I use:
select rtrim (xmlagg (xmlelement (c, column_name || ',')).extract ('//text()'), ',') csv
from cols where table_name = 'mytable' order by column_id

they are not listed in the same order as if I were to do :
select column_name
from cols where table_name = 'mytable' order by column_id

Any ideas why that might be the case?

Also, can you confirm, does the lower case c indicate a column element?

Message was edited by:
cubittm
michaels2

Posts: 6,119
Registered: 09/24/06
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 2:58 AM   in response to: cubittm in response to: cubittm
Click to report abuse...   Click to reply to this thread Reply
Any ideas why that might be the case?

Add an order by then:

select rtrim (xmlagg (xmlelement (c, column_name || ',') order by column_id).extract ('//text()'), ',') csv

  from cols

 where table_name = 'EMP'

does the lower case c indicate a column element?

It is just a dummy xml tag necessary to have a valid xml
cubittm

Posts: 485
Registered: 04/16/03
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 3:13 AM   in response to: michaels2 in response to: michaels2
Click to report abuse...   Click to reply to this thread Reply
Thanks again, very useful lesson in very useful syntax.

In your examples, you format the csv to 75 characters so you get text like this:
RESULT_KEY,COMPANY,PROPOSAL_ID,ORDER_COLUMN,COMPANY_NAME,CREATION_DATE,USER     

_ID,INCLUDE_CRED_INVOICES,ALLOCATE_CRED_INVOICES,INCLUDE_SUPP_NEG_BAL,USE_O     

NLY_DEF_WAY,SHOW_NETTING_POSSIBILITY,WAY_ID,PRIORITY,UNTIL_PLAN_PAY_DATE,FI     

RST_POSSIBLE_PAY_DATE,CURRENCIES,SUPPLIER,COMPANY_LIST,AMOUNT,ROWSTATE,PART     

Y_IDENTITY,PARTY_NAME,PAYEE,PAYEE_NAME,CURRENCY,CURR_AMOUNT,DISC_CURR_AMOUN     

T,REMAINING_AMOUNT,DUE_DATE,PLANED_PAYMENT_DATE,DISCOUNT_DATE,NOTE,LEDGER_I     

TEM_SERIES_ID,LEDGER_ITEM_ID,NETTING_POSSIBILITY,ACC_CURR_AMOUNT,SORT_ORDER     

,CHILD_COMPANY,PLANED_PAYMENT_DATE_2,NCF_REFERENCE  



2602064,"OKM","7","CKK7007222             2007-07-76          OK078742          

                                     ","Foobar Ltd.

ited","15-AUG-07","BLOGGSF","No","No","No","Yes","Yes","TRANSFER;",,"17-AU     

G-07","16-AUG-07","CHF","%","USB",,"Created","7007222","Dan the Man","7007

222","Dan the Man","CHF",2200,0,0,"12-JUL-07","23-AUG-07","","","SI","FA07

3342","-",405.15,"","USB","",""

When it opens in Excel, for example, the line breaks throw it so it ends up like this:
RESULT_KEY	COMPANY	PROPOSAL_ID	ORDER_COLUMN	COMPANY_NAME	CREATION_DATE	USER     						

_ID	INCLUDE_CRED_INVOICES	ALLOCATE_CRED_INVOICES	INCLUDE_SUPP_NEG_BAL	USE_O     								

NLY_DEF_WAY	SHOW_NETTING_POSSIBILITY	WAY_ID	PRIORITY	UNTIL_PLAN_PAY_DATE	FI     							

RST_POSSIBLE_PAY_DATE	CURRENCIES	SUPPLIER	COMPANY_LIST	AMOUNT	ROWSTATE	PART     						

Y_IDENTITY	PARTY_NAME	PAYEE	PAYEE_NAME	CURRENCY	CURR_AMOUNT	DISC_CURR_AMOUN     						

T	REMAINING_AMOUNT	DUE_DATE	PLANED_PAYMENT_DATE	DISCOUNT_DATE	NOTE	LEDGER_I     						

TEM_SERIES_ID	LEDGER_ITEM_ID	NETTING_POSSIBILITY	ACC_CURR_AMOUNT	SORT_ORDER     								

	CHILD_COMPANY	PLANED_PAYMENT_DATE_2	NCF_REFERENCE  									



2602064	OKM	7	"CKK7007222             2007-07-76          OK078742          

                                     "	"Foobar Ltd.

ited"	15-Aug-07	BLOGGSF	No	No	No	Yes	Yes	TRANSFER;

Actually, put " " around the columns and it is fixed.

Just thought I would share that.

select replace(rtrim (xmlagg (xmlelement (c, '"' || column_name || '",') order by column_id).extract ('//text()'), ','),'"','"') csv

from cols where table_name = 'mytable'

/



"RESULT_KEY","COMPANY","PROPOSAL_ID","ORDER_COLUMN","COMPANY_NAME","CREATIO

N_DATE","USER_ID","INCLUDE_CRED_INVOICES","ALLOCATE_CRED_INVOICES","INCLUDE

_SUPP_NEG_BAL","USE_ONLY_DEF_WAY","SHOW_NETTING_POSSIBILITY","WAY_ID","PRIO

RITY","UNTIL_PLAN_PAY_DATE","FIRST_POSSIBLE_PAY_DATE","CURRENCIES","SUPPLIE

R","COMPANY_LIST","AMOUNT","ROWSTATE","PARTY_IDENTITY","PARTY_NAME","PAYEE"

,"PAYEE_NAME","CURRENCY","CURR_AMOUNT","DISC_CURR_AMOUNT","REMAINING_AMOUNT

","DUE_DATE","PLANED_PAYMENT_DATE","DISCOUNT_DATE","NOTE","LEDGER_ITEM_SERI

ES_ID","LEDGER_ITEM_ID","NETTING_POSSIBILITY","ACC_CURR_AMOUNT","SORT_ORDER

","CHILD_COMPANY","PLANED_PAYMENT_DATE_2","NCF_REFERENCE"
cubittm

Posts: 485
Registered: 04/16/03
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 7:20 AM   in response to: cubittm in response to: cubittm
Click to report abuse...   Click to reply to this thread Reply
Michael, I seem to have found a restriction in the (default) use of that code.

When I try to retrieve data from more than 66 columns, it errors on the 67th.

If I only include column_id 1 to 66 its ok, 1 to 67 it errors. 2 to 67 is ok.

The error is:
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 206
ORA-06512: at line 1

Do you know if there is a buffer or maximum value I can alter?

Thanks
cubittm

Posts: 485
Registered: 04/16/03
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 7:26 AM   in response to: cubittm in response to: cubittm
Click to report abuse...   Click to reply to this thread Reply
Sorry, I should make it clear that I am refering to the code:

SELECT RTRIM(REPLACE (REPLACE (d.COLUMN_VALUE.EXTRACT ('ROW/C/C/text()'),'"','"'),'amp;quot;','"'),',') csv

 FROM (SELECT 'select '|| RTRIM(REPLACE(XMLAGG(XMLELEMENT(c,'xmlelement(c, '|| CASE WHEN data_type IN('VARCHAR2', 'DATE') THEN '''"'' || ' END || column_name || CASE WHEN data_type IN ('VARCHAR2', 'DATE') THEN ' || ''"''' END || ' ||'','') c,' ) ORDER BY column_id ).EXTRACT ('C/text()').getclobval (), ''', '''' ), 

',' ) || ' from ' || :tab1  || ' 

where result_key=57 and rownum=1' cols  

FROM cols WHERE table_name = UPPER (:tab1)  and column_id 

between 2 and 67

),  TABLE (XMLSEQUENCE (DBMS_XMLGEN.getxmltype (cols).EXTRACT ('ROWSET/ROW')  )  ) d

/

Anyway, I will create a new thread, since it's away from this issue really.
michaels2

Posts: 6,119
Registered: 09/24/06
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 29, 2007 8:44 AM   in response to: cubittm in response to: cubittm
Click to report abuse...   Click to reply to this thread Reply
The CREATE TABLE script would be helpful to see if I can reproduce the problem.
Furthermore what is your database version?
cubittm

Posts: 485
Registered: 04/16/03
Re: Using XMLSEQUENCE to give a single, csv list of column names
Posted: Aug 30, 2007 3:19 AM   in response to: michaels2 in response to: michaels2
Click to report abuse...   Click to reply to this thread Reply
Sorry for the delay, had "fun" getting example set up buthere it is.

I am using 9i but it failed in 10g too.

drop table sample_table;



create table sample_table (

RESULT_KEY NUMBER,

ROW_NO NUMBER,

COMPANY VARCHAR2(2000),

DESCRIPTION VARCHAR2(2000),

OWNER VARCHAR2(2000),

CREATION_DATE DATE,

MODIFICATION_DATE DATE,

ROW_TEMPLATE_ID VARCHAR2(2000),

ROW_TEMPLATE_DESCRIPTION VARCHAR2(2000),

COLUMN_TEMPLATE_ID VARCHAR2(2000),

COLUMN_TEMPLATE_DESCRIPTION VARCHAR2(2000),

DEFAULT_LANGUAGE VARCHAR2(2000),

BUDGET_VERSION VARCHAR2(2000),

TEMPLATES_EXCHANGABLE VARCHAR2(2000),

FIXED_COLUMN_TEMPLATE_ID VARCHAR2(2000),

TEMPLATE_TYPE VARCHAR2(2000),

TEMPLATE_ID VARCHAR2(2000),

REFERENCED_TEMPLATE_ID VARCHAR2(2000),

ROWCOL_DESCRIPTION VARCHAR2(2000),

ROWCOL_ID VARCHAR2(2000),

VISIBLE VARCHAR2(2000),

TYPE VARCHAR2(2000),

HEADER VARCHAR2(2000),

DR_USE_IN_TOTAL_CALCULATIONS VARCHAR2(2000),

DR_CHANGE_OF_SIGN VARCHAR2(2000),

DATABASE_SOURCE_DESCR VARCHAR2(2000),

PARAMETER_VALUE VARCHAR2(2000),

RTR_REFERENCED_TEMPLATE_ID VARCHAR2(2000),

RTR_USE_IN_TOTAL_CALCULATIONS VARCHAR2(2000),

CR_USE_IN_TOTAL_CALCULATIONS VARCHAR2(2000),

CC_CALCULATION_ORDER NUMBER,

CC_OVERRIDING VARCHAR2(2000),

COM_CALCULATION_ORDER NUMBER,

COM_OVERRIDING VARCHAR2(2000),

COMPARISON_COLUMN_NAME VARCHAR2(2000),

VALUE_TEXT VARCHAR2(2000),

FUNCTIONAL_TYPE VARCHAR2(2000),

OPERATION_ORDER NUMBER,

ARG_ROWCOL_ID VARCHAR2(2000),

ARGUMENT VARCHAR2(2000),

ARGUMENT_TYPE VARCHAR2(2000),

CALC_OPERATOR VARCHAR2(2000),

TERM VARCHAR2(2000),

TERM_NAME VARCHAR2(2000),

TERM_LEVEL VARCHAR2(2000),

INCLUDE_NULL VARCHAR2(2000),

VALUE1 VARCHAR2(2000),

VALUE2 VARCHAR2(2000),

OPERATOR VARCHAR2(2000),

GROUPING_LEVEL NUMBER,

LEVEL_STATIC_DESC VARCHAR2(2000),

G_TERM VARCHAR2(2000),

G_TERM_NAME VARCHAR2(2000),

G_TERM_LEVEL VARCHAR2(2000),

TOTAL_TEXT VARCHAR2(2000),

FLAG VARCHAR2(2000),

NFLAG NUMBER,

NFLAGPAGE NUMBER,

COMPARISION_ROW_ID VARCHAR2(2000),

INCLUDESUBTEMPLATES VARCHAR2(2000),

YEAR VARCHAR2(2000),

PERIOD VARCHAR2(2000),

BUDGET_DESC VARCHAR2(2000),

LANGUAGE_DESC VARCHAR2(2000),

SUP_COLUMN_NAME VARCHAR2(20),

SUP_OPERATOR VARCHAR2(20),

SUP_FROM_VALUE VARCHAR2(20),

SUP_TO_VALUE VARCHAR2(20),

TR_PRINT_VOU_TOT VARCHAR2(5),

TR_DATA_COLUMN VARCHAR2(20),

TR_DATACOL_DESCRIPTION VARCHAR2(200),

TR_DATA_ROW VARCHAR2(20),

TR_DATAROW_DESCRIPTION VARCHAR2(200),

AUTHORIZATION VARCHAR2(200)

);



insert into ifsapp.sample_table values (

57,13,'','','1234 Ammlionci and Lendespor De               0.00               0.00         9789.04            0.00         9789.04            9789.04','','','DM','','DM','','DM','','DM','','','','','N','','','','FALSE','','','','','','','',0,'',0,'','','','',NULL,'','','','','','','','','','','','','','','','','',NULL,NULL,'','DM','','','','','','','','','','','','','',''

,''

);



set echo off pagesize 0 linesize 9999 feedback off define off                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

column csv format a9999                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

set define off

var tab1 varchar2(30)  

exec :tab1 := 'SAMPLE_TABLE'

SELECT RTRIM(REPLACE (REPLACE (d.COLUMN_VALUE.EXTRACT ('ROW/C/C/text()'),'&quot;','"'),'amp;quot;','"'),',') csv FROM (SELECT 'select '|| RTRIM(REPLACE(XMLAGG(XMLELEMENT(c,'xmlelement(c, '|| CASE WHEN data_type IN('VARCHAR2', 'DATE') THEN '''"'' || ' END || column_name || CASE WHEN data_type IN ('VARCHAR2', 'DATE') THEN ' || ''"''' END || ' ||'','') c,' ) ORDER BY column_id ).EXTRACT ('C/text()').getclobval (), ''', '''' ), ',' ) || ' from ' || :tab1  || ' where rownum <= 50' cols  FROM cols WHERE table_name = UPPER (:tab1) ),  TABLE (XMLSEQUENCE (DBMS_XMLGEN.getxmltype (cols).EXTRACT ('ROWSET/ROW')  )  ) d;



ERROR at line 1:

ORA-19206: Invalid value for query or REF CURSOR parameter

ORA-06512: at "SYS.DBMS_XMLGEN", line 206

ORA-06512: at line 1

Thanks
Pages: 1
Back to Thread List Back to Thread List
Legend
Guru Guru : 2500 - 1000000 pts
Expert Expert : 1000 - 2499 pts
Pro Pro : 500 - 999 pts
Journeyman Journeyman : 200 - 499 pts
Newbie Newbie : 0 - 199 pts
Oracle ACE Director
Oracle ACE Member
Oracle Employee ACE
Helpful Answer (5 pts)
Correct Answer (10 pts)

Point your RSS reader here for a feed of the latest messages in all forums