3 Replies Latest reply: Apr 19, 2013 11:41 AM by Hari_639 RSS

    CLOB error in APEX 4.2

    SaraB
      Hi

      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:
      DECLARE
         v_source CLOB := empty_clob;
      BEGIN
        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);
      END;
      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.

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

          Try
               IF APEX_COLLECTION.COLLECTION_EXISTS( p_collection_name => 'CLOB_CONTENT' ) THEN
                         APEX_COLLECTION.TRUNCATE_COLLECTION( p_collection_name => 'CLOB_CONTENT' );
               ELSE
                         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}

          Regards,
          Hari
          • 2. Re: CLOB error in APEX 4.2
            SaraB
            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
            Sara

            PS. Will provide points when I've check solution in dev environment.... :-)
            • 3. Re: CLOB error in APEX 4.2
              Hari_639
              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

              Regards,
              Hari