Home » SQL & PL/SQL » SQL & PL/SQL » Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement (Oracle 10g)
Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement [message #664235] Sun, 09 July 2017 03:33 Go to next message
keewee279
Messages: 1
Registered: July 2017
Junior Member
I am getting the above error for the following query - only after it runs already for about 30 min.
Since I only get the error after this time I was wondering if it is caused by something towards the end of the query, e.g. the .EXTRACT ?

I tried using TO_CHAR and NVL for all items in the Xmlelement which didn't help either.

However, when I use the same structure (Rtrim / Xmlagg / Xmlelement) separately for columns 2 to 6 then it works fine (it just doesn't help me that way).
Listagg works too but doesn't allow enough characters.

Can someone please tell me what I am doing wrong here ?

Error message:

ORA-01722 ... invalid use of a NUMBER.

My query:

    SELECT
    	a.column1 AS ID
    	, RTRIM
    		(
    			XMLAGG
    				(
    					XMLELEMENT
    					(
    						e, 'Column2: ' || b.column2 || 
    						'Column3: ' || SUBSTR(c.column3, 1, 50) || '...' || 
    						'Column4: ' || b.column4 || 
    						'Column5: ' || TO_CHAR(b.column5, 'FM9,990.00') || 
    						'Column6: ' || TO_CHAR(b.column6, 'FM9,990.00') || 
    						'---'
    					) ORDER BY b.column2
    				).EXTRACT('//text()'), ','
    		) AS AD
    	, TO_CHAR(b.column7, 'FM9,990.00') AS GN
    	, TO_CHAR(b.column8, 'FM9,990.00') AS GU
    	, TO_CHAR((b.column7 + b.column8), 'FM9,990.00') AS GB
    FROM
    	/* ... */

Update:
Could it be possible that the problem here is that I use ',' as the EXTRACT separator but might also have commas somewhere within my column values ?

Any help is much appreciated,
Mike
Re: Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement [message #664238 is a reply to message #664235] Sun, 09 July 2017 06:05 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Quote:
Could it be possible that the problem here is that I use ',' as the EXTRACT separator but might also have commas somewhere within my column values ?
Yes it is possible.
With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects and insert statements so that we will be able work to reproduce what you have.

Previous Topic: Appending CLOB
Next Topic: GMT Time Stamp
Goto Forum:
  


Current Time: Thu Mar 28 08:11:22 CDT 2024