APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

How to find duplicate rows?

Von Tobias Arnhold → 6.08.2011

Basically with a single table you do like this:


SELECT COLUMN_A, COLUMN_B, COUNT(*)
FROM MY_TABLE
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*)>1;

If you have a special select and you need to find duplicate rows then do like this:


select count(*), cust_no
(
select
c.cust_no, c.cust_name, s.order_date, s.art_no, s.art_amount
from table_sales s, table_customer c
where c.cust_no = s.cust_no
  and s.country_no = 49
  and s.order_date = trunc(sysdate-1)
group by
order by 2,3
) 
group by cust_no
having count(*) > 1

APEX examples

Check ob Flash installiert ist

Von Tobias Arnhold → 6.06.2011

Ich habe vor ein paar Tagen ne interessante Frage gestellt bekommen:
"Wie überprüft man in Apex, ob ein Flash Plugin installiert ist?"
Antwort:
Einen Standard-Check gibt es nicht.

Lösungsansatz:
Probiert folgende Erweiterung JavaScript Flash Detection Library (Flash Detect)

Die Datei "flash_detect_min.js" auf eurem Server laden und in den APEX Page Header einbetten.

Auf eurer Startseite erstellt ihr ein Hidden Item: P1_FLASH

Auf der gleichen Seite erstellt ihre eine Dynamic Action die nach dem "Page Load" startet.

Erste TRUE Action: Execute Javascript Code
if(!FlashDetect.installed){
$s('P1_FLASH','TRUE');
}else{
$s('P1_FLASH','FALSE');
}

Zweite TRUE Action: Execute PL/SQL Code
Code: null;
Page Items to Submit: P1_FLASH

Nun könnt ihr auf jeder Seite für die betroffenen Regionen Bedingungen integrieren:
:P1_FLASH = 'TRUE'

Das Ganze könnte auch ein schönes kleines Plug-in sein! :D

XML

Two ways using string to table in APEX selects

Von Tobias Arnhold → 5.03.2011

First way
This example uses "REGULAR expressions" and the "connect by" clause. Perfect when you select on columns including lists.
Forum entry: http://forums.oracle.com/forums/thread.jspa?messageID=9494074


WITH TABLE1
       AS (SELECT 1 my_id, '8092:7054:9237:4232:3333:4023:6781' my_list FROM DUAL
           UNION
           SELECT 2, '8765:2231:2242:3412:3453' FROM DUAL
           UNION
           SELECT 3, '2121' FROM DUAL
           UNION
           SELECT 4, '6565:9121' FROM DUAL)
  SELECT my_id,REGEXP_SUBSTR ( my_list, '([^:]+)', 1, lvl)
    FROM TABLE1,
         (SELECT LEVEL lvl
            FROM (SELECT MAX (LENGTH (REGEXP_REPLACE ( my_list || ':', '[^:]'))) mx
                    FROM TABLE1)
          CONNECT BY LEVEL <= mx + 1)
   WHERE lvl - 1 <= LENGTH (REGEXP_REPLACE ( my_list || ':', '[^:]'))
     AND REGEXP_SUBSTR ( my_list, '([^:]+)', 1, lvl) IS NOT NULL
   ORDER BY my_id,lvl;

Second way
This example is based on a nice XML solution. Only needs two lines of code. Easy to use in combination with variables.
Forum entry; http://forums.oracle.com/forums/thread.jspa?threadID=2184251&tstart=0&messageID=9406487


:F_STRING := '8092:7054:9237:4232:3333:4023:6781'

select upper(extractvalue(column_value,'e'))
from table(xmlsequence(xmltype('' || replace(:F_STRING,':','') || '').extract('e/*')));

APEX issues

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