This discussion is archived
6 Replies Latest reply: May 7, 2012 3:54 PM by MarcoGralike RSS

Adding child element or update existing one in BEFORE INSERT trigger

927388 Newbie
Currently Being Moderated
Hello,

Trying to add a child element or update existing one in BEFORE INSERT trigger:
TRIGGER before_insert_tds_xml
BEFORE INSERT ON tds_xml
FOR EACH ROW
declare 
xmlval XMLType;
BEGIN
 IF (:new.OBJECT_VALUE IS NOT NULL) 
   THEN 
              
       select insertChildXML (
                             :new.object_value,
                             '/TDSTestData',
                             'TestID', 
                             XMLType('<TestID xmlns="http://xmlns.abc.com/tds/TDSSchemaGen2.xsd">147</TestID>') 
                             ,'xmlns="http://xmlns.abc.com/tds/TDSSchemaGen2.xsd"'
                             ) into xmlval from DUAL;
                             
       :new.OBJECT_VALUE := xmlval;                                
 END IF;
END;
However I'm getting:

SQL Error: ORA-22922: nonexistent LOB value
22922. 00000 - "nonexistent LOB value"
*Cause:    The LOB value associated with the input locator does not exist.
The information in the locator does not refer to an existing LOB.
*Action:   Repopulate the locator by issuing a select statement and retry
the operation.





Some elements are stored as CLOBs. Did anyone deal with this issue?




SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 30 20:17:29 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
  • 1. Re: Adding child element or update existing one in BEFORE INSERT trigger
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Yep, I think I encountered this one already once before. Sometimes handling with LOB's the first INSERT only creates the LOB locator and a reservation of the LOB value it will need, that is, it only allocates the memory/disk space but the real value of the LOB is still NULL. During the second action, that is during the UPDATE phase, the NULL value gets replaced with the actual value of the LOB.

    What happens if you temporarily replace the "FOR INSERT" with "FOR UPDATE".

    I created a workaround, because I didn't had an issue with statements in SQL*Plus directly but only with a third party OCI method, via a trigger that had the following section in its UPDATING clause
    ...
    ...
     if updating
     then
       if :new.I_XML_MESSSAGE != empty_clob()
       then
           xml$handling.inbound_xml(:new.I_XML_MSGTYPE,
                                    :new.I_XML_EPMSGSEQNUM,
                                    :new.I_XML_MESSAGE,
                                    'UPDATE');
       end if;
     end if;
    ...
    ...
    "xml$handling.inbound_xml" was the package that handled the actual methods in this trigger. :new.I_XML_MESSAGE was the actual XML data load.

    Edited by: Marco Gralike on May 7, 2012 1:13 PM

    Edited by: Marco Gralike on May 7, 2012 1:14 PM
  • 2. Re: Adding child element or update existing one in BEFORE INSERT trigger
    275191 Newbie
    Currently Being Moderated
    Thank you Marco.

    We never update files once they are loaded, BEFORE UPDATE does not trigger on insert.
    I worked around it with AFTER INSERT:
    TRIGGER after_insert_tds_xml
    AFTER INSERT or UPDATE ON tds_xml
    declare
    CURSOR getNewFiles IS
      SELECT OBJECT_VALUE XML, rowid
      FROM TDS_XML
      where not XMLExists('declare default element namespace "http://xmlns.abc.com/tds/TDSSchemaGen2.xsd"; /TDSTestData/TestID' PASSING OBJECT_VALUE);
    begin
    
    for f in getNewFiles loop
     update TDS_XML
           set object_value = insertChildXML
                              (
                                 object_value,
                                 '/TDSTestData',
                                 'TestID', 
                                 XMLType('<TestID xmlns="http://xmlns.abc.com/tds/TDSSchemaGen2.xsd">'||to_Char(TDS_XML_TEST_ID.NEXTVAL)||'</TestID>') 
                                 ,'xmlns="http://xmlns.abc.com/tds/TDSSchemaGen2.xsd"'
                                 )
     WHERE TDS_XML.rowid = f.rowid; 
     
     end loop;
    end;
    The other approach I'm thinking to update XML file itself before loading with API to assign IDs. It might also help to workaround issue with Virtual Columns I've posted before.
  • 3. Re: Adding child element or update existing one in BEFORE INSERT trigger
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    We never update files once they are loaded, BEFORE UPDATE does not trigger on insert.
    I think you didn't get it completely. Sometimes "an insert" of a LOB exists of 2 stages:

    - insert: assign lob locator and EMPTY allocation of memory / disk stack
    - update: fill the empty allocation / reserved space with the actual data

    The insert trigger will fail due to trying to update an empty LOB (which is at least not valid XML/wellformed)
    The update will actually do the real "insert". In that case, modification of the XML will succeed.This is also why the trigger will fire twice during "one" insert.
  • 4. Re: Adding child element or update existing one in BEFORE INSERT trigger
    275191 Newbie
    Currently Being Moderated
    Thanks, Marco.
    Tried but BEFORE UPDATE does not fire when I insert XML file. How do I make it to go on 2 stage?
    Schema defines files as structured with some branches as stored as CLOBs
  • 5. Re: Adding child element or update existing one in BEFORE INSERT trigger
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    IN MY CASE, the third party client insert fired in 2 phases (insert+update), but SQL*Plus fired (as initially expected by me) during the insert. You will have to give more info on how your case is build.

    With the test "empty_clob()", you can check if there is actually something "to do". If there is no data, you can not update the data (yet)

    Edited by: Marco Gralike on May 8, 2012 12:55 AM
  • 6. Re: Adding child element or update existing one in BEFORE INSERT trigger
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    BTW because this dualistic behavior you can expect that my code become a bit more interesting. I had to support this third party software, but also, in case a DBA or Developer made manual inserts/updates/deletes, had to take those cases into account of my code as well.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points