Solved

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

Posted on 2006-06-27
6
639 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
6 Comments
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 100 total points
Comment Utility
0
 
LVL 9

Accepted Solution

by:
neo9414 earned 300 total points
Comment Utility
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 100 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dplinnane
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now