Forum Stats

  • 3,825,924 Users
  • 2,260,580 Discussions
  • 7,896,737 Comments

Discussions

extractvalue error ORA-06502

Robeen
Robeen Member Posts: 2,179 Silver Badge

Oracle DB 12.1.0.2

Dear Team,


can you please advise on error below?


create table tt_ticketinfoxml nologging as

select x.DOCKET_NUMBER_N, x.TICKET_REGN_DT_D, x.VISIBILITY_N, x.ENTITY_TYPE_V, x.NO_ENTITY_ID_N, x.STATUS_V, x.PRIORITY_N, x.RESPONSE_MEDIUM_V, x.RESP_MEDIUM_ADDR_V, x.CATEGORY_ID_V, x.SUBCATEGORY_ID_V, x.TICKET_TYPE_V, x.REF_DOCKET_NUMBER_V, x.LOGGED_BY_N, x.TT_SOURCE_V, x.RESPONDED_BY_N, x.TICKET_RESP_DT_D, x.LOCATION_V, x.AREA_V, x.REOPEN_COUNT_N,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'REGION') as REGION,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'STATE') as STATE,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'CITY') as CITY,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'STREETNO') as STREETNO,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'BUILDINGNO') as BUILDINGNO,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'COMPLAINTTYPE') as COMPLAINTTYPE,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'DESTINATIONNO') as DESTINATIONNO,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'OTHERNO') as OTHERNO,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'DOCKET_NO') as DOCKET_NO,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'GEN_DOCKET_NO') as GEN_DOCKET_NO,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'SEGMENT') as SEGMENT,

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'CHANNEL') as CHANNEL,

x.QUESTION_V, x.ANSWER_V, x.REMEDY_REF_NO_V, x.UPLOAD_DOC_B, x.FILE_NAME_V, x.TICKET_REGN_DT_D_1, x.SEGMENT_V, x.CHANNEL_V, x.RELEVE, x.CAUSE, x.COMMENTS, x.REASON, x.ENTITY_ID_N

from cbsogg.tt_ticketinfo x;

~


SQL> @ttticketinfoxml.sql

extractvalue(xmltype(x.TT_ADDNL_DATA.getclobval()),'REGION') as REGION,

       *

ERROR at line 3:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "SYS.XMLTYPE", line 272

ORA-06512: at line 1


Thanks,


Roshan

Best Answers

  • cormaco
    cormaco Member Posts: 1,941 Silver Crown
    Answer ✓

    Here is an example using XMLTABLE:

    with TT_TICKETINFO(TT_ADDNL_DATA) as (
    select xmltype(
    '<?xml version="1.0" encoding="WINDOWS-1252"?>
    <DATA xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schema.concierge.com" xmlns:t1="http://schema.concierge.com/WorkflowDetails" xmlns:wor="http://schema.concierge.com/WorkflowDetails">
        <REGION>REGION</REGION>
        <STATE>STATE</STATE>
        <CITY>CITY</CITY>
        <STREETNO>STREET</STREETNO>
        <BUILDINGNO>0</BUILDINGNO>
        <COMPLAINTTYPE>I</COMPLAINTTYPE>
        <DESTINATIONNO>0</DESTINATIONNO>
        <OTHERNO>0</OTHERNO>
        <DOCKET_NO/>
        <GEN_DOCKET_NO/>
        <SEGMENT/>
        <CHANNEL>clm</CHANNEL>
        <EVENT>
            <ADD_DET_XML REMINDER=""/>
        </EVENT>
    </DATA>') from dual)
    select x.*
    from TT_TICKETINFO,xmltable(
        '/DATA'
        passing TT_ADDNL_DATA
        columns
            COMPLAINTTYPE char(1)   path 'COMPLAINTTYPE',
            DESTINATIONNO number(1) path 'DESTINATIONNO'
    ) x
    
    
    COMPLAINTTYPE   DESTINATIONNO
    --------------- -------------
    I                           0
    
    
    
    
    Robeen
  • cormaco
    cormaco Member Posts: 1,941 Silver Crown
    Answer ✓

    The code I've posted works for any number of rows in TT_TICKETINFO, if the all XML files have the same structure. Give it a try.

  • cormaco
    cormaco Member Posts: 1,941 Silver Crown
    Answer ✓

    Like this:

    In the first xmltable is a new column lvl of xmltype which holds all the levels for this activity. This lvl is passed to the second xmltable and processed there.

    select activityName,activityStatus,batchOnline,maxHoldTime,lvl.*
    from WF_WORKFLOW,
    xmltable(
      '/WorkFlow/Activity[node()]'
      passing WRKFLW_DATA_X
      columns
        activityName varchar2(100)  path '@activityName ',
        activityStatus varchar2(100) path '@activityStatus',
        batchOnline char(1) path '@batchOnline',
        maxHoldTime number(10) path '@maxHoldTime' ,
        lvl xmltype path 'Owners/Level'
    ) wfl,
    xmltable(
        'Level' passing lvl
        columns
            id number(10) path '@id ',
            resolutionTime number(10) path '@resolutionTime'
    ) lvl
    
    
    ACTIVITYNAME              A B MAXHOLDTIME         ID RESOLUTIONTIME
    ------------------------- - - ----------- ---------- --------------
    Activity2 - ISSOLUTION    I O         240          1            240
    Activity2 - ISSOLUTION    I O         240          2            240
    Activity2 - ISSOLUTION    I O         240          3            240
    Activity3 - CRC           N O         240          1            240
    Activity3 - CRC           N O         240          2            240
    Activity3 - CRC           N O         240          3            240
    Activity 4 - CRC          I O         120          1            120
    Activity 4 - CRC          I O         120          2            120
    Activity 4 - CRC          I O         120          3            120
    
    
    
«1

Answers

  • cormaco
    cormaco Member Posts: 1,941 Silver Crown

    Extractvalue is deprecated, use XMLTABLE or XMLQUERY instead:

    As to your code, what is the datatype of x.TT_ADDNL_DATA ? If it is already XMLTYPE, these codeparts are unnecessary:

    xmltype(x.TT_ADDNL_DATA.getclobval())
    
    Robeen
  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    Thanks for the update.


    I have reviewed the URL you provided.


    Can you please help me with the conversion of the XML data below? I would like to extract only the values for the fields in tag e.g DESTINATIONNO ->0, COMPLAINTYPE ->1?


  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    SELECT ticket_type_v, XMLQuery(

    'for $i in /DATA

     return $i/REGION'

    passing by value TT_TICKETINFO

    RETURNING CONTENT) XMLData

    FROM TT_TICKETINFO;

  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    edited Jul 3, 2021 5:08AM

    I am providing sample data for the XML.


    <?xml version="1.0" encoding="WINDOWS-1252"?>

    <DATA xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schema.concierge.com" xmlns:t1="http://schema.concierge.com/WorkflowDetails" xmlns:wor="http://schema.concierge.com/WorkflowDetails">

     <REGION>REGION</REGION>

     <STATE>STATE</STATE>

     <CITY>CITY</CITY>

     <STREETNO>STREET</STREETNO>

     <BUILDINGNO>0</BUILDINGNO>

     <COMPLAINTTYPE>I</COMPLAINTTYPE>

     <DESTINATIONNO>0</DESTINATIONNO>

     <OTHERNO>0</OTHERNO>

     <DOCKET_NO/>

     <GEN_DOCKET_NO/>

     <SEGMENT/>

     <CHANNEL>clm</CHANNEL>

     <EVENT>

      <ADD_DET_XML REMINDER=""/>

     </EVENT>

    </DATA>


    For example, I would like to extract the scalar value ony.

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/EXTRACTVALUE.html#GUID-20AB974B-7544-4F44-B539-787FB6145680

    Grateful if you can help.


    Thanks,


    Roshan

  • cormaco
    cormaco Member Posts: 1,941 Silver Crown
    Answer ✓

    Here is an example using XMLTABLE:

    with TT_TICKETINFO(TT_ADDNL_DATA) as (
    select xmltype(
    '<?xml version="1.0" encoding="WINDOWS-1252"?>
    <DATA xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schema.concierge.com" xmlns:t1="http://schema.concierge.com/WorkflowDetails" xmlns:wor="http://schema.concierge.com/WorkflowDetails">
        <REGION>REGION</REGION>
        <STATE>STATE</STATE>
        <CITY>CITY</CITY>
        <STREETNO>STREET</STREETNO>
        <BUILDINGNO>0</BUILDINGNO>
        <COMPLAINTTYPE>I</COMPLAINTTYPE>
        <DESTINATIONNO>0</DESTINATIONNO>
        <OTHERNO>0</OTHERNO>
        <DOCKET_NO/>
        <GEN_DOCKET_NO/>
        <SEGMENT/>
        <CHANNEL>clm</CHANNEL>
        <EVENT>
            <ADD_DET_XML REMINDER=""/>
        </EVENT>
    </DATA>') from dual)
    select x.*
    from TT_TICKETINFO,xmltable(
        '/DATA'
        passing TT_ADDNL_DATA
        columns
            COMPLAINTTYPE char(1)   path 'COMPLAINTTYPE',
            DESTINATIONNO number(1) path 'DESTINATIONNO'
    ) x
    
    
    COMPLAINTTYPE   DESTINATIONNO
    --------------- -------------
    I                           0
    
    
    
    
    Robeen
  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    edited Jul 3, 2021 12:56PM

    @cormaco

    Thanks for the update.

    It is working now.


    select x.*
    from TT_TICKETINFO,xmltable(
        '/DATA'
        passing TT_ADDNL_DATA
        columns
            COMPLAINTTYPE char(1)   path 'COMPLAINTTYPE',
            DESTINATIONNO number(1) path 'DESTINATIONNO'
    ) x
    


  • cormaco
    cormaco Member Posts: 1,941 Silver Crown
    Answer ✓

    The code I've posted works for any number of rows in TT_TICKETINFO, if the all XML files have the same structure. Give it a try.

  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    Hi

    I have one table which has different xml structure(field WRKFLW_DATA_X) for each record.

    WRKFLW_INSTANCE_ID_N NUMBER(10,0) No 1

    EVENT_TYPE_N NUMBER(5,0) Yes 2

    EVENT_REFNO_V VARCHAR2(20 BYTE) Yes 3

    WRKFLW_ID_N NUMBER(5,0) Yes 4

    START_DT_D TIMESTAMP(6) WITH LOCAL TIME ZONE Yes 5

    END_DT_D TIMESTAMP(6) WITH LOCAL TIME ZONE Yes 6

    STATUS_V VARCHAR2(1 BYTE) Yes 7

    CREATED_BY_N NUMBER(19,0) Yes 8

    TERMINATED_BY_N NUMBER(19,0) Yes 9

    WRKFLW_DATA_X XMLTYPE Yes 10

    START_DT_D_1 DATE Yes SYSDATE 11

    Kindly advise how the select will be?

    <WorkFlow>

     <Activity/>

     <Activity activityName="Activity2 - ISSOLUTION" activityStatus="I" batchOnline="O" maxHoldTime="240" parentCount="1" parentSequence="jsx_287917" sequenceNumber="jsx_742507" timer="0" timerFlag="D" urlKeyCode="" workAgent="401">

      <Owners>

       <Level id="1" resolutionTime="240">

        <Owner defaultUser="" userGroup="G" userGroupId="348"/>

       </Level>

       <Level id="2" resolutionTime="240">

        <Owner defaultUser="" userGroup="G" userGroupId="1428"/>

       </Level>

       <Level id="3" resolutionTime="240">

        <Owner defaultUser="" userGroup="G" userGroupId="348"/>

       </Level>

      </Owners>

      <DependentParents>

       <DependentActivity/>

      </DependentParents>

     </Activity>

     <Activity activityName="Activity3 - CRC" activityStatus="N" batchOnline="O" maxHoldTime="240" parentCount="1" parentSequence="jsx_742507" sequenceNumber="jsx_c52442" timer="0" timerFlag="D" urlKeyCode="" workAgent="410">

      <Owners>

       <Level id="1" resolutionTime="240">

        <Owner defaultUser="" userGroup="G" userGroupId="346"/>

       </Level>

       <Level id="2" resolutionTime="240">

        <Owner defaultUser="" userGroup="G" userGroupId="1421"/>

       </Level>

       <Level id="3" resolutionTime="240">

        <Owner defaultUser="" userGroup="G" userGroupId="346"/>

       </Level>

      </Owners>

      <DependentParents>

       <DependentActivity sequenceNumber="jsx_742507"/>

      </DependentParents>

     </Activity>

    </WorkFlow>


    -----

    <WorkFlow>

     <Activity/>

     <Activity/>

     <Activity/>

     <Activity/>

    </WorkFlow>

    -------


    <WorkFlow>

     <Activity/>

     <Activity/>

     <Activity/>

     <Activity activityName="Activity 4 - CRC" activityStatus="I" batchOnline="O" maxHoldTime="120" parentCount="1" parentSequence="jsx_2r63744" sequenceNumber="jsx_2w38679" timer="0" timerFlag="D" urlKeyCode="" workAgent="410">

      <Owners>

       <Level id="1" resolutionTime="120">

        <Owner defaultUser="" userGroup="G" userGroupId="346"/>

       </Level>

       <Level id="2" resolutionTime="120">

        <Owner defaultUser="" userGroup="G" userGroupId="1421"/>

       </Level>

       <Level id="3" resolutionTime="120">

        <Owner defaultUser="" userGroup="G" userGroupId="346"/>

       </Level>

      </Owners>

      <DependentParents>

       <DependentActivity/>

      </DependentParents>

     </Activity>

    </WorkFlow>

    Kindly advise how the sql code will be.


    select x.*

    from WF_WORKFLOW,xmltable(

      '/WORKFLOW/ACTIVITY'

      passing TT_ADDNL_DATA

      columns

        activityName varchar2(100)  path 'activityName ',

        activityStatus varchar2(100) path 'activityStatus',

    batchOnline char(1) 'batchOnline',

    maxHoldTime number(10) maxHoldTime

    ) x

    ..

  • cormaco
    cormaco Member Posts: 1,941 Silver Crown

    Here are my corrections to your code, I used the node() function to filter out empty activities:

    select x.*
    from WF_WORKFLOW,xmltable(
      '/WorkFlow/Activity[node()]'
      passing WRKFLW_DATA_X
      columns
        activityName varchar2(100)  path '@activityName ',
        activityStatus varchar2(100) path '@activityStatus',
        batchOnline char(1) path '@batchOnline',
        maxHoldTime number(10) path '@maxHoldTime'
    ) x
    
    
    ACTIVITYNAME              A B MAXHOLDTIME
    ------------------------- - - -----------
    Activity2 - ISSOLUTION    I O         240
    Activity3 - CRC           N O         240
    Activity 4 - CRC          I O         120
    
    
    

    I'm confident you can figure out the rest yourself.

  • Robeen
    Robeen Member Posts: 2,179 Silver Badge

    @cormaco

    I managed to make the command work.

    select x.*

    from WF_WORKFLOW,xmltable(

     '/WorkFlow/Activity/Owners/Level[node()]'

     passing WRKFLW_DATA_X

     columns

    id number(10) path '@id ' ,

    resolutionTime number(10) path '@resolutionTime'

    ) x



    Result:

    1 240

    2 240

    3 240

    1 240

    2 240

    3 240


    Can you please advise how can I combine 2 xmltables? I would like to display the values from different levels of the XML.

    XML table 1:

    select x.*

    from WF_WORKFLOW,xmltable(

     '/WorkFlow/Activity/Owners/Level[node()]'

     passing WRKFLW_DATA_X

     columns

    id number(10) path '@id ' ,

    resolutionTime number(10) path '@resolutionTime'

    ) x

    XML Table 2:

    select x.*

    from WF_WORKFLOW,xmltable(

     '/WorkFlow/Activity[node()]'

     passing WRKFLW_DATA_X

     columns

      activityName varchar2(100) path '@activityName ',

      activityStatus varchar2(100) path '@activityStatus',

      batchOnline char(1) path '@batchOnline',

      maxHoldTime number(10) path '@maxHoldTime'

    ) x

    Regards,


    Roshan