3 Replies Latest reply on Apr 19, 2013 4:41 PM by Hari_639

    CLOB error in APEX 4.2


      We have a number of CLOB columns in our application that are edited using CKEditor. To do this we use apex.ajax.clob as described by Carl many moons ago and detailed in this post Re: 4000 chars from a textarea into a clob column

      When we run the page we sometimes get an error "ORA-00001: unique constraint (APEX_040200.WWV_FLOW_COLLECTIONS_UK) violated". The full error is:
      •is_internal_error: false
      •ora_sqlcode: -1
      •ora_sqlerrm: ORA-00001: unique constraint (APEX_040200.WWV_FLOW_COLLECTIONS_UK) violated
      •component.type: APEX_APPLICATION_PAGE_PROCESS
      •component.id: 11292445496835310
      •component.name: Set CLOB Data
      •error_backtrace: ORA-06512: at "APEX_040200.WWV_FLOW_COLLECTION", line 512
      ORA-06512: at line 8
      ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926
      ORA-06512: at "SYS.WWV_DBMS_SQL", line 973
      ORA-06512: at "SYS.WWV_DBMS_SQL", line 999
      ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 830
      ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS", line 138
      Which looks like there is a problem with the collection in the page process called "Set CLOB Data". However, the code for this process is:
         v_source CLOB := empty_clob;
        SELECT clob_column
        INTO   v_source
        FROM   our_table
        WHERE id = :P16_ID;
        apex_collection.create_or_truncate_collection(p_collection_name => 'CLOB_CONTENT');
        apex_collection.add_member(p_collection_name => 'CLOB_CONTENT',p_clob001 => v_source);
      I'm not sure how this code could produce that error?

      On our development environment we get the error intermittently and mostly in IE9 although I have had it in Chrome, but never in FireFox. However, on a customer's environment they get this error every time they try to access the page in IE9. Although they don't get it in FF, their company policy is to use IE so it's a big problem for them.

      We have only ever had this error since upgrading to APEX 4.2

      Does anyone know what could be causing this problem? Or have a workaround? Or is there a different way of handling CLOBs in APEX 4.2 that we could use?

      Many thanks for your time.

        • 1. Re: CLOB error in APEX 4.2
          Hello Sara,

               IF APEX_COLLECTION.COLLECTION_EXISTS( p_collection_name => 'CLOB_CONTENT' ) THEN
                         APEX_COLLECTION.TRUNCATE_COLLECTION( p_collection_name => 'CLOB_CONTENT' );
                         APEX_COLLECTION.CREATE_COLLECTION( p_collection_name => 'CLOB_CONTENT' );
               END IF;
          instead of
          apex_collection.create_or_truncate_collection(p_collection_name => 'CLOB_CONTENT');
          See {message:id=10524978}

          • 2. Re: CLOB error in APEX 4.2
            Hi Hari

            I have to admit I didn't think this would work. But I've just tried it quickly on our customer site and it did! Thank you. I need to try it on our dev environment to be sure, but it does appear to work. How strange.

            Can Oracle provide an answer to why this would work and the other way wouldn't? Is create_or_truncate no longer available from 4.2?

            Many thanks

            PS. Will provide points when I've check solution in dev environment.... :-)
            • 3. Re: CLOB error in APEX 4.2
              Hello Sara,

              Yes, some times APEX behaves weirdly, we had this issue when we use create_or_truncate procedure in on-load page process and if we call this page as pop-up using jQuery dialog. If we run page normally, then this issue was not coming. However handling create and truncate explicitly solved the problem. Strange, but that is how it is!
              Is create_or_truncate no longer available from 4.2?
              No, see this