developers

    Forum Stats

  • 3,873,750 Users
  • 2,266,637 Discussions
  • 7,911,626 Comments

Discussions

CLOB error in APEX 4.2

SaraB
SaraB Member Posts: 244 Blue Ribbon
edited Apr 19, 2013 12:41PM in APEX Discussions
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 9569863

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
kempiak

Best Answer

  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy
    Answer ✓
    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
    kempiak

Answers

  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy
    Answer ✓
    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
    kempiak
  • SaraB
    SaraB Member Posts: 244 Blue Ribbon
    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.... :-)
  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy
    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
This discussion has been closed.
developers