Forum Stats

  • 3,825,037 Users
  • 2,260,460 Discussions
  • 7,896,391 Comments

Discussions

invalid number

Robeen
Robeen Member Posts: 2,179 Silver Badge

Oracle DB 12.1.0.2

Solaris 11.4

Hello Team,


can you please advise why error below for BUILDINGNO

Error report -

ORA-01722: invalid number

01722. 00000 - "invalid number"

*Cause:  The specified number was invalid.

*Action:  Specify a valid number.


<?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>


tt_source_v:8900

<?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/>

 <STATE/>

 <CITY/>

 <STREETNO/>

 <BUILDINGNO/>

 <COMPLAINTTYPE>I</COMPLAINTTYPE>

 <DESTINATIONNO/>

 <OTHERNO/>

 <DOCKET_NO/>

 <GEN_DOCKET_NO/>

 <SEGMENT/>

 <CHANNEL>clm</CHANNEL>

 <EVENT>

  <ADD_DET_XML REMINDER=""/>

 </EVENT>

 <CATEGORY1>MASS MARKET-POSTPAID</CATEGORY1>

 <CATEGORY2>REACTIVATION OF SIM CARD</CATEGORY2>

 <CATEGORY3/>

 <SUBS_NAME/>

 <SERVICE_ACC_LINC_CODE>20225576</SERVICE_ACC_LINC_CODE>

 <ROWSET/>

</DATA>


status:P

<?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>


SQL code:

select DOCKET_NUMBER_N,REGION,

STATE

,CITY

,STREETNO

,BUILDINGNO

--,COMPLAINTTYPE,DESTINATIONNO,OTHERNO,CHANNEL,CATEGORY1,CATEGORY2,SERVICE_ACC_LINC_CODE, tvl.*

from tt_ticketinfo,

xmltable(

 '/DATA[node()]'

 passing TT_ADDNL_DATA

 columns

  REGION varchar2(10) path 'REGION ',

  STATE varchar2(10) path 'STATE',

  CITY varchar2(10) path 'CITY',

  STREETNO varchar2(10) path 'STREETNO' ,

  BUILDINGNO varchar2(10) path 'BUILDINGNO',

  COMPLAINTTYPE char(1) path 'COMPLAINTTYPE',

  DESTINATIONNO number path 'DESTINATIONNO',

  OTHERNO number path 'OTHERNO',

  CHANNEL char(1) path 'CHANNEL',

  CATEGORY1 varchar2(15) path 'CATEGORY1',

  CATEGORY2 varchar2(15) path 'CATEGORY2',

  SERVICE_ACC_LINC_CODE number path 'SERVICE_ACC_LINC_CODE',

  tvl xmltype path 'EVENT/ADD_DET_XML'

   

) wfl,

xmltable(

  'ADD_DET_XML' passing tvl

  columns

    REMINDER varchar2(10) path '@REMINDER'

    ) tvl


Thanks,


Roshan

Answers

  • cormaco
    cormaco Member Posts: 1,940 Silver Crown

    Generally this error occurs when you define a column as number but the data in the XML can't be converted to number as in this example:

    with example(xmldata) as (select xmltype('<DATA><NUM>12a</NUM></DATA>') from dual)
    select num from example,
    xmltable(
        '/DATA'
        passing xmldata
        columns
            num number path 'NUM'
    );
    
    ORA-01722: invalid number
    01722. 00000 -  "invalid number"
    

    You can find the data that can't be converted like this:

    with example(xmldata) as (select xmltype('<DATA><NUM>12a</NUM></DATA>') from dual)
    select num from example,
    xmltable(
        '/DATA'
        passing xmldata
        columns
            num varchar(100) path 'NUM'
    )
    where to_number(num default null on conversion error) is null
    
    NUM       
    ----------
    12a