SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Converting XMLTYPE date to VARCHAR2 or String output for UTL_FILE.PUT_LINE

Slippery JimJan 5 2017 — edited Jan 6 2017

Hi we're using Oracle Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

I have data that is being generated by a query using XML functions like this:

SELECT XMLELEMENT ("ROW", XMLAttributes('0' AS "ss:AutoFitHeight"),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Provisioning Date')),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('Number' AS "ss:Type"),'Account ID')),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Account Name')),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Technology')), 
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Devices Provisioned'))) AS STUFF                                              
FROM DUAL;

I want to store this in a cursor and add it as rows to a file using

 CURSOR GSM_DATA                                                         --(
    IS 
SELECT XMLELEMENT ("ROW", XMLAttributes('0' AS "ss:AutoFitHeight"),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Provisioning Date')),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('Number' AS "ss:Type"),'Account ID')),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Account Name')),
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Technology')), 
            XMLELEMENT ("CELL", XMLELEMENT ("DATA", XMLAttributes('String' AS "ss:Type"),'Devices Provisioned'))) AS STUFF                                              
FROM DUAL;
BEGIN
     FOR GSM_DATA_CUR IN GSM_DATA
     LOOP
        UTL_FILE.PUT_LINE (
           lv_file, 
              GSM_DATA_CUR.GSM_ROW);
     END LOOP;
   UTL_FILE.FCLOSE (LV_FILE);
END;

I'm getting an error that says"wrong type or number of arguments"

I think this is Oracle not liking that fact I'm trying to output XMLTYPE data as string data. I'd like to know how to do a simple conversion either in the cursor or around the UTL_FILE_PUT_LINE statement.

Does anyone know a simple conversion that works. TO_CHAR does not do it.

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 3 2017

Added on Jan 5 2017

3 comments

1,695 views