2012-01-24

XMLType from no rows

I used DBMS_XMLGEN package in my post constraint name generated. There I used GETXML function and got problems when the schema constraints were all named. So the query result was empty - no rows.

SELECT XMLTYPE(
         DBMS_XMLGEN.GETXML('SELECT 1 FROM dual WHERE 1=0')
       ) AS xml
  FROM dual
;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

There is a built in DBMS_XMLGEN.GETXMLTYPE function to get the XMLType. This is how you get rid of the ORA-06502 problem.

SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT 1 FROM dual WHERE 1=0') AS xml
  FROM dual
;

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.