Home » SQL & PL/SQL » SQL & PL/SQL » Help w/ REGEXP_SUBSTR
Help w/ REGEXP_SUBSTR [message #671585] |
Thu, 06 September 2018 10:19 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
I have a piece of data in a blob column and I am trying to grab ALL text that is between the <poId> and </poId>, and have come up with this expression, it's not working.
REGEXP_SUBSTR(SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),1,100),'(<poId>(.*?)</poId>)') PO_NUM,
It would also be nice to return the error between <errorMessage>......</errorMessage>
Here's some sample data:
<poId>QWSLWA</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode></rejectCode><package
<poId>NA</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode>POI</rejectCode><packageQ
<poId>QVUNPB</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>QTXSQJ</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>P103606</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode>POI</rejectCode><pac
<poId>QNUNJP</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>GWGTEB</poId><errorMessage>Invalid MF_ORDER_NO</errorMessage><rejectCode>POI</rejectCode><upcI
<poId>QVEUPT</poId><errorMessage>Invalid Location</errorMessage><rejectCode>MA</rejectCode><packageQ
<poId>BUYQVUNYP725</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode>POI</rejectCode
<poId>QWDMEG</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>QVTOAE</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode></rejectCode><package
<poId>QTOUTG</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>QVZOFQ</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>469277 </poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode></rejectCode><packag
<poId>QRMCHR</poId><errorMessage>Invalid item carton quantity</errorMessage><rejectCode>P</rejectCod
<poId>OVOISQ</poId><errorMessage>Invalid Location</errorMessage><rejectCode>MA</rejectCode><packageQ
<poId>QWDZPH</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>NONE P.O</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode>POI</rejec
<poId>QVIXTO</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
<poId>QWDYOR</poId><errorMessage>Invalid Location</errorMessage><rejectCode></rejectCode><packageQty
[Updated on: Thu, 06 September 2018 10:27] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671595 is a reply to message #671593] |
Thu, 06 September 2018 13:14 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
OK, that looks just like what I need, but I don't understand how to incorporate this into my query. The actual BLOB column I want to look in is EMAIL_DATA. I was just using the substr so that I could see into it, but there has to be a better way to do this I"m certain.
SELECT
TO_CHAR(CREATEDTS,'MM/DD/YYYY HH12:MI AM') CREATE_DTT,
CASE
WHEN MODIFIEDBY IS NULL AND MODIFIEDTS IS NULL THEN 'EMAIL NOT SENT'
END AS "FAILURE REASON",
MODIFIEDBY,
MODIFIEDTS,
SRC_SYS,
DATA_TOPIC,
RQST_ID,
EMAIL_TYP,
EMAIL_DATA,
SCAC_CARRIER_CODE SCAC,
FROM_EMAIL,
TO_EMAIL,
REGEXP_SUBSTR(SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),1,100),'(<poId>(.*?)</poId>)') PO_NUM,
--REGEXP_SUBSTR(SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),1,100),'(<errorMessage>([A-Z]+[a-z]+)</errorMessage>)') PO_ERROR,
SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),38,100) PODATA
FROM ABPPBB_2016.carrier_email_rqst
WHERE EMAIL_TYP = 'ERROR'
AND DATA_TOPIC <> 'CollectUpdate'
AND CREATEDTS >= (SYSDATE-30/1440) -- created in the past 30 min
ORDER BY CREATEDTS;
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671596 is a reply to message #671595] |
Thu, 06 September 2018 13:34 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I answer the question for the data you gave I can't answer the question for the data I have not and I even have no idea of what it actually is.
But if you can SUBSTR or REGEXP_SUBSTR to lead to the data you gave (for instance in a subquery)then you can easily used what I posted.
[Updated on: Thu, 06 September 2018 13:36] Report message to a moderator
|
|
|
|
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671609 is a reply to message #671600] |
Fri, 07 September 2018 09:31 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Try using the following. This is a guess without access to your data
select poid, errmsg
from EMAIL,
xmltable('/' passing xmltype(EMAIL_DATA)
columns
poid varchar2(10) path '//poId',
errmsg varchar2(30) path '//errorMessage');
|
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671611 is a reply to message #671610] |
Fri, 07 September 2018 13:49 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
This is what I've tried but am getting an error
ORA-00904: "EMAIL_DATA": invalid identifier
00904. 00000 - "%s: invalid identifier"
WITH
data as ( SELECT
SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),1,100)
FROM ABPPBB_2016.carrier_email_rqst
WHERE EMAIL_TYP = 'ERROR'
AND DATA_TOPIC <> 'CollectUpdate'
AND CREATEDTS >= (SYSDATE-30/1440) -- created in the past 30 min
ORDER BY CREATEDTS
)
SELECT TO_CHAR(CREATEDTS,'MM/DD/YYYY HH12:MI AM') CREATE_DTT,
CASE
WHEN MODIFIEDBY IS NULL AND MODIFIEDTS IS NULL THEN 'EMAIL NOT SENT'
END AS "FAILURE REASON",
MODIFIEDBY,
MODIFIEDTS,
SRC_SYS,
DATA_TOPIC,
RQST_ID,
EMAIL_TYP,
EMAIL_DATA,
SCAC_CARRIER_CODE SCAC,
FROM_EMAIL,
TO_EMAIL
from DATA,
xmltable('/' passing xmltype(EMAIL_DATA)
columns
POID VARCHAR2(10) PATH '//poId',
errmsg varchar2(30) path '//errorMessage');
|
|
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671624 is a reply to message #671613] |
Mon, 10 September 2018 03:28 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
Getting a bit closer now with this query:
with
DATA AS (
SELECT
TO_CHAR(CREATEDTS,'MM/DD/YYYY HH12:MI AM') CREATE_DTT,
CASE
WHEN MODIFIEDBY IS NULL AND MODIFIEDTS IS NULL THEN 'EMAIL NOT SENT'
END AS "FAILURE REASON",
MODIFIEDBY,
MODIFIEDTS,
SRC_SYS,
DATA_TOPIC,
RQST_ID,
EMAIL_TYP,
EMAIL_DATA,
SCAC_CARRIER_CODE SCAC,
FROM_EMAIL,
TO_EMAIL,
SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),1,106) PO_DATA
--UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)) PO_DATA
FROM ABPPBB_2016.carrier_email_rqst
WHERE EMAIL_TYP = 'ERROR'
AND DATA_TOPIC <> 'CollectUpdate'
AND CREATEDTS >= SYSDATE-3
)
select poid, errmsg
FROM DATA,
xmltable('/' passing xmltype(PO_DATA)
columns
POID VARCHAR2(10) PATH '//poId',
ERRMSG VARCHAR2(30) PATH '//errorMessage')
;
Throwing a different error now:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.
My thought then was that maybe I needed to use the entire XML Blob so I uncommented the line above and commented the SUBSTR only to then get this error
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 - "XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence.
Here is another sample row:
<rejectedShipments>
<Item>
<by>RMS</by>
<poId>0QVUPFH</poId>
<errorMessage>Invalid MF Order No</errorMessage>
<rejectCode>POI</rejectCode>
<packageQty>53</packageQty>
<packageWeight>1332</packageWeight>
<packageCode>CTN</packageCode>
<packageWeightCode>L</packageWeightCode>
</Item>
<Item>
<by>ESP</by>
<poId>QWCKWE</poId>
<errorMessage>Missing or Invalid Item Quantity.</errorMessage>
<rejectCode>P</rejectCode>
<packageQty>0</packageQty>
<packageWeight>0</packageWeight>
<packageCode>CTN</packageCode>
<packageWeightCode>L</packageWeightCode>
</Item>
</rejectedShipments>
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671626 is a reply to message #671624] |
Mon, 10 September 2018 06:28 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
You should use XMLTable properly to query the different <Item>'s :
WITH data(po_data) AS
(SELECT
'<rejectedShipments>
<Item>
<by>RMS</by>
<poId>0QVUPFH</poId>
<errorMessage>Invalid MF Order No</errorMessage>
<rejectCode>POI</rejectCode>
<packageQty>53</packageQty>
<packageWeight>1332</packageWeight>
<packageCode>CTN</packageCode>
<packageWeightCode>L</packageWeightCode>
</Item>
<Item>
<by>ESP</by>
<poId>QWCKWE</poId>
<errorMessage>Missing or Invalid Item Quantity.</errorMessage>
<rejectCode>P</rejectCode>
<packageQty>0</packageQty>
<packageWeight>0</packageWeight>
<packageCode>CTN</packageCode>
<packageWeightCode>L</packageWeightCode>
</Item>
</rejectedShipments>' FROM dual)
SELECT poid, errmsg
FROM data,
XMLTABLE('/rejectedShipments/Item' PASSING xmltype(po_data)
COLUMNS
poid VARCHAR2(10) PATH '/Item/poId',
errmsg VARCHAR2(30) PATH '/Item/errorMessage');
POID ERRMSG
----------------------------
0QVUPFH Invalid MF Order No
QWCKWE Missing or Invalid Item Quanti
[Updated on: Mon, 10 September 2018 06:30] Report message to a moderator
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671628 is a reply to message #671626] |
Mon, 10 September 2018 06:48 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
OK, when I remove the hardcoded XML and use the entire field as shown here, I get an error:
ORA-32038: number of WITH clause column names does not match number of elements in select list
32038. 00000 - "number of WITH clause column names does not match number of elements in select list"
*Cause: A list of column aliases or names was specified for a WITH clause
query name but the number of elements in the list did not match the
number of elements in the select list of the definition query.
*Action: Update the column alias list or the select list to ensure they
have the same number of elements.
Error at Line: 120 Column: 30
WITH data(po_data) AS (
SELECT
TO_CHAR(CREATEDTS,'MM/DD/YYYY HH12:MI AM') CREATE_DTT,
CASE
WHEN MODIFIEDBY IS NULL AND MODIFIEDTS IS NULL THEN 'EMAIL NOT SENT'
END AS "FAILURE REASON",
MODIFIEDBY,
MODIFIEDTS,
SRC_SYS,
DATA_TOPIC,
RQST_ID,
EMAIL_TYP,
EMAIL_DATA,
SCAC_CARRIER_CODE SCAC,
FROM_EMAIL,
TO_EMAIL,
--SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)),1,106) PO_DATA
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)) PO_DATA
FROM ABPPBB_2016.carrier_email_rqst
WHERE EMAIL_TYP = 'ERROR'
AND DATA_TOPIC <> 'CollectUpdate'
AND CREATEDTS >= SYSDATE-1
)
SELECT poid, errmsg
FROM data,
XMLTABLE('/rejectedShipments/Item' PASSING xmltype(po_data)
COLUMNS
POID VARCHAR2(10) PATH '/Item/poId',
errmsg VARCHAR2(30) PATH '/Item/errorMessage');
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671629 is a reply to message #671628] |
Mon, 10 September 2018 07:08 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That error message really speaks for it self.
You've provided a list of columns for the WITH clause:
WITH data(po_data) AS (
^ here
That's got one column.
The select in the with clause selects 13 columns.
Hence the error.
Either expand the column list after the WITH keyword to include all columns or remove that list entirely - it's optional.
|
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671756 is a reply to message #671630] |
Tue, 18 September 2018 08:40 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
OK gang...I was asking for help with this query so that I could use it in our Cognos reporting tool. Apparently, Cognos does not using the "With" clause syntax so I am wondering if there is any other way to pull this info without using a With clause?
WITH data AS (
SELECT
TO_CHAR(CREATEDTS,'MM/DD/YYYY HH12:MI AM') CREATE_DTT,
CASE
WHEN MODIFIEDBY IS NULL AND MODIFIEDTS IS NULL THEN 'EMAIL NOT SENT'
END AS "FAILURE REASON",
MODIFIEDBY,
TO_CHAR(MODIFIEDTS,'MM/DD/YYYY HH12:MI AM') MODIFIED_DTT,
SRC_SYS,
DATA_TOPIC,
RQST_ID,
EMAIL_TYP,
SCAC_CARRIER_CODE SCAC,
FROM_EMAIL,
TO_EMAIL,
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(EMAIL_DATA)) PO_DATA
FROM ABPPBB_2016.carrier_email_rqst
WHERE EMAIL_TYP = 'ERROR'
AND DATA_TOPIC <> 'CollectUpdate'
AND CREATEDTS >= trunc(sysdate-1)
)
SELECT CREATE_DTT,
"FAILURE REASON",
MODIFIEDBY,
MODIFIED_DTT,
SRC_SYS,
DATA_TOPIC,
RQST_ID,
EMAIL_TYP,
SCAC,
FROM_EMAIL,
TO_EMAIL,
POID,
ERRMSG
FROM data,
XMLTABLE('/rejectedShipments/Item' PASSING xmltype(po_data)
COLUMNS
POID VARCHAR2(10) PATH '/Item/poId',
ERRMSG VARCHAR2(30) PATH '/Item/errorMessage')
ORDER BY 1;
|
|
|
Re: Help w/ REGEXP_SUBSTR [message #671760 is a reply to message #671756] |
Tue, 18 September 2018 10:15 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Take the select you have in the with and enclose it with parens and put in in your from clause. See below
SELECT Create_dtt,
"FAILURE REASON",
Modifiedby,
Modified_dtt,
Src_sys,
Data_topic,
Rqst_id,
Email_typ,
Scac,
From_email,
To_email,
Poid,
Errmsg
FROM (SELECT TO_CHAR(Createdts, 'MM/DD/YYYY HH12:MI AM')
Create_dtt,
CASE
WHEN Modifiedby IS NULL AND Modifiedts IS NULL THEN
'EMAIL NOT SENT'
END
AS "FAILURE REASON",
Modifiedby,
TO_CHAR(Modifiedts, 'MM/DD/YYYY HH12:MI AM')
Modified_dtt,
Src_sys,
Data_topic,
Rqst_id,
Email_typ,
Scac_carrier_code
Scac,
From_email,
To_email,
UTL_RAW.Cast_to_varchar2(DBMS_LOB.SUBSTR(Email_data))
Po_data
FROM Abppbb_2016.Carrier_email_rqst
WHERE Email_typ = 'ERROR'
AND Data_topic <> 'CollectUpdate'
AND Createdts >= TRUNC(SYSDATE - 1)) Data,
XMLTABLE(
'/rejectedShipments/Item'
PASSING Xmltype(Po_data)
COLUMNS Poid VARCHAR2(10) PATH '/Item/poId',
Errmsg VARCHAR2(30) PATH '/Item/errorMessage')
ORDER BY 1;
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:46:19 CDT 2024
|