1 / 48

Steal the Show with ApEx

Steal the Show with ApEx. Oracle Open World, November 13, 2007 Bill Holtzman National Air Traffic Controllers Association. NATCA. National Air Traffic Controllers Association 15,000 members 400 locations Employees of the Federal Aviation Administration. Grievance.

connor-merrill
Download Presentation

Steal the Show with ApEx

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Steal the Show with ApEx Oracle Open World, November 13, 2007 Bill Holtzman National Air Traffic Controllers Association

  2. NATCA • National Air Traffic Controllers Association • 15,000 members • 400 locations • Employees of the Federal Aviation Administration Steal the Show with ApEx

  3. Grievance • A complaint against the employer by an employee or the union • Over 200,000 active grievances • Requirements akin to legal case Steal the Show with ApEx

  4. G.A.T.S. Steal the Show with ApEx

  5. Wizards Steal the Show with ApEx

  6. Graphical query builder Steal the Show with ApEx

  7. SQL report: Region Source Steal the Show with ApEx

  8. SQL report: Attributes Steal the Show with ApEx

  9. Automated report link Steal the Show with ApEx

  10. Manual report link: link||text Manual link enables concatenation with plain text <a href="f?p=104:8:10234501378364652310:::8: P8_DUP_GRID,P8_RETURN_PAGE,P8_ARTICLE: 5880,32,0">06-ZDC-34</a><br><span style="font-size:8pt">123456</span> Steal the Show with ApEx

  11. Manual link: Javascript Manual link with Javascript enables custom pop-ups '<a href="javascript:myPopUp(''f?p=&APP_ID.:9:' || :APP_SESSION || '::::P9_GRID:' || g.GRID || ''')">' || g.topic || '</a>' “Grievance Regarding<br>(View/Print)" At runtime, this becomes: <a href="javascript:myPopUp('f?p=104:9:11001668615862681378 ::::P9_GRID:5581')">Article 34 Working Hours</a> Steal the Show with ApEx

  12. Use of conditional || Decode and case enable conditional || with images, text select decode(g.status_id, 1, decode(g.date_sub, null, trunc(g.u_action) - trunc(sysdate) || '&nbsp;' || case when (g.u_action - sysdate) > 7 then '<img src="#FLOW_IMAGES#greenN.gif">' when (g.u_action - sysdate) > 3 then '<img src="#FLOW_IMAGES#yellowN.gif">' when (g.u_action - sysdate) > 0 then '<img src="#FLOW_IMAGES#redN.gif">' else '<img src="#FLOW_IMAGES#past.gif" border="0">' end, to_char(g.date_sub, 'MM/DD/YY')), 'Closed') "DATE_SUB" from grievance g Steal the Show with ApEx

  13. SQL generated by PLSQL • SQL Report Region could not enable optional sorting of composite columns • Use of PLSQL-generated SQL enables finer control over the report source query, enhancing performance Steal the Show with ApEx

  14. Converting a report to PLSQL declare p_sql varchar2(32767); begin p_sql := q'! select grid from grievance !'; return p_sql; end; Note: 10g quoting syntax Steal the Show with ApEx

  15. Adding conditions declare p_sql varchar2(32767); begin p_sql := q'! select g.GRID, !'; p_sql := p_sql || q'! '<a href="javascript$myPopUp(''f?p=&APP_ID.$9$' || $APP_SESSION || '$$$$P9_GRID$' || g.GRID || ''')">' || g.topic || '</a>' || gr_groupid(g.grid) "Topic“ !'; p_sql := p_sql || q'! from GRIEVANCE g, gr_status_lookup p, gr_bu b where g.gr_status = 3 and g.status_id = p.id !'; if :P35_FAANUM is not null then p_sql := p_sql || q'! and lower(g.faanum) like '%' || lower($P35_FAANUM) || '%' !'; end if; return replace(p_sql,'$',':'); end; Steal the Show with ApEx

  16. Composite sorting declare p_sql varchar2(32767); begin p_sql := q'! select g.GRID, !'; p_sql := p_sql || q'! '<a href="javascript$myPopUp(''f?p=&APP_ID.$9$' || $APP_SESSION || '$$$$P9_GRID$' || g.GRID || ''')">' || g.topic || '</a>' || gr_groupid(g.grid) "Topic" !'; p_sql := p_sql || q'! from grievance g, gr_status_lookup p, gr_bu b where g.gr_status = 3 and g.status_id = p.id and g.bu_id = b.id (+) !'; case when :P35_SORT = 1 then p_sql := p_sql || q'! order by trunc(g.reply_by_3), trunc(g.date_sub_3) nulls last !'; when :P35_SORT = 2 then p_sql := p_sql || q'! order by trunc(g.date_sub_3), trunc(g.u_action_3) nulls last !'; else null; end case; return replace(p_sql,'$',':'); end; Steal the Show with ApEx

  17. Checkboxes PLSQL-generated SQL Page process Steal the Show with ApEx

  18. Check-all checkbox From Sergio Leunissen’s Blog Steal the Show with ApEx

  19. Grievance listing Steal the Show with ApEx

  20. Grievance listing Number of rows (item) Order by Filters = where clauses Check all check box Composite report column Manual page link || text Manual javascript pop-up link || text Large clickable area Steal the Show with ApEx

  21. Javascript on a Select List Page Attributes Page Item Steal the Show with ApEx

  22. Database-driven Javascript declare p_java varchar2(4000); cursor c1 is select bu_id, bplate from gr_bu; begin p_java := 'function insertBP(p_region_id) { var p_bu_id = document.getElementById("P8_BU_ID").value;'; for a1 in c1 loop p_java := p_java || chr(10) || 'if (p_bu_id == ' || a1.bu_id || ')' || chr(10) || 'document.getElementById("P8_BPLATE").value = "' || a1.bplate || '";'; end loop; p_java := p_java || chr(10) || '}'; :F168_BPLATE_JAVA := p_java; end; Steal the Show with ApEx

  23. Javascript result function insertBP(p_region_id) { var p_bu_id = document.getElementById("P8_BU_ID").value; if (p_bu_id == 12) document.getElementById("P8_BPLATE").value = "This grievance is filed pursuant to the Interim agreements and 5 USC 7103 (a) (9). The Agency's actions constitute a violation of the Interim agreements between NATCA and the FAA, 5 USC Chapter 71, and all applicable laws, rules, regulations, and past practice. NOTE: Under protest, and as ordered by FAA management, this grievance is filed in accordance with the Imposed Working Rules (IWR)."; if (p_bu_id == 13) document.getElementById("P8_BPLATE").value = "This grievance is filed pursuant to the Interim agreements and 5 USC 7103 (a) (9). "; } Steal the Show with ApEx

  24. Custom tables: file storage Users upload and download documents associated with each grievance. The process is analogous to a legal case. Steal the Show with ApEx

  25. Upload/download tables The custom tables are tied to individual grievances by the primary key GRID. Steal the Show with ApEx

  26. Upload process Steal the Show with ApEx

  27. Upload: File size validation A validation restricts the size of uploads. Steal the Show with ApEx

  28. Upload: File name validation Javascript restricts the length of the file name. ApEx will not accept more than 78 characters. Steal the Show with ApEx

  29. Download report Steal the Show with ApEx

  30. Download link Don’t forget! SQL> grant execute on download_my_file to public Steal the Show with ApEx

  31. Session state protection http://www.abc.net/pls/htmldb/f?p=168:34:470931357178041727::NO:::&cs=3A70EA7DD614FA61411D4DCACB75E481C Steal the Show with ApEx

  32. URL with checksum Steal the Show with ApEx

  33. Checksums in manual links Original SQL: '<a href="javascript:myPopUp(''f?p=&APP_ID.:9:' || :APP_SESSION || '::::P9_GRID:' || g.GRID || ''')">' || g.topic || '</a>' "Topic" With session state protection: '<a href="javascript:myPopUp(''' || htmldb_util.prepare_URL('f?p=&APP_ID.:9:' || :APP_SESSION || '::::P9_GRID:' || g.GRID) || ''')">' || g.topic || '</a>' "Topic" Steal the Show with ApEx

  34. Checksum in PLSQL region Steal the Show with ApEx

  35. Session state violation Tampering with values in the URL produces this error message. Steal the Show with ApEx

  36. Security through branching Steal the Show with ApEx

  37. Automatic row processing Automatic row processing includes optimistic locking. But more advanced apps use manual processing. Steal the Show with ApEx

  38. Manual row processing :P8_CHECKSUM is calculated when the page is rendered. If it changes, the update does not execute. for c1 in (select * from grievance where grid = :P8_GRID) loop current_state := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => c1.FAANUM||c1.GRIEVANT||c1.REP||c1.TOPIC||c1.ORAL)); end loop; if current_state = :P8_CHECKSUM then update grievance set faanum = :P8_FAANUM, rep = :P8_REP, topic = :P8_TOPIC, oral = :P8_ORAL where grid = :P8_GRID; :P8_RETURN_PAGE := 32; end case; else :P8_RETURN_PAGE := 39; end if; Steal the Show with ApEx

  39. Optimistic locking error When the checksums do not agree, conditional processing prevents the update and conditional branching takes the user to this page. Steal the Show with ApEx

  40. Application level items • Page item names are visible in the HTML source • The names of application level items are not, making them more difficult to tamper with Steal the Show with ApEx

  41. Using application level items LOGIN PROCESS… case when p_sec_lev = 1 then :F134_HEADER := :F134_HEADER || 'FacRep Level'; when p_sec_lev = 2 then :F134_HEADER := :F134_HEADER || 'RVP Level'; else null; end case; Steal the Show with ApEx

  42. User activity or select * from htmldb_activity_log Steal the Show with ApEx

  43. Integrating apps Steal the Show with ApEx

  44. Internal message board Build or borrow a message board application, customize it and integrate it into all of your apps for an internal message board/knowledge base. Steal the Show with ApEx

  45. Application Express skill set Steal the Show with ApEx

  46. Steal the Show with ApEx

  47. Steal the Show with ApEx

  48. Thank you! For more information: Bill Holtzman skyworker@comcast.net 703-403-0139 Steal the Show with ApEx

More Related