5 Replies Latest reply: Oct 2, 2013 11:32 AM by user8704911 RSS

    [11g] ORA-22993 when extract large xmltype values in BIU trigger as CLOB into another table

    user8704911

      [Let me kindly ask for some comment for this issue.

      Can someone else reproduce it with the test listed below?

      Is my code invalid/wrong?

      Or what is the problem here?

      - thanks

      Frank

      ]

       

      Hi,

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      CORE    11.2.0.3.0      Production

      TNS for Linux: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      In Before Insert Or Update trigger of a table i invoke some PL/SQL package function to check for the size of some xmltype column (passing it as IN OUT NOCOPY).

      The PL/SQL functions retrieves the xmltype.GetClobVal(), checks it's size, and when this is beyond a limit it tries to insert the data into another table (GTT - global temporary table).

      Finally it changes the value of the xmltype column value to some shorter XML (just referencing to the corresponding GTT record).

      But the SQL insert operation into the GTT hits ORA-22993: specified input amount is greater than actual source amount.

      I can't find out why this happens. In the internet i only find references about file import into lob/clob.

       

      Did anybody have the same problem?

      I know you prefer to have some sample program to demonstrate the problem - i'll try to post one, but it will take some time.

      But maybe someone can help me based on information above already.

       

      - many thanks!

       

      best regards,

      Frank

        • 1. Re: [11g] extract large xmltype values in BIU trigger as CLOB into another table
          user8704911

          Here is a demo program - pls. see below.

          It's about a dbms_output.put_line using some dbms_lob.substr(), that causes the problem

          - but why?

          =================================================

          drop table test_xml

          /

           

           

          drop table test_clob

          /

           

           

          create table Test_Xml

          (

                 data xmltype

          )

          /

           

           

          create table Test_Clob

          (

                 data clob

          )

          /

           

           

          create or replace package test_pkg

          as

                 procedure check_xml_data(

                           p_data_xml IN OUT NOCOPY xmltype);

                      

          end;

          /

           

           

          create or replace package body test_pkg

          as

                 procedure insert_record(

                           p_data_clob IN clob)

                 is

                 begin

                    -- THIS LINE NEXT (dbms_output.put_line()) CAUSES THE ORA-22993

                    -- WHEN disabled, the insert works

                    dbms_output.put_line('Data = ' || dbms_lob.substr(p_data_clob, 50));

                   

                    insert into test_clob(data) values (p_data_clob);

                 end;

               

                 procedure check_xml_data(

                           p_data_xml IN OUT NOCOPY xmltype)

                 is

                     v_data_clob clob;

                 begin

                     dbms_output.put_line('check_xml_data(): enter');

                     IF p_data_xml IS NULL

                     THEN

                        dbms_output.put_line('check_xml_data(): exit (p_data_xml IS NULL)');

                        RETURN; -- early exit

                     END IF;

                     v_data_clob := p_data_xml.GetClobVal();

                     dbms_output.put_line('  v_data_clob.len = ' || dbms_lob.getLength(v_data_clob));   

                    

                     insert_record(v_data_clob);

          --           insert into test_clob(data) values (v_data_clob);

                    

                     dbms_output.put_line('check_xml_data(): exit');      

                 EXCEPTION

                 WHEN OTHERS THEN

                      dbms_output.put_line('check_xml_data)(): exception: ' || SQLERRM);

                       RAISE;

                 end;

          end;

          /

           

          CREATE OR REPLACE TRIGGER Test_Xml_tr_biu  BEFORE INSERT OR UPDATE ON Test_xml FOR EACH ROW

          DECLARE

          BEGIN

               dbms_output.put_line('Test_Xml_tr_biu(): enter');

              

               test_pkg.check_xml_data(:new.data);

          EXCEPTION

          WHEN OTHERS THEN

              dbms_output.put_line('Test_Tab_tr_biu: exception: ' || SQLERRM);

              RAISE;

          END;

          /

           

           

           

           

          delete from test_xml;

          delete from test_clob;

          commit;

           

           

          insert into test_xml (data) values (xmltype('<root><node><level1><level2><level3>the quick brown fox jumps over the lazy dog</level3></level2></level1></node></root>'));

           

          select dbms_lob.substr(data,4000,1) from test_clob;

          • 2. Re: [11g] extract large xmltype values in BIU trigger as CLOB into another table
            user8704911

            Please gimme some feedback for this issue.

            E.g. repeat the test script (see above) on another system.

            And tell me, if my code is wrong/illegal.

            Or what is the reason for the problem?

            Btw, i don't hit this problem, when i make test_pkg.insert_record() public and invoke it directly(?!)

            - many thanks!

            best regards,

            Frank

            • 3. Re: [11g] extract large xmltype values in BIU trigger as CLOB into another table
              user8704911

              Can anybody please confirm, if the side-effect of dbms_lob.substr() is a bug or a feature?

              (pls. see demo script above)

              - thanks

              \Frank

              • 4. Re: [11g] extract large xmltype values in BIU trigger as CLOB into another table
                odie_63

                I've reproduced the issue.

                This must be a bug.

                • 5. Re: [11g] extract large xmltype values in BIU trigger as CLOB into another table
                  user8704911

                  Hi Odie,

                  thanks for confirmation.

                  I can avoid to use dbms_lob.substr() - so it doesn't show up for me.

                  In parallel I'll ask our DB team to check for a support request to oracle.

                  - many thanks!

                  best regards,
                  Frank