Oracle : Convert NCHAR column value into Hexadecimal when fetching VARRAY in XML format?

63 views Asked by At

Test

CREATE OR REPLACE TYPE DTOBJECT AS OBJECT
( 
  project_no NUMBER(2),
  datecol DATE,
  ncharcol nchar(50),
  nvarchar2col nvarchar2(50)
);

CREATE OR REPLACE TYPE vDTOBJECT AS VARRAY(500) OF DTOBJECT;
CREATE TABLE qatest.varray_datetime (id int, vdate vDTOBJECT);

INSERT INTO qatest.varray_datetime VALUES (1, vDTOBJECT( DTOBJECT(1, 
    to_date('2020-06-08', 'yyyy-mm-dd'), 'nchar', 'nvarcharcol')
    ))

SQLPlus output

 SQL> SELECT id, XMLElement("vdate", vdate) FROM qatest.varray_datetime;

    ID
----------
XMLELEMENT("VDATE",VDATE)
-----------------------------------------------------------------------------------------
     1
<vdate><VDTOBJECT><DTOBJECT><PROJECT_NO>1</PROJECT_NO><DATECOL>2020-06-08T00:00:00.000000000</DATECOL><NCHARCOL>nchar                         </NCHARCOL><NVARCHAR2COL>nvarcharcol</
NVARCHAR2COL></DTOBJECT></VDTOBJECT></vdate>


SQL>

Question How can I convert NCHAR column value into Hexadecimal when fetching VARRAY in XML format ? The XMLElement should convert NCHAR value from nchar to 6E6368617220202020202020202020202020202020202020202020202020. Also NVARCHAR value should be converted from NVARCHAR2COL to 6E76617263686172636F6C.

Expected Output

<vdate><VDTOBJECT><DTOBJECT><PROJECT_NO>1</PROJECT_NO><DATECOL>2020-06-08T00:00:00.000000000</DATECOL><NCHARCOL>6E6368617220202020202020202020202020202020202020202020202020</NCHARCOL><NVARCHAR2COL>6E76617263686172636F6C</NVARCHAR2COL></DTOBJECT></VDTOBJECT></vdate>
0

There are 0 answers