Length Restriction of Textareas in APEX 4.2

Are you hitting the now enforced length restriction of Textareas in your existing applications after upgrading to APEX 4.2? Then you should read Joels blog posting APEX 4.2 and Textareas for additional information and a fix.

If you want to make your applications future prove then I would suggest that you run the following query in SQL Workshop. It will return you all the textareas which are bound to a database column and where the length specified for the textarea doesn’t match with the database column length definition.

select i.application_id,
       i.page_id,
       i.item_name,
       i.item_element_max_length,
       c.data_type,
       case
         when c.data_type = 'CLOB' then 32767
         else c.data_length
       end as data_length
  from apex_application_page_items i,
       apex_application_page_proc p,
       user_tab_columns c
 where i.display_as_code   = 'NATIVE_TEXTAREA'
   and i.item_source_type  = 'Database Column'
   and p.application_id    = i.application_id
   and p.page_id           = i.page_id
   and p.process_type_code = 'DML_FETCH_ROW'
   and c.table_name        = to_char(regexp_replace(p.process_source, '^[^:]*:([^:]*).*', '\1'))
   and c.column_name       = i.item_source
   and (  ( c.data_type = 'CLOB' and i.item_element_max_length < 30000 )
       or ( c.data_type = 'VARCHAR2' and c.data_length > i.item_element_max_length )
       )
   order by 1, 2, 3;

Note: This query assumes that your workspace is only assigned to one database schema! If you have multiple schemas assigned, you have to change the “Schema” select list in SQL Commands and run the statement for each schema you find there.

One thought on “Length Restriction of Textareas in APEX 4.2

  1. Hi. I’m currently running Apex 4.1 on a Windows 7 machine with an 11g xe database. Is it safe to upgrade to 4.2? Is it straightforward, or do I need to hire an Oracle DBA to assist me?

Comments are closed.