Home » SQL & PL/SQL » SQL & PL/SQL » Help w/ REGEXP_SUBSTR
Help w/ REGEXP_SUBSTR [message #671585] Thu, 06 September 2018 10:19 Go to next message
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 #671586 is a reply to message #671585] Thu, 06 September 2018 11:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Sample you posted looks like XML. If it is, use XMLTABLE/XMLQUERY.

SY.
Re: Help w/ REGEXP_SUBSTR [message #671587 is a reply to message #671586] Thu, 06 September 2018 11:36 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
It's XML stored in a BLOB table.
Re: Help w/ REGEXP_SUBSTR [message #671589 is a reply to message #671587] Thu, 06 September 2018 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So post valid XML data and expected result from them.

Re: Help w/ REGEXP_SUBSTR [message #671590 is a reply to message #671589] Thu, 06 September 2018 12:10 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
This is the contents of the BLOB column for a single row:

<rejectedShipments><Item><by>RMS</by><poId>QVOTSO-725</poId><errorMessage>Invalid MF Order No</errorMessage><rejectCode>POI</rejectCode><packageQty>1</packageQty><packageWeight>1</packageWeight><packageCode>CTN</packageCode ><packageWeightCode>L</packageWeightCode></Item></rejectedShipments>
Re: Help w/ REGEXP_SUBSTR [message #671591 is a reply to message #671590] Thu, 06 September 2018 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the result for this data should be?

Re: Help w/ REGEXP_SUBSTR [message #671592 is a reply to message #671591] Thu, 06 September 2018 12:30 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
QVOTSO-725

and

Invalid MF Order No
Re: Help w/ REGEXP_SUBSTR [message #671593 is a reply to message #671592] Thu, 06 September 2018 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select
  4  '<rejectedShipments><Item><by>RMS</by><poId>QVOTSO-725</poId><errorMessage>Invalid MF Order No</errorMessage>
  5  <rejectCode>POI</rejectCode><packageQty>1</packageQty><packageWeight>1</packageWeight><packageCode>CTN</packageCode >
  6  <packageWeightCode>L</packageWeightCode></Item></rejectedShipments>' val
  7             from dual
  8    )
  9  select poid, errmsg
 10  from data,
 11       xmltable('/' passing xmltype(val)
 12                columns
 13                  poid   varchar2(10) path '//poId',
 14                  errmsg varchar2(30) path '//errorMessage')
 15  /
POID       ERRMSG
---------- ------------------------------
QVOTSO-725 Invalid MF Order No
Re: Help w/ REGEXP_SUBSTR [message #671595 is a reply to message #671593] Thu, 06 September 2018 13:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 #671597 is a reply to message #671596] Thu, 06 September 2018 13:37 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
The data I gave you was a sample of the contents of the blob column (email_data). I don't know how else to have posed the question initially.
Re: Help w/ REGEXP_SUBSTR [message #671598 is a reply to message #671597] Thu, 06 September 2018 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't see the problem to build the query.

Re: Help w/ REGEXP_SUBSTR [message #671599 is a reply to message #671598] Thu, 06 September 2018 13:48 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
You are clearly the expert here so I was attempting to build your suggestion into my query and I wasn't able to make it work, hence why I posted the entire query, simply looking for additional assistance from the expert
Re: Help w/ REGEXP_SUBSTR [message #671600 is a reply to message #671599] Thu, 06 September 2018 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

with
  data as (
    <your query without your REGPEX_SUBSTR but with SUBSTR to extract the data you posted>
  )
<my query adding your columns>
Re: Help w/ REGEXP_SUBSTR [message #671609 is a reply to message #671600] Fri, 07 September 2018 09:31 Go to previous messageGo to next message
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 #671610 is a reply to message #671609] Fri, 07 September 2018 10:40 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
Bill, is the "from EMAIL" that actual name of the table where the data resides? ABPPBB_2016.carrier_email_rqst in this instance.
Re: Help w/ REGEXP_SUBSTR [message #671611 is a reply to message #671610] Fri, 07 September 2018 13:49 Go to previous messageGo to next message
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 #671612 is a reply to message #671611] Fri, 07 September 2018 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Format your query, see what I posted and what you posted, which one is easier to read? read How to use [code] tags and make your code easier to read.

2/ You didn't do what I told you to do.

Re: Help w/ REGEXP_SUBSTR [message #671613 is a reply to message #671612] Fri, 07 September 2018 14:02 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
EMAIL would be the name of the table, EMAIL_DATA would be the column name holding the XML
Re: Help w/ REGEXP_SUBSTR [message #671624 is a reply to message #671613] Mon, 10 September 2018 03:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
_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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #671630 is a reply to message #671629] Mon, 10 September 2018 08:03 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
finally got it to work. Many thanks to all for your assistance and this learning experience.
Re: Help w/ REGEXP_SUBSTR [message #671756 is a reply to message #671630] Tue, 18 September 2018 08:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: Help w/ REGEXP_SUBSTR [message #671761 is a reply to message #671760] Tue, 18 September 2018 11:02 Go to previous message
ssmith001
Messages: 37
Registered: August 2018
Member
Perfect. Thanks so much for the help!
Previous Topic: Time difference between 2 rows
Next Topic: OE SAMPEL SCHEMA
Goto Forum:
  


Current Time: Thu Mar 28 05:48:23 CDT 2024