Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to PARSE XML files or strings using PL/SQL.

Posted on 2006-06-27
6
Medium Priority
?
683 Views
Last Modified: 2008-01-09
I have inherited some code that I have been asked to modify. Basically an xml string is being passed to a procedure as a clob. Certain values are extracted from the string. Based on on an id the values are either inserted into a table or update a table.  The code was written using basically the INSTR function to find tags then parse values in between tags.  I assume there is XML functionality available in oracle 10G that would make this process much simpler.

I would like to know where to start. Here is the existing package body that uses no built in oracle xml packages.

Here is an example of running the procedure.
DECLARE
  PINPRICEEVENTMSG CLOB;
  POUTERROR NUMBER;
  POUTERRORMSG VARCHAR2(200);
  POUTTIMESTAMP VARCHAR2(200);
  POUTSOURCEID VARCHAR2(200);

BEGIN
  -- PINPRICEEVENTMSG := NULL;  Modify the code to initialize this parameter
  PINPRICEEVENTMSG :='<PDATA><HDR>20060328150300~RMS</HDR><EVH>00111~10000001~R~Y~N~20060101120000~~EventName</EVH><EVD>10000001~175~1.01~A~S||10000001~176~1.02~A~S||10000001~177~1.03~A~S</EVD></PDATA>';
  POUTERROR := NULL;
  POUTERRORMSG := NULL;
  POUTTIMESTAMP := NULL;
  POUTSOURCEID := NULL;

  ADMIN.PRICEEVENT.DATAIMPORT ( PINPRICEEVENTMSG, POUTERROR, POUTERRORMSG, POUTTIMESTAMP, POUTSOURCEID );
  COMMIT;
END;

CREATE OR REPLACE PACKAGE BODY PriceEvent AS

------------------------------------------------------------------------
-- Procedure to be called from Data Import
-- Verifies the Header tag
------------------------------------------------------------------------
PROCEDURE ParseHdrPrc(
       pInPriceEventMsg  IN CLOB
   ,   pOutError      OUT NUMBER
   ,   pOutErrorMsg      OUT VARCHAR
   ,   pOutTimeStamp     OUT VARCHAR
   ,   pOutSourceId      OUT VARCHAR
   ,   pOutEndHdrPos       OUT NUMBER
) IS
   nMsgLength            NUMBER     := dbms_lob.getlength(pInPriceEventMsg);
   nBeginPdtPos              NUMBER;
   nEndPdtPos              NUMBER;
   nBeginHdrPos              NUMBER;
   nEndHdrPos              NUMBER;
   nHdrCol1              NUMBER;
   nHdrCol2              NUMBER;
   cHdrRow               VARCHAR2(50);
   cTimeStamp            VARCHAR2(25);
   dTimeStamp            TIMESTAMP;
   cSourceId             VARCHAR2(25);
BEGIN
   --
   /********************************dpl********************************************
   We Check if the message is null if it is rollback.
   Set other out variables.
   ********************************************************************************/
   IF NVL(nMsgLength, 0) = 0 THEN
      ROLLBACK;
      pOutError  := 1;
      pOutErrorMsg  := 'The Price Event Message is empty';
      pOutTimeStamp := NULL;
      pOutSourceId  := NULL;
      pOutEndHdrPos   := NULL;
      RETURN;
   END IF;

   -- Check if beginning PDATA tag exists
   nBeginPdtPos := INSTR (pInPriceEventMsg, gBeginPdtTag);
   IF nBeginPdtPos = 0 THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Header beginning tag (' || gBeginPdtTag || ') is missing';
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Check if ending PDATA tag exists
   nEndPdtPos := INSTR (pInPriceEventMsg, gEndPdtTag, -1, 1);
   IF nEndPdtPos = 0 THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Header ending tag (' || gEndPdtTag || ') is missing';
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Check if ending PDATA tag is at the end of the string
   IF nEndPdtPos + gEndpdtLen - 1 <> nMsgLength  THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Header ending tag (' || gEndPdtTag || ') is not ending the message';
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Check if there are any characters between the tags
   IF SUBSTR(pInPriceEventMsg, nEndPdtPos - gEndEvdLen, gEndEvdLen) <> gEndEvdTag THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'There are invalid characters between ' || gEndEvdTag || ' and ' ||
         gEndPdtTag || ' tags';
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Check if beginning HDR tag exists
   nBeginHdrPos := INSTR (pInPriceEventMsg, gBeginHdrTag);
   IF nBeginHdrPos = 0 THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Header beginning tag (' || gBeginHdrTag || ') is missing';
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Check if there are any invalid characters between the tags
   IF nBeginHdrPos <>  nBeginPdtPos + gBeginPdtLen THEN
      ROLLBACK;
      pOutError  := 1;
      pOutErrorMsg  := 'There are invalid characters between ' || gBeginPdtTag || ' and ' ||
         gBeginHdrTag || ' tags';
      pOutTimeStamp := NULL;
      pOutSourceId  := NULL;
      pOutEndHdrPos   := NULL;
      RETURN;
   END IF;

   -- Check if ending HDR tag exists
   nEndHdrPos := INSTR (pInPriceEventMsg, gEndHdrTag);
   IF nEndHdrPos = 0 THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Header ending tag (' || gEndHdrTag || ') is missing';
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Check if HDR tags are exchanged
   IF nBeginHdrPos >= nEndHdrPos THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Header begining tag (' || gBeginHdrTag || ') and ending tag (' || gEndHdrTag ||
        ') are interchanged' ;
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   -- Get the HDR row
   cHdrRow := SUBSTR(pInPriceEventMsg, nBeginHdrPos + gBeginHdrLen, nEndHdrPos - (nBeginHdrPos + gBeginHdrLen));

   -- Parse the HDR row
   nHdrCol1   := INSTR(cHdrRow, gColSep, 1, 1);
   IF NVL(nHdrCol1, 0) = 0 THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'Less than expected columns found in the Header Section' ;
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   nHdrCol2   := INSTR(cHdrRow, gColSep, 1, 2);
   IF NVL(nHdrCol2, 0) <> 0 THEN
      ROLLBACK;
      pOutError    := 1;
      pOutErrorMsg    := 'More than expected columns found in the Header Section' ;
      pOutTimeStamp   := NULL;
      pOutSourceId    := NULL;
      pOutEndHdrPos     := NULL;
      RETURN;
   END IF;

   cTimeStamp := SUBSTR (cHdrRow, 1, nHdrCol1 - 1 );
   cSourceId  := SUBSTR (cHdrRow, nHdrCol1 + 1);

   -- Verify the timestamp is a valid date
   BEGIN
      SELECT TO_DATE(cTimeStamp, gDateFmt)
      INTO   dTimeStamp
      FROM   DUAL;
   EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;
         pOutError    := 1;
         pOutErrorMsg    := cTimeStamp || ' is not a valid date';
         pOutTimeStamp   := NULL;
         pOutSourceId    := NULL;
         pOutEndHdrPos     := NULL;
         RETURN;
   END;

   -- Everything looks fine so return the values
   pOutError    := 0;
   pOutErrorMsg    := NULL;
   pOutTimeStamp   := cTimeStamp;
   pOutSourceId    := cSourceId;
   pOutEndHdrPos     := nEndHdrPos;
   RETURN;
EXCEPTION
WHEN OTHERS THEN
   ROLLBACK;
   pOutError    := 1;
   pOutErrorMsg    := 'Error occurred in ParseHdrPrc (' || SQLCODE || ')' || SQLERRM;
   pOutTimeStamp   := NULL;
   pOutSourceId    := NULL;
   pOutEndHdrPos     := NULL;
   DBMS_OUTPUT.PUT_LINE(pOutErrorMsg);
END ParseHdrPrc;


------------------------------------------------------------------------
-- This function will return TRUE if the row exist otherwise FALSE
------------------------------------------------------------------------
FUNCTION CheckRowExists (
       pItemId     IN VARCHAR
   ,   pStoreId    IN VARCHAR
   ,   pEventId    IN NUMBER
) RETURN ROWID IS
   cRowId          ROWID;
BEGIN
   SELECT ROWID
   INTO   cRowId
   FROM   PS_ITM_LOC_PRC pilc
   WHERE  pilc.id_itm    = pItemId
   AND    pilc.id_str_rt = pStoreId
   AND    id_evt_prc_chn = pEventId;

   RETURN cRowId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
   RETURN NULL;
WHEN OTHERS THEN
   ROLLBACK;
   RAISE;
END CheckRowExists;


------------------------------------------------------------------------
-- Procedure to be called from Data Import
-- Inserts a row into the table PS_ITM_LOC_PRC
------------------------------------------------------------------------
PROCEDURE InsertRow (
       pEVHStoreId               IN PS_ITM_LOC_PRC.ID_STR_RT%TYPE
   ,   pEVHEventId               IN PS_ITM_LOC_PRC.ID_EVT_PRC_CHN%TYPE
   ,   pEVHEventType             IN PS_ITM_LOC_PRC.TY_PRC_CHN%TYPE
   ,   pEVHEventSignFlag         IN PS_ITM_LOC_PRC.FL_SGN_EVT_PRC%TYPE
   ,   pEVHEventOverrideFlag     IN PS_ITM_LOC_PRC.FL_OVRD%TYPE
   ,   pEVHEventBeginTimeStamp   IN PS_ITM_LOC_PRC.DC_EFF_CUR_SLS%TYPE
   ,   pEVHEventEndTimeStamp     IN PS_ITM_LOC_PRC.DC_EXP_CUR_SLS%TYPE
   ,   pEVHEventName             IN PS_ITM_LOC_PRC.NM_EVT_PRC_CHN%TYPE
   ,   pEVDItemId                IN PS_ITM_LOC_PRC.ID_ITM%TYPE
   ,   pEVDItemPrice             IN PS_ITM_LOC_PRC.RP_CUR_SLS%TYPE
   ,   pEVDItemStatusCode        IN PS_ITM_LOC_PRC.SC_ITM_LOC_PRC%TYPE
   ,   pEVDSaleType              IN PS_ITM_LOC_PRC.TY_SLS%TYPE
   ,   pOutError                OUT NUMBER
   ,   pOutErrorMsg                OUT VARCHAR)
IS
BEGIN
   INSERT INTO PS_ITM_LOC_PRC (
       ID_STR_RT
   ,   ID_EVT_PRC_CHN
   ,   TY_PRC_CHN
   ,   FL_SGN_EVT_PRC
   ,   FL_OVRD
   ,   DC_EFF_CUR_SLS
   ,   DC_EXP_CUR_SLS
   ,   NM_EVT_PRC_CHN
   ,   ID_ITM
   ,   RP_CUR_SLS
   ,   SC_ITM_LOC_PRC
   ,   TY_SLS
   ,   DE_RSN_EVT_PRC_CHN
   ,   SC_EFF
   ,   SC_EXP
   ,   SC_UPD
   ,   TS_CRT_RCRD
   ,   TS_MDF_RCRD)
   VALUES (
       pEVHStoreId
   ,   pEVHEventId
   ,   pEVHEventType
   ,   pEVHEventSignFlag
   ,   pEVHEventOverrideFlag
   ,   pEVHEventBeginTimeStamp
   ,   pEVHEventEndTimeStamp
   ,   pEVHEventName
   ,   pEVDItemId
   ,   pEVDItemPrice
   ,   pEVDItemStatusCode
   ,   pEVDSaleType
   ,   NULL
   ,   0
   ,   0
   ,   gInsFlag
   ,   SYSDATE
   ,   SYSDATE);
EXCEPTION
WHEN OTHERS THEN
   ROLLBACK;
   pOutError    := 1;
   pOutErrorMsg    := 'Error occurred in InsertRow (' || SQLCODE || ')' || SQLERRM;
   /*pOutErrorMsg    := '[eventID=' || pEVHEventId || ', storeID=' || pEVHStoreId ||

                    ', itemID=' || pEVDItemId || '] ' ||

                    'Error occurred in InsertRow (' || SQLCODE || ')' || SQLERRM;

   DBMS_OUTPUT.PUT_LINE(pOutError);*/


   DBMS_OUTPUT.PUT_LINE(pOutErrorMsg);
END InsertRow;


------------------------------------------------------------------------
-- Procedure to be called from Data Import
-- Updates a row into the table PS_ITM_LOC_PRC
------------------------------------------------------------------------
PROCEDURE UpdateRow (
       pRowId                    IN ROWID
   ,   pEVHStoreId               IN PS_ITM_LOC_PRC.ID_STR_RT%TYPE
   ,   pEVHEventId               IN PS_ITM_LOC_PRC.ID_EVT_PRC_CHN%TYPE
   ,   pEVHEventType             IN PS_ITM_LOC_PRC.TY_PRC_CHN%TYPE
   ,   pEVHEventSignFlag         IN PS_ITM_LOC_PRC.FL_SGN_EVT_PRC%TYPE
   ,   pEVHEventOverrideFlag     IN PS_ITM_LOC_PRC.FL_OVRD%TYPE
   ,   pEVHEventBeginTimeStamp   IN PS_ITM_LOC_PRC.DC_EFF_CUR_SLS%TYPE
   ,   pEVHEventEndTimeStamp     IN PS_ITM_LOC_PRC.DC_EXP_CUR_SLS%TYPE
   ,   pEVHEventName             IN PS_ITM_LOC_PRC.NM_EVT_PRC_CHN%TYPE
   ,   pEVDItemId                IN PS_ITM_LOC_PRC.ID_ITM%TYPE
   ,   pEVDItemPrice             IN PS_ITM_LOC_PRC.RP_CUR_SLS%TYPE
   ,   pEVDItemStatusCode        IN PS_ITM_LOC_PRC.SC_ITM_LOC_PRC%TYPE
   ,   pEVDSaleType              IN PS_ITM_LOC_PRC.TY_SLS%TYPE
   ,   pOutError                OUT NUMBER
   ,   pOutErrorMsg                OUT VARCHAR)
IS
BEGIN
   UPDATE PS_ITM_LOC_PRC
   SET    TY_PRC_CHN            = pEVHEventType
   ,      FL_SGN_EVT_PRC        = pEVHEventSignFlag
   ,      FL_OVRD               = pEVHEventOverrideFlag
   ,      DC_EFF_CUR_SLS        = pEVHEventBeginTimeStamp
   ,      DC_EXP_CUR_SLS        = pEVHEventEndTimeStamp
   ,      NM_EVT_PRC_CHN        = pEVHEventName
   ,      RP_CUR_SLS            = pEVDItemPrice
   ,      SC_ITM_LOC_PRC        = pEVDItemStatusCode
   ,      TY_SLS                = pEVDSaleType
   ,      DE_RSN_EVT_PRC_CHN    = NULL
 --,      SC_EFF                = NULL
 --,      SC_EXP                = NULL
   ,      SC_UPD                = gUpdFlag
   ,      TS_MDF_RCRD           = SYSDATE
   WHERE  ROWID = pRowId;
EXCEPTION
WHEN OTHERS THEN
   ROLLBACK;
   pOutError    := 1;
   pOutErrorMsg    := 'Error occurred in UpdateRow (' || SQLCODE || ')' || SQLERRM;
   DBMS_OUTPUT.PUT_LINE(pOutErrorMsg);
END UpdateRow;


------------------------------------------------------------------------
-- Main procedure to be called from Java Application
------------------------------------------------------------------------
PROCEDURE DataImportPrc(
       pInPriceEventMsg  IN CLOB
   ,   pOutError      OUT NUMBER
   ,   pOutErrorMsg      OUT VARCHAR
   ,   pOutTimeStamp     OUT VARCHAR
   ,   pOutSourceId      OUT VARCHAR
) IS
    cPriceEventMsg   CLOB          := pInPriceEventMsg;
    nEndrrorOut        NUMBER;
    cErrorMsg        VARCHAR2(100);
    cTimeStamp       VARCHAR2(20);
    cSourceId        VARCHAR2(100);
    nEndHdrPos         NUMBER;
    nBeginevhPos         NUMBER;
    nEndEvhPos         NUMBER;
    nTotEvh          NUMBER;
    nTotEvd          NUMBER;
    nBeginEvdPos         NUMBER;
    nEndEvdPos         NUMBER;

    cEvhRow          VARCHAR2(100);
    nevhCol1         NUMBER;
    nevhCol2         NUMBER;
    nevhCol3         NUMBER;
    nevhCol4         NUMBER;
    nevhCol5         NUMBER;
    nevhCol6         NUMBER;
    nevhCol7         NUMBER;
    nevhCol8         NUMBER;
    cStoreId         PS_ITM_LOC_PRC.ID_STR_RT%TYPE;
    nEventId         PS_ITM_LOC_PRC.ID_EVT_PRC_CHN%TYPE;
    cEventTy         PS_ITM_LOC_PRC.TY_PRC_CHN%TYPE;
    cSgnFlag         PS_ITM_LOC_PRC.FL_SGN_EVT_PRC%TYPE;
    cOvrFlag         PS_ITM_LOC_PRC.FL_OVRD%TYPE;
    dBeginTS         PS_ITM_LOC_PRC.DC_EFF_CUR_SLS%TYPE;
    dEndTS           PS_ITM_LOC_PRC.DC_EXP_CUR_SLS%TYPE;
    cEventNm         PS_ITM_LOC_PRC.NM_EVT_PRC_CHN%TYPE;
    cBeginTS         VARCHAR2(20);
    cEndTS           VARCHAR2(20);

    cEvdRow          CLOB;
    nStarter         NUMBER;
    nPosition        NUMBER;
    nEvdCol1         NUMBER;
    nEvdCol2         NUMBER;
    nEvdCol3         NUMBER;
    nEvdCol4         NUMBER;
    nEvdCol5         NUMBER;
    nEvdCol6         NUMBER;
    nEvdEvnt         PS_ITM_LOC_PRC.ID_EVT_PRC_CHN%TYPE;
    cItemID          PS_ITM_LOC_PRC.ID_ITM%TYPE;
    nItemPr          PS_ITM_LOC_PRC.RP_CUR_SLS%TYPE;
    cIStsCd          PS_ITM_LOC_PRC.SC_ITM_LOC_PRC%TYPE;
    cSaleTyp         PS_ITM_LOC_PRC.TY_SLS%TYPE;

    cRowId           ROWID;

BEGIN
    -- Parse the HDR tag
    ParseHdrPrc(
           pInPriceEventMsg  => cPriceEventMsg
       ,   pOutError       => nEndrrorOut
       ,   pOutErrorMsg       => cErrorMsg
       ,   pOutTimeStamp      => cTimeStamp
       ,   pOutSourceId       => cSourceId
       ,   pOutEndHdrPos        => nEndHdrPos);

    IF NVL(nEndrrorOut, 0) = 1 THEN -- Error
       ROLLBACK;
       pOutError  := nEndrrorOut;
       pOutErrorMsg  := cErrorMsg;
       pOutTimeStamp := NULL;
       pOutSourceId  := NULL;
       RETURN;
    END IF;

    -- Processing of EVH
    -- Check if there are any EVH tags available
    nBeginevhPos  := INSTR(cPriceEventMsg, gBeginEvhTag, nEndHdrPos + gEndHdrLen);
    IF nBeginevhPos = 0 THEN
       ROLLBACK;
       pOutError  := 1;
       pOutErrorMsg  := 'There are no ' || gBeginEvhTag || ' tags found';
       pOutTimeStamp := NULL;
       pOutSourceId  := NULL;
       RETURN;
    END IF;

    -- Check if there are any invalid characters between the strings
    IF nBeginevhPos <>  nEndHdrPos + gEndHdrLen THEN
       ROLLBACK;
       pOutError  := 1;
       pOutErrorMsg  := 'There are invalid characters between ' || gEndHdrTag || ' and ' ||
          gBeginEvhTag || ' tags';
       pOutTimeStamp := NULL;
       pOutSourceId  := NULL;
       RETURN;
    END IF;

    -- Process EVH row
    WHILE nBeginevhPos <> 0
    LOOP
       -- Check if ending EVH tag exists
       nEndEvhPos := INSTR (pInPriceEventMsg, gEndEvhTag, nBeginevhPos);
       IF nEndEvhPos = 0 THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'Event Header ending tag (' || gEndEvhTag || ') is missing';
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END IF;

       -- Check if EVH tags are exchanged
       IF nBeginevhPos >= nEndEvhPos THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'Event Header begining tag (' || gBeginEvhTag || ') and ending tag (' ||
             gEndEvhTag || ') are interchanged' ;
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END IF;

       -- Get the EVH row
       cEvhRow := SUBSTR(pInPriceEventMsg, nBeginevhPos + gBeginevhLen, nEndEvhPos - (nBeginevhPos + gBeginevhLen));
       IF cEvhRow IS NULL THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'There are no details found in the Event Header';
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

       -- Parse the EVH row
       -- Begin with the column separater
       nevhCol1         := INSTR(cEvhRow, gColSep, 1, 1);
       nevhCol2         := INSTR(cEvhRow, gColSep, 1, 2);
       nevhCol3         := INSTR(cEvhRow, gColSep, 1, 3);
       nevhCol4         := INSTR(cEvhRow, gColSep, 1, 4);
       nevhCol5         := INSTR(cEvhRow, gColSep, 1, 5);
       nevhCol6         := INSTR(cEvhRow, gColSep, 1, 6);
       nevhCol7         := INSTR(cEvhRow, gColSep, 1, 7);
       nevhCol8         := INSTR(cEvhRow, gColSep, 1, 8);
       IF NVL(nevhCol8, 0) <> 0 THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'More than expected columns found in the Event Header';
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

       -- Get the column values
       BEGIN
          cStoreId         := SUBSTR (cEvhRow, 1, nevhCol1 - 1);
          nEventId         := SUBSTR (cEvhRow, nevhCol1 + 1, nevhCol2 - (nevhCol1 + 1));
          cEventTy         := SUBSTR (cEvhRow, nevhCol2 + 1, nevhCol3 - (nevhCol2 + 1));
          cSgnFlag         := SUBSTR (cEvhRow, nevhCol3 + 1, nevhCol4 - (nevhCol3 + 1));
          cOvrFlag         := SUBSTR (cEvhRow, nevhCol4 + 1, nevhCol5 - (nevhCol4 + 1));
          cBeginTS         := SUBSTR (cEvhRow, nevhCol5 + 1, nevhCol6 - (nevhCol5 + 1));
          cEndTS           := SUBSTR (cEvhRow, nevhCol6 + 1, nevhCol7 - (nevhCol6 + 1));
          cEventNm         := SUBSTR (cEvhRow, nevhCol7 + 1);
       EXCEPTION
       WHEN OTHERS THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'Error occurred while parsing the columns of the Event Header';
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END;

       -- Verify if Event Begin Timestamp is a valid date
       BEGIN
          SELECT TO_DATE(cBeginTS, gDateFmt)
          INTO   dBeginTS
          FROM   DUAL;
       EXCEPTION
       WHEN OTHERS THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := cBeginTS || ' is not a valid date';
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END;

       -- Verify if Event End Timestamp is a valid date
       BEGIN
          SELECT TO_DATE(cEndTS, gDateFmt)
          INTO   dEndTS
          FROM   DUAL;
       EXCEPTION
       WHEN OTHERS THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := cEndTS || ' is not a valid date';
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END;

       -- Processing of EVD
       -- Check if there are any EVD tags available
       nBeginEvdPos  := INSTR(cPriceEventMsg, gBeginEvdTag, nEndEvhPos + gEndEvhLen);
       IF nBeginEvdPos = 0 THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'There are no ' || gBeginEvdTag || ' tags found';
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

       -- Check if there are any invalid characters between the strings
       IF (nBeginEvdPos <>  nEndEvhPos + gEndEvhLen) THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'There are invalid characters between ' || gEndEvhTag || ' and ' ||
             gBeginEvdTag || ' tags';
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

       -- Process EVD row
       -- Check if ending EVD tag exists
       nEndEvdPos := INSTR (pInPriceEventMsg, gEndEvdTag, nBeginEvdPos);
       IF nEndEvdPos = 0 THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'Event Detail ending tag (' || gEndEvdTag || ') is missing';
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END IF;

       -- Check if EVD tags are exchanged
       IF nBeginEvdPos >= nEndEvdPos THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'Event Detail begining tag (' || gBeginEvdTag || ') and ending tag (' ||
             gEndEvdTag || ') are interchanged' ;
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END IF;

       -- Get the EVD row
       cEvdRow := SUBSTR(pInPriceEventMsg, nBeginEvdPos + gBeginEvdLen, nEndEvdPos - (nBeginEvdPos + gBeginEvdLen));
       IF cEvdRow IS NULL THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'No Rows found in the Event Details' ;
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END IF;

       -- Process all the EVD rows
       nStarter  := 1;
       nPosition := NVL(INSTR(cEvdRow, gRowSep, 1), 0);

       -- Process all the remaining rows
       WHILE nPosition <> 0
       LOOP
          -- Parse the EVD row
          -- Begin with the column separater
          nEvdCol1         := NVL(INSTR(cEvdRow, gColSep, nStarter, 1), 0);
          nEvdCol2         := NVL(INSTR(cEvdRow, gColSep, nStarter, 2), 0);
          nEvdCol3         := NVL(INSTR(cEvdRow, gColSep, nStarter, 3), 0);
          nEvdCol4         := NVL(INSTR(cEvdRow, gColSep, nStarter, 4), 0);
          nEvdCol5         := NVL(INSTR(cEvdRow, gColSep, nStarter, 5), 0);
          IF (nEvdCol5 <> 0) AND (nEvdCol5 < nPosition) THEN
             ROLLBACK;
             pOutError    := 1;
             pOutErrorMsg    := 'More than expected columns found in the Event Details' ;
             pOutTimeStamp   := NULL;
             pOutSourceId    := NULL;
             RETURN;
          END IF;

          -- Get the column values and convert them from CLOB to CHAR
          BEGIN
             nEvdEvnt         := TO_CHAR (SUBSTR (cEvdRow, nStarter, nEvdCol1 - nStarter));
             cItemID          := TO_CHAR (SUBSTR (cEvdRow, nEvdCol1 + 1, nEvdCol2 - (nEvdCol1 + 1)));
             nItemPr          := TO_CHAR (SUBSTR (cEvdRow, nEvdCol2 + 1, nEvdCol3 - (nEvdCol2 + 1)));
             cIStsCd          := TO_CHAR (SUBSTR (cEvdRow, nEvdCol3 + 1, nEvdCol4 - (nEvdCol3 + 1)));
             cSaleTyp         := TO_CHAR (SUBSTR (cEvdRow, nEvdCol4 + 1, nPosition - (nEvdCol4 + 1)));
          EXCEPTION
          WHEN OTHERS THEN
             ROLLBACK;
             pOutError    := 1;
             pOutErrorMsg    := 'Error occurred while parsing the columns of the Event Details' ;
             pOutTimeStamp   := NULL;
             pOutSourceId    := NULL;
             RETURN;
          END;

          -- Check if Event Id matches with the Event Header
          IF nEvdEvnt <> nEventId THEN
             ROLLBACK;
             pOutError  := 1;
             pOutErrorMsg  := 'The Event ID of Event Header ' || nEventId ||
                ' does not match with Event ID of Event Detail ' || nEvdEvnt;
             pOutTimeStamp := NULL;
             pOutSourceId  := NULL;
             RETURN;
          END IF;

          -- Verify if this row exists
          cRowId := CheckRowExists (cItemId, cStoreId, nEventId);
          IF cRowId IS NULL THEN
             DBMS_OUTPUT.PUT_LINE ('Row Does Not Exist. Inserting the row');
             InsertRow (
                    pEVHStoreId               => cStoreId
                ,   pEVHEventId               => nEventId
                ,   pEVHEventType             => cEventTy
                ,   pEVHEventSignFlag         => cSgnFlag
                ,   pEVHEventOverrideFlag     => cOvrFlag
                ,   pEVHEventBeginTimeStamp   => dBeginTS
                ,   pEVHEventEndTimeStamp     => dEndTS
                ,   pEVHEventName             => cEventNm
                ,   pEVDItemId                => cItemID
                ,   pEVDItemPrice             => nItemPr
                ,   pEVDItemStatusCode        => cIStsCd
                ,   pEVDSaleType              => cSaleTyp
                ,   pOutError                 => nEndrrorOut
                ,   pOutErrorMsg              => cErrorMsg);
          ELSE
             DBMS_OUTPUT.PUT_LINE ('Row Exists. Updating the row');
             UpdateRow (
                    pRowId                    => cRowId
                ,   pEVHStoreId               => cStoreId
                ,   pEVHEventId               => nEventId
                ,   pEVHEventType             => cEventTy
                ,   pEVHEventSignFlag         => cSgnFlag
                ,   pEVHEventOverrideFlag     => cOvrFlag
                ,   pEVHEventBeginTimeStamp   => dBeginTS
                ,   pEVHEventEndTimeStamp     => dEndTS
                ,   pEVHEventName             => cEventNm
                ,   pEVDItemId                => cItemID
                ,   pEVDItemPrice             => nItemPr
                ,   pEVDItemStatusCode        => cIStsCd
                ,   pEVDSaleType              => cSaleTyp
                ,   pOutError                 => nEndrrorOut
                ,   pOutErrorMsg                 => cErrorMsg);
          END IF;

          -- Check for any errors
          IF NVL(nEndrrorOut, 0) = 1 THEN -- Error
             ROLLBACK;
             pOutError  := nEndrrorOut;
             pOutErrorMsg  := cErrorMsg;
             pOutTimeStamp := NULL;
             pOutSourceId  := NULL;
             RETURN;
          END IF;

          -- Recalculate the values
          nStarter  := nPosition + gRowLen;
          nPosition := INSTR(cEvdRow, gRowSep, nPosition + gRowLen);

       END LOOP;
       -- get the last row
       nEvdCol1         := INSTR(cEvdRow, gColSep, nStarter, 1);
       nEvdCol2         := INSTR(cEvdRow, gColSep, nStarter, 2);
       nEvdCol3         := INSTR(cEvdRow, gColSep, nStarter, 3);
       nEvdCol4         := INSTR(cEvdRow, gColSep, nStarter, 4);
       nEvdCol5         := INSTR(cEvdRow, gColSep, nStarter, 5);
       IF NVL(nEvdCol5, 0) <> 0 THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'More than expected columns found in the last row of the Event Details' ;
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END IF;

       -- Get the column values
       BEGIN
          nEvdEvnt         := TO_CHAR (SUBSTR (cEvdRow, nStarter, nEvdCol1 - nStarter));
          cItemID          := TO_CHAR (SUBSTR (cEvdRow, nEvdCol1 + 1, nEvdCol2 - (nEvdCol1 + 1)));
          nItemPr          := TO_CHAR (SUBSTR (cEvdRow, nEvdCol2 + 1, nEvdCol3 - (nEvdCol2 + 1)));
          cIStsCd          := TO_CHAR (SUBSTR (cEvdRow, nEvdCol3 + 1, nEvdCol4 - (nEvdCol3 + 1)));
          cSaleTyp         := TO_CHAR (SUBSTR (cEvdRow, nEvdCol4 + 1));
       EXCEPTION
       WHEN OTHERS THEN
          ROLLBACK;
          pOutError    := 1;
          pOutErrorMsg    := 'Error occurred while parsing the columns of the ' ||
             'last row of the Event Details' ;
          pOutTimeStamp   := NULL;
          pOutSourceId    := NULL;
          RETURN;
       END;

       -- Check if Event Id matches with the Event Header
       IF nEvdEvnt <> nEventId THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'The Event ID of Event Header ' || nEventId ||
             ' does not match in the last row with Event ID of Event Detail ' || nEvdEvnt;
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

       -- Verify if this row exists
       cRowId := CheckRowExists (cItemId, cStoreId, nEventId);
       IF cRowId IS NULL THEN
          DBMS_OUTPUT.PUT_LINE ('Row Does Not Exist. Inserting the row');
          InsertRow (
                 pEVHStoreId               => cStoreId
             ,   pEVHEventId               => nEventId
             ,   pEVHEventType             => cEventTy
             ,   pEVHEventSignFlag         => cSgnFlag
             ,   pEVHEventOverrideFlag     => cOvrFlag
             ,   pEVHEventBeginTimeStamp   => dBeginTS
             ,   pEVHEventEndTimeStamp     => dEndTS
             ,   pEVHEventName             => cEventNm
             ,   pEVDItemId                => cItemID
             ,   pEVDItemPrice             => nItemPr
             ,   pEVDItemStatusCode        => cIStsCd
             ,   pEVDSaleType              => cSaleTyp
             ,   pOutError                 => nEndrrorOut
             ,   pOutErrorMsg                 => cErrorMsg);
       ELSE
          DBMS_OUTPUT.PUT_LINE ('Row Exists. Updating the row');
             UpdateRow (
                    pRowId                    => cRowId
                ,   pEVHStoreId               => cStoreId
                ,   pEVHEventId               => nEventId
                ,   pEVHEventType             => cEventTy
                ,   pEVHEventSignFlag         => cSgnFlag
                ,   pEVHEventOverrideFlag     => cOvrFlag
                ,   pEVHEventBeginTimeStamp   => dBeginTS
                ,   pEVHEventEndTimeStamp     => dEndTS
                ,   pEVHEventName             => cEventNm
                ,   pEVDItemId                => cItemID
                ,   pEVDItemPrice             => nItemPr
                ,   pEVDItemStatusCode        => cIStsCd
                ,   pEVDSaleType              => cSaleTyp
                ,   pOutError                 => nEndrrorOut
                ,   pOutErrorMsg                 => cErrorMsg);
       END IF;

       -- Check for any errors
       IF NVL(nEndrrorOut, 0) = 1 THEN -- Error
          ROLLBACK;
          pOutError  := nEndrrorOut;
          pOutErrorMsg  := cErrorMsg;
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

       nBeginevhPos  := INSTR(cPriceEventMsg, gBeginEvhTag, nEndEvdPos + gEndEvdLen);

       -- Check if there are any invalid characters between the strings
       IF (nBeginevhPos <> 0) AND (nBeginevhPos <>  nEndEvdPos + gEndEvdLen) THEN
          ROLLBACK;
          pOutError  := 1;
          pOutErrorMsg  := 'There are invalid characters between ' || gEndEvhTag || ' and ' ||
             gBeginEvdTag || ' tags in the last row';
          pOutTimeStamp := NULL;
          pOutSourceId  := NULL;
          RETURN;
       END IF;

    END LOOP;

    -- Everything went well hence return the HDR columns
    pOutError  := 0;
    pOutErrorMsg  := NULL;
    pOutTimeStamp := cTimeStamp;
    pOutSourceId  := cSourceId;

    -- ODH (4.6.2006) - Commit will be done by calling application
    -- Finally commit the transaction
    --COMMIT;
EXCEPTION
WHEN OTHERS THEN
   ROLLBACK;
   pOutError := 1;
   pOutErrorMsg := 'Error occurred in DataImportPrc (' || SQLCODE || ')' || SQLERRM;
   pOutTimeStamp := NULL;
   pOutSourceId  := NULL;
   DBMS_OUTPUT.PUT_LINE(pOutErrorMsg);
END DataImportPrc;

END PriceEvent;
/
0
Comment
Question by:dplinnane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 400 total points
ID: 16997161
0
 
LVL 9

Accepted Solution

by:
neo9414 earned 1200 total points
ID: 16998289
The above link from sathyagiri should give you the details in implementing your solution.

Here is an example using your code snippet to have a quick look on its working.

create or replace procedure myparse (
       pInPriceEventMsg  IN CLOB
   ,   pOutError      OUT NUMBER
   ,   pOutErrorMsg      OUT VARCHAR
   ,   pOutTimeStamp     OUT VARCHAR
   ,   pOutSourceId      OUT VARCHAR) as

  xmldata    XMLType;
  data varchar2(100);

begin
  -- convert clob into xmltype instance
  xmldata := XMLType.createXML(pInPriceEventMsg);
 
  --get "HDR" data
  begin
        SELECT extractValue(xmldata, '/PDATA/HDR') "REFERENCE"
          into data
          FROM dual
          WHERE existsNode(xmldata, '/PDATA/HDR') = 1;
        dbms_output.put_line('Data: '||data);
  exception
      when no_data_found then
            ROLLBACK;
            pOutError       := 1;
            pOutErrorMsg    := 'Header tag (<HDR>) is missing';
            pOutTimeStamp   := NULL;
            pOutSourceId    := NULL;
            pOutEndHdrPos     := NULL;
            RETURN;      
  end;

  --get "EVH" data
  begin
        SELECT extractValue(xmldata, '/PDATA/EVH') "REFERENCE"
          into data
          FROM dual
          WHERE existsNode(xmldata, '/PDATA/EVH') = 1;
        dbms_output.put_line('Data: '||data);
  exception
      when no_data_found then
            ROLLBACK;
            pOutError       := 1;
            pOutErrorMsg    := 'Evh tag (<EVH>) is missing';
            pOutTimeStamp   := NULL;
            pOutSourceId    := NULL;
            pOutEndHdrPos     := NULL;
            RETURN;      
  end;
 
  --You can continue this way and store the data in respective variable to pass it for storing
  -- them into database.

end;
/


SQL> set serverout on

SQL>
DECLARE
  PINPRICEEVENTMSG CLOB;
  POUTERROR NUMBER;
  POUTERRORMSG VARCHAR2(200);
  POUTTIMESTAMP VARCHAR2(200);
  POUTSOURCEID VARCHAR2(200);
BEGIN
  -- PINPRICEEVENTMSG := NULL;  Modify the code to initialize this parameter
  PINPRICEEVENTMSG :='<PDATA><HDR>20060328150300~RMS</HDR><EVH>00111~10000001~R~Y~N~20060101120000~~EventName</EVH><EVD>10000001~175~1.01~A~S||10000001~176~1.02~A~S||10000001~177~1.03~A~S</EVD></PDATA>';
  POUTERROR := NULL;
  POUTERRORMSG := NULL;
  POUTTIMESTAMP := NULL;
  POUTSOURCEID := NULL;
  myparse ( PINPRICEEVENTMSG, POUTERROR, POUTERRORMSG, POUTTIMESTAMP, POUTSOURCEID );  
END;
/



Data: 20060328150300~RMS
EVH: 00111~10000001~R~Y~N~20060101120000~~EventName

PL/SQL procedure successfully completed.


cheers,
Neo

0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 400 total points
ID: 16998823
If you know the field names thenthe values can be inserted to a table.
In the sample code l_xmlt holds the XML data. The tablename is passed to dbms_xmlsave.newContext. This table will have the parsed data.

Run the SQL script to create dbms_xmlsave package
$ORACLE_HOME/rdbms/admin/dbmsxml.sql

declare
l_clob CLOB;
l_ctx   dbms_xmlsave.ctxType;
l_rows  number;
l_xmlt  xmltype;
BEGIN
l_clob := xmltype.getclobval(l_xmlt);
l_ctx := dbms_xmlsave.newContext('MYTAB');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
END;
/

Also see the links
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4061080732051
http://www.oracle.com/technology/oramag/oracle/03-jul/o43xml.html


The other option is to parse the XML document using dbms_xmlparser built-in package in Oracle 9i

Use dbms_xmlparser and dbms_xmldom packages to parse the XML document and get fields withing the DOM hierarchy.

XDB has to be installed as sysdba. See the link below for installing XML DB

Installing and Configuring XML DB - http://www.idevelopment.info/data/Oracle/DBA_tips/xmldb/XMLDB_3.shtml


l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_xmlparser.freeParser(l_parser);

--    l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'');
--   l_n := dbms_xmldom.item(l_nl, curn);
--   dbms_xslprocessor.valueOf(l_n,'ORDER/text()',eno);


http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/transformxml/TransformXML.html
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:dplinnane
ID: 17002670
It will be a few days before I get around to trying out solutions, I will let you know how it goes thanks.
0
 

Author Comment

by:dplinnane
ID: 17032504
Hi I decided to go with comment from sathyagiri and neo9414
I took my existing procedure and modified and also add a parsing function to parse a string with an input delimiter. I am having problems getting my parsing function to work  see below /****************** DOES NOT WORK  PLS-00382: expression is of wrong type   *************************
However it does work in when I run test_parse_prc. Any ideas what I am doing wrong?

BEGIN
  -- PINPRICEEVENTMSG := NULL;  Modify the code to initialize this parameter
  PINPRICEEVENTMSG :='<PDATA><HDR>20060328150300~RMS</HDR><EVH>00111~10000001~R~Y~N~20060101120000~~EventName</EVH><EVD>10000001~175~1.01~A~S||10000001~176~1.02~A~S||10000001~177~1.03~A~S</EVD></PDATA>';
  POUTERROR := NULL;
  POUTERRORMSG := NULL;
  POUTTIMESTAMP := NULL;
  POUTSOURCEID := NULL;
  --parse_xml_pkg_1.parse_xml_import_prc ( PINPRICEEVENTMSG, POUTERROR, POUTERRORMSG, POUTTIMESTAMP, POUTSOURCEID );
  parse_xml_pkg_1.test_parse_prc (PINPRICEEVENTMSG);  

END;


/* Formatted on 2006/07/03 12:30 (Formatter Plus v4.8.5) */
CREATE OR REPLACE PACKAGE BODY parse_xml_pkg_1
AS
   l_tbl1             mytable;
   l_tbl2             mytable;
   DATA               VARCHAR2 (2000);
   pinpriceeventmsg   CLOB;
   pouterror          NUMBER;
   pouterrormsg       VARCHAR2 (200);
   pouttimestamp      VARCHAR2 (200);
   poutsourceid       VARCHAR2 (200);

   --TYPE mytable IS TABLE OF VARCHAR2 (200);
   FUNCTION parse_str_fnc (p_str IN VARCHAR2, delim IN VARCHAR2)
      RETURN mytable
   AS
      l_str            VARCHAR2 (2000) DEFAULT p_str || delim;
      l_i              NUMBER;
      l_tbl            mytable         := mytable ();
      l_len_of_delim   NUMBER;
   BEGIN
      l_len_of_delim := LENGTH (delim) - 1;
      DBMS_OUTPUT.put_line ('parse_str_fnc p_str : ' || p_str);

      LOOP
         l_i := INSTR (l_str, delim);
         EXIT WHEN (NVL (l_i, 0) = 0);
         l_tbl.EXTEND;
         l_tbl (l_tbl.COUNT) := SUBSTR (l_str, 1, l_i - 1);
         --Add length of delimiter to account correct value retrieved in substr function.
         l_str := SUBSTR (l_str, l_i + 1 + l_len_of_delim);
      --DBMS_OUTPUT.put_line ('l_i INSTR fnc: ' || l_i||' l_str: '||l_str||' l_len_of_delim '||l_len_of_delim );
      END LOOP;

      RETURN l_tbl;
   END parse_str_fnc;

   PROCEDURE parse_xml_import_prc (
      pinpriceeventmsg   IN       CLOB,
      pouterror          OUT      NUMBER,
      pouterrormsg       OUT      VARCHAR,
      pouttimestamp      OUT      VARCHAR,
      poutsourceid       OUT      VARCHAR
   )
   AS
      xmldata    XMLTYPE;
      DATA       VARCHAR2 (2000);
      evd_data   VARCHAR2 (2000);

      TYPE mytable IS TABLE OF VARCHAR2 (2000);

      l_tbl1     mytable;
      l_tbl2     mytable;
   --p_str     VARCHAR2 (2000);
   BEGIN
      -- convert clob into xmltype instance
      xmldata := XMLTYPE.createxml (pinpriceeventmsg);

      --get "HDR" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/HDR') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/HDR') = 1;

         DBMS_OUTPUT.put_line ('HDR Data: ' || DATA);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pouterror := 1;
            pouterrormsg := 'Header tag (<HDR>) is missing';
            pouttimestamp := NULL;
            poutsourceid := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('NO Data: ' || DATA);
            RETURN;
      END;

      --get "EVH" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;

         DBMS_OUTPUT.put_line ('EVH Data: ' || DATA);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pouterror := 1;
            pouterrormsg := 'Evh tag (<EVH>) is missing';
            pouttimestamp := NULL;
            poutsourceid := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('NO Data: ' || DATA);
            RETURN;
      END;

      --get "EVH" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVD') "REFERENCE"
           INTO evd_data
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVD') = 1;

         DBMS_OUTPUT.put_line ('EVD_DATA =>EVD Data: ' || DATA);
/****************** DOES NOT WORK  PLS-00382: expression is of wrong type   *************************        
             l_tbl1 := parse_str_fnc (data, '||');

         FOR i IN 1 .. l_tbl1.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('Value: ' || l_tbl1 (i));
            l_tbl2 := parse_str_fnc (l_tbl1 (i), '~');

            FOR j IN 1 .. l_tbl2.COUNT
            LOOP
               DBMS_OUTPUT.put_line ('--Subvalue: ' || l_tbl2 (j));
            END LOOP;
         END LOOP;
******************************************************************************************************/
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pouterror := 1;
            pouterrormsg := 'Evh tag (<EVD>) is missing';
            pouttimestamp := NULL;
            poutsourceid := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('EVD_DATA EVD Data: ' || evd_data);
            RETURN;
      END;


   END parse_xml_import_prc;

   PROCEDURE test_parse_prc (p_str IN VARCHAR2)
   AS
      l_tbl1   mytable;
      l_tbl2   mytable;
   BEGIN
      l_tbl1 := parse_str_fnc (p_str, '||');

      FOR i IN 1 .. l_tbl1.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('Value: ' || l_tbl1 (i));
         l_tbl2 := parse_str_fnc (l_tbl1 (i), '~');

         FOR j IN 1 .. l_tbl2.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--Subvalue: ' || l_tbl2 (j));
         END LOOP;
      END LOOP;
   END test_parse_prc;
END parse_xml_pkg_1;
/
0
 

Author Comment

by:dplinnane
ID: 17033663
I think I have solved my problem for the most part.  I'll reopen as a new question if I have further problems.


/* Formatted on 2006/07/03 15:56 (Formatter Plus v4.8.5) */
CREATE OR REPLACE PACKAGE BODY parse_xml_pkg_2
AS
   evd_sub_val_tab       str_tab;
   evd_val_tab           str_tab;
   hdr_val_tab           str_tab;
   evh_val_tab           str_tab;
   DATA                  VARCHAR2 (2000);


   --TYPE mytable IS TABLE OF VARCHAR2 (200);
   FUNCTION parse_str_fnc (p_str IN VARCHAR2, delim IN VARCHAR2)
      RETURN str_tab
   AS
      l_str            VARCHAR2 (2000) DEFAULT p_str || delim;
      l_i              NUMBER;
      l_tbl            str_tab         := str_tab ();
      l_len_of_delim   NUMBER;
   BEGIN
      l_len_of_delim := LENGTH (delim) - 1;

      --DBMS_OUTPUT.put_line ('parse_str_fnc p_str : ' || p_str);
      LOOP
         l_i := INSTR (l_str, delim);
         EXIT WHEN (NVL (l_i, 0) = 0);
         l_tbl.EXTEND;
         l_tbl (l_tbl.COUNT) := SUBSTR (l_str, 1, l_i - 1);
         --Add length of delimiter to account correct value retrieved in substr function.
         l_str := SUBSTR (l_str, l_i + 1 + l_len_of_delim);
      --DBMS_OUTPUT.put_line ('l_i INSTR fnc: ' || l_i||' l_str: '||l_str||' l_len_of_delim '||l_len_of_delim );
      END LOOP;

      RETURN l_tbl;
   END parse_str_fnc;

   PROCEDURE parse_xml_import_prc (
      pin_price_event_msg   IN       CLOB,
      pout_error            OUT      NUMBER,
      pout_error_msg        OUT      VARCHAR,
      pout_time_stamp       OUT      VARCHAR,
      pout_source_id        OUT      VARCHAR
   )
   AS
      xmldata   XMLTYPE;
      DATA      VARCHAR2 (2000);
   BEGIN
      -- convert clob into xmltype instance
      xmldata := XMLTYPE.createxml (pin_price_event_msg);

      --get "HDR" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/HDR') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/HDR') = 1;

         DBMS_OUTPUT.put_line ('HDR Data: ' || DATA);
         hdr_val_tab := parse_str_fnc (DATA, '~');

         FOR j IN 1 .. hdr_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--HDR Value: ' || hdr_val_tab (j));
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pout_error := 1;
            pout_error_msg := 'Header tag (<HDR>) is missing';
            pout_time_stamp := NULL;
            pout_source_id := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('NO Data: ' || DATA);
            RETURN;
      END;

      --get "EVH" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;

         DBMS_OUTPUT.put_line ('EVH Data: ' || DATA);
         evh_val_tab := parse_str_fnc (DATA, '~');

         FOR j IN 1 .. evh_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--EVH Value: ' || evh_val_tab (j));
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pout_error := 1;
            pout_error_msg := 'Evh tag (<EVH>) is missing';
            pout_time_stamp := NULL;
            pout_source_id := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('NO Data: ' || DATA);
            RETURN;
      END;

      --get "EVH" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVD') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVD') = 1;

         DBMS_OUTPUT.put_line ('EVD_DATA =>EVD Data: ' || DATA);
         evd_val_tab := parse_str_fnc (DATA, '||');

         FOR j IN 1 .. evd_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--EVD Value: ' || evd_val_tab (j));
            evd_sub_val_tab := parse_str_fnc (evd_val_tab (j), '~');

            FOR i IN 1 .. evd_sub_val_tab.COUNT
            LOOP
               DBMS_OUTPUT.put_line ('--EVD Subvalue: ' || evd_sub_val_tab (i));
            END LOOP;
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pout_error := 1;
            pout_error_msg := 'Evh tag (<EVD>) is missing';
            pout_time_stamp := NULL;
            pout_source_id := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('EVD_DATA EVD Data: ' || DATA);
            RETURN;
      END;
   END parse_xml_import_prc;
END parse_xml_pkg_2;
/
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question