PL/SQL: XML Parsing CyberSource Response Example

 

PL/SQL: XML Parsing CyberSource Response Example

 

Business Need:

Just an example of XML Parsing within PL/SQL.

This can be used to parse any XML within PL/SQL. Make sure APEX JSON is installed in database.

Note: We are using Oracle 12.1.0.0 database version. Some objects and parser are not available in older versions of Oracle database.

 

Program / PL/SQL Script:

set serveroutput on;
     -- Insert data into staging table
    DECLARE
             xAuthRespXML XMLType;
             V_AUTH_RESP_XML VARCHAR2(5000);
             sAuthResponseNameSpace  VARCHAR2(5000);
             v_item_count NUMBER;
       BEGIN
 
-- Substitute your XML Text here
V_AUTH_RESP_XML := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       <soap:Header>
             <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
                    <wsu:Timestamp xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="Timestamp-1489510174">
                           <wsu:Created>2017-07-23T04:27:08.680Z</wsu:Created>
                    </wsu:Timestamp>
             </wsse:Security>
       </soap:Header>
       <soap:Body>
             <c:replyMessage xmlns:c="urn:schemas-cybersource-com:transaction-data-1.165">
                    <c:merchantReferenceCode>EbsONT122052844.27</c:merchantReferenceCode>
                    <c:requestID>5954784279986442603008</c:requestID>
                    <c:decision>ACCEPT</c:decision>
                    <c:reasonCode>100</c:reasonCode>
             <c:requestToken>Axj/7wSTQs87kNkwJAoAABsZLnsZc6HPnObVOMojhEbUIoBURwiNqEVpBK6PcDMhk0ky9GLDX7uBOTQs87kNkwJAoAAAXAhY</c:requestToken>
                    <c:purchaseTotals>
                           <c:currency>USD</c:currency>
                    </c:purchaseTotals>
                    <c:ccAuthReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:amount>1907.11</c:amount>
                           <c:authorizationCode>831000</c:authorizationCode>
                           <c:avsCode>Y</c:avsCode>
                           <c:avsCodeRaw>Y</c:avsCodeRaw>
                           <c:authorizedDateTime>2017-07-23T04:27:08Z</c:authorizedDateTime>
                           <c:processorResponse>00</c:processorResponse>
                           <c:reconciliationID>KO1KNCON9ZSF</c:reconciliationID>
                    <c:authRecord>0110322000000E10000200000000000019071107230427080517684B4F314B4E434F4E395A53463833313030303030000159004400103232415050524F56414C0022313457303136313530373033383032303934473036340006564943524120</c:authRecord>
                    <c:paymentNetworkTransactionID>016150703802094</c:paymentNetworkTransactionID>
                    </c:ccAuthReply>
                    <c:decisionEarlyReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:rcode>1</c:rcode>
                           <c:activeProfileReply/>
                    </c:decisionEarlyReply>
                    <c:card>
                           <c:cardType>001</c:cardType>
                    </c:card>
                    <c:acquirerMerchantNumber>000225130042990</c:acquirerMerchantNumber>
                    <c:pos>
                           <c:terminalID>06011497</c:terminalID>
                    </c:pos>
             </c:replyMessage>
       </soap:Body>
</soap:Envelope>';
 
               dbms_output.put_line('Before FOR loop through Customer XML');
 
               xAuthRespXML :=  XMLType(V_AUTH_RESP_XML);
               sAuthResponseNameSpace := ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:c="urn:schemas-cybersource-com:transaction-data-1.141"';
 
               v_item_count := 0;
               FOR r IN (
                    SELECT
                           ExtractValue(Value(p),'/c:replyMessage/c:merchantReferenceCode/text()', sAuthResponseNameSpace) as merchantReferenceCode
                    FROM   TABLE(XMLSequence(Extract(xAuthRespXML,'/soap:Envelope/soap:Body/c:replyMessage', sAuthResponseNameSpace))) p
                    ) LOOP
 
                    v_item_count := v_item_count + 1;
                    dbms_output.put_line('CustomerXML Loop Number: ' || v_item_count || '; merchantReferenceCode:' || r.merchantReferenceCode);
 
 
 
               END LOOP;
               dbms_output.put_line('After FOR loop through Customer XML');
 
 
 
 
 
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception while inserting Customer Data to Staging table; SQLCODE:' || SQLCODE || '; SQLERRM:' || SQLERRM);
    END;
/
 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Parser, APEX JSON, Patch

Date Conversion Examples: Oracle Date <=> XML ISO8601 Format

Introduction:

Some examples of Date Conversion from Oracle PL/SQL to and from XML Standard Date (ISO8601 format).

 

Examples:

Convert XML Date to Oracle Date Format:

-- CONVERT XML DATE TO ORACLE PL/SQL
SELECT TO_DATE('2017-08-24T12:11:06Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
FROM DUAL;

 

Convert XML Date to Oracle System Timestamp Format:

SELECT TO_TIMESTAMP('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM DUAL;
 
SELECT TO_TIMESTAMP('2017-08-24T12:11:06Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM DUAL;
 
 

Convert Oracle Date to XML ISO8601 Format:

-- CONVERT ORACLE DATE TO XML ISO8601 FORMAT
SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
    )
FROM DUAL;
 

  

Convert Oracle Date to XML ISO8601 Format with Time Zone Details:

SELECT SYSDATE,
TO_CHAR((FROM_TZ(TO_TIMESTAMP(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'AMERICA/NEW_YORK') AT TIME ZONE 'UTC'),'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
"ISO8601"
FROM DUAL

 

  

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, XML Date, ISO8601, Date Function, PL/SQL, Query, SQL, SQLPLUS

 

WIP Tables & Random SQLs – Work In Progress, Discrete Job Tables

 

Business Case:

Some random SQLs related to WIP (Work In Progress/Process) module functionalities. Useful for analyzing and troubleshooting issues related to WIP Job failures, stuck jobs, on-screen errors, etc.

 

Tables:

Major Tables:

WIP_DISCRETE_JOBS
WIP_LINES
WIP_OPERATIONS
WIP_ENTITIES
WIP_TRANSACTIONS

 

Other Useful Tables:

WIP_MOVE_TRANSACTIONS
WIP_DISCRETE_JOBS
WIP_REPETITIVE_SCHEDULES
WIP_REQUIREMENT_OPERATIONS WRO - COMPONENT TABLE

 

Interface Tables:

WIP_MOVE_TXN_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE

 

There are many more tables in Oracle EBS WIP Module. Only the major ones are listed here. For details, please check Oracle eTRM.

 

SQLs:

WIP Job – Header Level Details:

SELECT WE.WIP_ENTITY_ID,      -- SOURCE HEADER REFERENCE ID IN INSTALL BASE
      WE.WIP_ENTITY_NAME,        -- SOURCE HEADER REFERENCE NUMBER IN INSTALL BASE
      WE.CREATION_DATE AS WE_CREATION_DATE,
      DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
      MSIB.SEGMENT1 ASSEMBLY_ITEM_NAME,
      MSIB.DESCRIPTION,
      DSRJ.CLASS_CODE,
      LU1.MEANING STATUS,
      DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
      DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
      DSRJ.START_QUANTITY,
      DSRJ.NET_QUANTITY,
      LU2.MEANING WIP_SUPPLY_TYPE,
      DSRJ.COMPLETION_SUBINVENTORY,
      BD.DEPARTMENT_CODE
FROM WIP_ENTITIES WE,
      WIP_DISCRETE_JOBS DSRJ,
      MTL_SYSTEM_ITEMS_B MSIB ,
      MFG_LOOKUPS LU1,
      MFG_LOOKUPS LU2,
      WIP_OPERATIONS WO,
      BOM_DEPARTMENTS BD,
      BOM_RESOURCES BR,
      WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = DSRJ.STATUS_TYPE
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU2.LOOKUP_CODE = DSRJ.WIP_SUPPLY_TYPE
AND WE.PRIMARY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;

  

WIP Job – Header Level Details, Simpler Query: Same as above, but only required/basic columns selected.

SELECT  WE.PRIMARY_ITEM_ID AS FINAL_PRODUCT, WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
 DSRJ.CLASS_CODE,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
    DSRJ.COMPLETION_SUBINVENTORY
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ, 
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;

  

WIP Job Details – Components and Final Item Details:

SELECT  WE.PRIMARY_ITEM_ID AS FINAL_PRODUCT, WRO.INVENTORY_ITEM_ID AS COMPONENT_ITEM_ID, WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
 DSRJ.CLASS_CODE,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
    DSRJ.COMPLETION_SUBINVENTORY
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ, 
       WIP_REQUIREMENT_OPERATIONS WRO,
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;
 

  

WIP Job Details – Many QIP Job Related Details:

-- Query with many useful WIP Job Related Details
SELECT WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE)JOB_TYPE,
 MSIB.SEGMENT1 ASSEMBLY_ITEM_NAME,
 MSIB.DESCRIPTION,
 DSRJ.CLASS_CODE,
 LU1.MEANING STATUS,
 MMT.TRANSACTION_UOM UOM,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
 LU2.MEANING WIP_SUPPLY_TYPE,
    DSRJ.COMPLETION_SUBINVENTORY,
    MSIV.CONCATENATED_SEGMENTS,
    BD.DEPARTMENT_CODE
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ,        
MTL_SYSTEM_ITEMS_B MSIB ,
MFG_LOOKUPS LU1,
MFG_LOOKUPS LU2,
MTL_MATERIAL_TRANSACTIONS MMT,
 MTL_SYSTEM_ITEMS_VL MSIV,
       WIP_REQUIREMENT_OPERATIONS WRO,
        WIP_OPERATIONS WO,
      BOM_DEPARTMENTS BD,
        BOM_RESOURCES BR,
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='3401881'
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND WE.PRIMARY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = DSRJ.STATUS_TYPE
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU2.LOOKUP_CODE = DSRJ.WIP_SUPPLY_TYPE
AND WE.PRIMARY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
AND  MSIV.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;
  

Keywords:

WIP, Work in Progress, Work In Process, Discrete Manufacturing, EBS, R12, R12.2.6, PL/SQL, Query, SQL, SQLPLUS

 

Oracle EBS – Workflow Background SQLs

 

Business Need:

Some random useful SQLs/Queries related to Oracle Applications (EBS) concurrent programs, schedules, workflow background programs, requisition import process, etc.

 

Queries:

All Concurrent Jobs ran more than 10 times during previous day

-- all concurrent job executions during a single day, more than 10
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-2.1 AND SYSDATE-1.1
--AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
HAVING COUNT(*) > 9
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Get name of above Concurrent Program ID

SELECT *
FROM APPS.FND_CONCURRENT_PROGRAMS_VL CP
--WHERE USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';          -- use name from above SQL
WHERE CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';
 

 

Get more details on frequent program – Eg: Workflow Background

-- qry sql - notes - check concurrent program running prod wfbg
-- WFBG & ReqImport Only, last 2.4 hrs ~around 72 should be good
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.104 AND SYSDATE+.004
AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Requests by a specific user

-- Programs ran by <USER> during past 15 minutes
SELECT CR.REQUEST_ID, CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, TO_CHAR(CR.REQUEST_DATE, 'Mon/dd hh24miss') REQUEST_DATE, CR.REQUESTED_BY
  , CR.PHASE_CODE, DECODE(CR.PHASE_CODE, 'C', 'Completed', 'P', 'Pending', 'zOthers') AS PHASE_NAME
  , CR.STATUS_CODE, DECODE(CR.STATUS_CODE, 'C', 'Normal', 'I', 'Scheduled', 'zOthers') AS STATUS_NAME
  , CR.LOGFILE_NAME, CR.OUTFILE_NAME, CR.ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = 1151              -- use user id of the given user
ORDER BY CR.REQUEST_DATE DESC;
 

All Requests during last 24 hours

-- Conc Requests ran during past 24 hours
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Similar to above

SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
 AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;

  

Keywords:

Oracle EBS, R12, R12.2.8, Workflow, Background, Concurrent Programs, Concurrent Jobs, Executions, WFBG, FND, Query, SQL, SQLPLUS

Basic SQLs – RegEx Regular Expression – SQL Split String

 

Business Need:

Oracle Database SQL, SQL PLUS

Split a sting into multiple parts – name, EDI Inputs, Comma Separated Values, Character delimiters, etc.

 

Tables:

Any …

 

SQLs:

Split based on “_”:

SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 1)) FROM DUAL;        -- 4811             - Order Number / Receipt Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 2)) FROM DUAL;        -- ONT / AR - application name
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 3)) FROM DUAL;        -- 127044    - Payment Order Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 4)) FROM DUAL;        -- 5798526   - Order Header ID
 

“_” can be replaced with COMMA, SPACE, HYPEN, TAB or any other character to split based on the given character.

  

Eg: Consider a name string with “<First> <Middle><Last>” format. Here is the query to get First, Middle and Last names:

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^ ,]+', 1, 2) as firstname,
       regexp_substr(name, '[^ ,]+', 1, 3) as middle 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL, SQLPLUS, regexp_substr, RegEx