APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

ORA-00001, WWV_FLOW_DATA_IDX1, ERR-1029

Von Tobias Arnhold → 4.13.2011

Troubleshooting the following APEX error:


ORA-00001: unique constraint (APEX_040000.WWV_FLOW_DATA_IDX1) violated
ERR-1029 Speichern von Sessioninformationen nicht möglich. session=3642920897355064 item=9815904291046405

This issue often comes in combination with iFrames, item computations, different languages or collections.

First you need to find which item is effected:

Inside APEX Application Builder > Application xxx > Utilities > Application Express Views > APEX_APPLICATION_PAGE_ITEMS
Columns: Select all columns
Filter: Item_ID = 9815904291046405
Copy the generated select and execute it inside your SQL Developer:


select WORKSPACE,APPLICATION_ID,APPLICATION_NAME,PAGE_ID,PAGE_NAME,ITEM_NAME,DISPLAY_AS,DISPLAY_AS_CODE,ITEM_DATA_TYPE,IS_REQUIRED,STANDARD_VALIDATIONS,DISPLAY_SEQUENCE,REGION,REGION_ID,SOURCE_USED,ITEM_DEFAULT,ITEM_DEFAULT_TYPE,LABEL,PRE_ELEMENT_TEXT,POST_ELEMENT_TEXT,FORMAT_MASK,ITEM_LABEL_TEMPLATE,ITEM_LABEL_TEMPLATE_ID,ITEM_SOURCE,ITEM_SOURCE_TYPE,ENCRYPT_SESSION_STATE,SOURCE_POST_COMPUTATION,READ_ONLY_CONDITION_TYPE,READ_ONLY_CONDITION_EXP1,READ_ONLY_CONDITION_EXP2,READ_ONLY_DISPLAY_ATTR,LOV_NAMED_LOV,LOV_DEFINITION,LOV_DISPLAY_EXTRA,LOV_DISPLAY_NULL,LOV_NULL_TEXT,LOV_NULL_VALUE,LOV_QUERY_RESULT_TRANSLATED,LOV_CASCADE_PARENT_ITEMS,AJAX_ITEMS_TO_SUBMIT,AJAX_OPTIMIZE_REFRESH,ITEM_ELEMENT_WIDTH,ITEM_ELEMENT_MAX_LENGTH,ITEM_ELEMENT_HEIGHT,HTML_TABLE_CELL_ATTR_LABEL,HTML_TABLE_CELL_ATTR_ELEMENT,HTML_FORM_ELEMENT_ATTRIBUTES,FORM_ELEMENT_OPTION_ATTRIBUTES,ITEM_BUTTON_IMAGE,ITEM_BUTTON_IMAGE_ATTRIBUTES,BEGINS_ON_NEW_ROW,BEGINS_ON_NEW_CELL,COLUMN_SPAN,ROW_SPAN,LABEL_ALIGNMENT,ITEM_ALIGNMENT,SHOW_QUICK_PICKS,QUICK_PICK_LINK_ATTR,QUICK_PICK_LABEL_01,QUICK_PICK_VALUE_01,QUICK_PICK_LABEL_02,QUICK_PICK_VALUE_02,QUICK_PICK_LABEL_03,QUICK_PICK_VALUE_03,QUICK_PICK_LABEL_04,QUICK_PICK_VALUE_04,QUICK_PICK_LABEL_05,QUICK_PICK_VALUE_05,QUICK_PICK_LABEL_06,QUICK_PICK_VALUE_06,QUICK_PICK_LABEL_07,QUICK_PICK_VALUE_07,QUICK_PICK_LABEL_08,QUICK_PICK_VALUE_08,QUICK_PICK_LABEL_09,QUICK_PICK_VALUE_09,QUICK_PICK_LABEL_10,QUICK_PICK_VALUE_10,ATTRIBUTE_01,ATTRIBUTE_02,ATTRIBUTE_03,ATTRIBUTE_04,ATTRIBUTE_05,ATTRIBUTE_06,ATTRIBUTE_07,ATTRIBUTE_08,ATTRIBUTE_09,ATTRIBUTE_10,CONDITION_TYPE,CONDITION_EXPRESSION1,CONDITION_EXPRESSION2,MAINTAIN_SESSION_STATE,ITEM_PROTECTION_LEVEL,ESCAPE_ON_HTTP_OUTPUT,AUTHORIZATION_SCHEME,AUTHORIZATION_SCHEME_ID,BUILD_OPTION,BUILD_OPTION_ID,ITEM_HELP_TEXT,LAST_UPDATED_BY,LAST_UPDATED_ON,COMPONENT_COMMENT,ITEM_ID,COMPONENT_SIGNATURE
from APEX_APPLICATION_PAGE_ITEMS
where ITEM_ID = 9815904291046405

Investigate the effected item... You may find strange connections to other APEX components?

Forum entry: http://forums.oracle.com/forums/thread.jspa?messageID=9365663

APEX issues

Handling the 32k problem in APEX charts

Von Tobias Arnhold → 4.03.2011

How to handle more then 32k (32768) characters inside your APEX charts: AnyChart gantt with custom xml and more then 32k

Workaround:
- Create your own chart

Info:
- AnyChart documentation: Set XML As String From TextArea

HTML Header:http://www.blogger.com/img/blank.gif


<script src="#APP_IMAGES#anychart_intkit.js" language="javascript"></script>

PL/SQL Region:


/* ... */
  htp.p('<textarea cols="65" rows="17" id="rowData" style="display:none;">');
 
  /* Write CLOB through a package function */
  P_CLOB := MY_PACKAGE.F_APEX_GANTT_COMPLETE ( P_USER );
 
  dbms_lob.open(P_CLOB, dbms_lob.lob_readonly); 
 
  P_LENGTH := dbms_lob.getlength (P_CLOB);
 
  while P_LENGTH > 0 loop
  
     /* Read 32000 characters from CLOB */
     dbms_lob.read(P_CLOB, P_CLOB_BUFFER, P_POS, P_SET); 
          
     /* Write code in APEX */
     htp.prn(P_SET);
     
     /* Increase Counter */
     P_POS := P_POS + P_CLOB_BUFFER;
     P_LOOP := P_LOOP + 1;
     
     /* Resize CLOB length */
     P_LENGTH := P_LENGTH - P_CLOB_BUFFER;
     
  end loop;
 
  dbms_lob.close(P_CLOB); 
 
  htp.p('</textarea>');
  htp.p('<div id="chartDiv"></div>');
 
  htp.p(
    '<script type="text/javascript" language="javascript">' || chr(10) ||
    '/* Set default swf path */' || chr(10) ||
     'AnyChart.swfFile = ''/i/flashchart/anygantt_4/swf/AnyGantt.swf'';' || chr(10) ||
    '/* Create new gantt chart */' || chr(10) ||
     'var chart = new AnyChart();' || chr(10) ||
     'chart.width="2500";' || chr(10) ||
     'chart.height="1400";' || chr(10) ||
    '/* Get string data from text area */' || chr(10) ||
     'var data = document.getElementById(''rowData'').value.toString();' || chr(10) ||
    '/* Set data */' || chr(10) ||
     'chart.setData(data);' || chr(10) ||
    '/* Write chart to "chart" div */' || chr(10) ||
     'chart.write("chartDiv");' || chr(10) ||
    '</script>');
/* ... */

I created a region plug-in based on the above code to handle the huge charts! :)

APEX Plugin

Remove delete icon in SkillBuilders Super LOV

Von Tobias Arnhold → 3.25.2011

Do you like the Super LOV as much as I do? It is just a great plug-in.

Some customers didn't like the delete button to erase the selected value. I could not find any setting to disable this icon.

Workaround:
Add a dynamic action "after page load" which executes some javascript with this little piece of code:
$('.superlov-modal-delete').remove();

That's it.

APEX examples

Customize Interactive report header graphic

Von Tobias Arnhold → 3.22.2011

Create a new image called report_bg_red.gif:

Add this inside your page header to exchange the IRR default image:


.apexir_WORKSHEET_DATA th {
    background: url("/i/my_images/ir_styles/report_bg_red.gif") repeat-x scroll 0 0 #AAAAAA;
}

Change "/i/my_images/" with your image directory.

If you want to customize even more CSS watch this blog post of Shakeeb Rahman: http://apex.shak.us/post/1145946801/fully-control-your-interactive-reports-toolbar

APEX examples

SQL: Count character in column or string

Von Tobias Arnhold → 3.21.2011

Use this sql select to count a special character inside a column or string:

Column selection:


select col, length(col)-length(replace(col,',','')) 
from
 (select 'col1,col2,col3,col4' as col from dual);

COL                 LENGTH(COL)-LENGTH(REPLACE(COL,',',''))
------------------- ---------------------------------------
col1,col2,col3,col4                                       3

String selection


BEGIN
  :P1_STRING := 'col1,col2,col3,col4';

  select length(:P1_STRING)-length(replace(:P1_STRING,',','')) into :P1_CHAR_CNT from dual;
END;

Watch this thread on Ask Tom: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:55423584511523

APEX issues

APEX bug with APEX_ITEM and POPUPKEY_FROM_LOV

Von Tobias Arnhold → 3.17.2011

Try to set two POPUPKEY_FROM_LOVs beside each other with the column ids: 2,3


select
APEX_ITEM.CHECKBOX (1,ac.ac_id) as ac_id,
APEX_ITEM.POPUPKEY_FROM_LOV(2,ac.application_key,'lov_apps',50) as application,
APEX_ITEM.POPUPKEY_FROM_LOV(3,ac.system_key,'lov_systems',50)   as system
from app_conf ac;

When you run the page then APEX automatically creates a hidden field for the return value of the "popup lov".

The output looks like that:
column "application": Return column id = 2, Display column id = 3
column "system": Return column id = 3, Display column id = 4

If you set a value for column "system" it saves the "return value" in the "display value"-field of column "application".

Workaround: Use at least two numbers in between to avoid this behavior:


select
APEX_ITEM.CHECKBOX (1,ac.ac_id) as ac_id,
APEX_ITEM.POPUPKEY_FROM_LOV(2,ac.application_key,'lov_apps',50) as application,
APEX_ITEM.POPUPKEY_FROM_LOV(4,ac.system_key,'lov_systems',50)   as system
from app_conf ac;

APEX Plugin

New plug-in "Add favorite"

Von Tobias Arnhold → 3.16.2011

Have you ever looked for a solution to create an "add favorite"-button which let the user bookmark your website. You probably found out that each browser uses an own solution for it. I put it all into a nice little dynamic action plug-in. It supports to load only the "add favorite"-function, bookmark the current APEX page, bookmark any other page you define through the plug-in.

Supported browsers: MS Internet Explorer, Firefox, Opera

Demo: http://apex.oracle.com/pls/otn/f?p=65560:ADD_FAVORITE

I'm still working on the demo and will release all new plug-ins in the next couple of weeks. For all who want those plug-ins before the release then refer to this post: How can we help Japan?

Tobias