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