0

I am trying to extract Oracle table data in XML format using the DBMS_XMLGEN.GETXMLTYPE libraries. With my SQL described below; I get the output in the format I want but XML generator skipped the last column output. It happens to be the LONG data type, which is what I wrote this format of SQL.

   { CREATE TABLE SYSADM.PS_SPEEDCHART_HDR
     (
       SETID VARCHAR2(5 BYTE) NOT NULL,
       SPEEDCHART_KEY VARCHAR2(10 BYTE) NOT 
       NULL,
       OPRID VARCHAR2(30 BYTE) NOT NULL,
       OPRCLASS VARCHAR2(30 BYTE) NOT NULL,
       EFFDT DATE NOT NULL,
       EFF_STATUS VARCHAR2(1 BYTE) NOT NULL,
       DESCR VARCHAR2(30 BYTE) NOT NULL,
       SPEEDTYPE_TYPE VARCHAR2(1 BYTE) NOT NULL,
       TOTAL_LINES INTEGER NOT NULL,
       UNIT_OF_MEASURE VARCHAR2(3 BYTE) NOT 
       NULL,
       SPDCHRT_DEFN_FLG VARCHAR2(1 BYTE) NOT 
       NULL,
       DESCRLONG LONG
       );
Bellerofont
  • 1,081
  • 18
  • 17
  • 16
user10102351
  • 1
  • 1
  • 1
    Sorry, my complete question didn't appear. Here's the re-post. – user10102351 Jul 19 '18 at 02:22
  • Why are you creating a table with a LONG column? You should be using CLOB. It also might be helpful to show your code. – Alex Poole Jul 19 '18 at 07:33
  • Hi Alex, I am pulling data from a database where data is in LONG field; converting to another DB with CLOB fields. This is what I am doing.. I may have to post my code with multiple comments. Thank you for your comment. – user10102351 Jul 19 '18 at 20:01
  • -- Insert data script SET DEFINE OFF; Insert into PS_SPEEDCHART_HDR (SETID, SPEEDCHART_KEY, OPRID, OPRCLASS, EFFDT, EFF_STATUS, DESCR, SPEEDTYPE_TYPE, TOTAL_LINES, UNIT_OF_MEASURE, SPDCHRT_DEFN_FLG, DESCRLONG) Values ('XYZ', '05663', ' ', ' ', TO_DATE('01/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'A', 'WX DOE 06 SUBRECIPIENT TRVL', 'U', 1, ' ', 'P', ' '); COMMIT; – user10102351 Jul 19 '18 at 20:03
  • -- Select data in XML format SELECT REGEXP_REPLACE ( DBMS_XMLGEN.GETXMLTYPE ('select * from PS_SPEEDCHART_HDR').getclobval (), CHR (38), CHR (38) || 'amp;') AS XML_output FROM DUAL) – user10102351 Jul 19 '18 at 20:04
  • -- XML output TDHCA 05663 01-JAN-00 A WX DOE 06 SUBRECIPIENT TRVL U 1 P is missing – user10102351 Jul 19 '18 at 20:04
  • Please [edit your question](https://stackoverflow.com/posts/51413433/edit) to add all of that, as formatted text – Alex Poole Jul 19 '18 at 20:25

0 Answers0