Transcript Tight Tabular Forms

APEX:
Tight Tabular Forms
Karen Cannell
[email protected]
TH Technology
http://www.thtechnology.com
TH Technology
About Me …
Karen Cannell ~ Consultant, TH Technology
Analyzed, designed developed, converted, upgraded,
enhanced and otherwise worked on database
applications for 25+ years, focused on Oracle since
1994.
Currently migrating business processes from paper to
web-based systems, leveraging the Oracle 10g & 11g
suite of tools. Lately APEX.
Editor, ODTUG Technical Journal
Volunteer to author ODTUG Journal Articles!
Using APEX since the HTMLDB beginning
APEX Tight Tabular Forms
TH Technology
About You … (Audience Background)
New to APEX?
APEX Experience?
Previous Tools?
APEX Training?
Version 2.0? 3.1 ? 3.2 ? 4.0?
APEX Tight Tabular Forms
TH Technology
Agenda
Tabular Forms, <= APEX 3.2
Loose
Tabular Forms, Manual
Tighter and Tedious
Tabular Form 4.0 Enhancements
Tighter, less work OOTB
Tabular Form 4.0 + Custom
Tightest and (a bit) less Tedious
APEX Tight Tabular Forms
TH Technology
Tabular Forms
Multiple Rows, Columns at Once
APEX Tight Tabular Forms
TH Technology
Tabular Form vs. Updateable Report
Tabular Form = = Updateable Report
Report + editable items + MRU, MRD, Add Row
Processes.
APEX Tight Tabular Forms
TH Technology
Tabular Forms <= APEX 3.2
~ Loose ~
APEX Tight Tabular Forms
TH Technology
The Problem? (<= APEX 3.2)
Limited Out-of-the-Box Validations
Required, Format Mask Column Checks Only
No Per Row
No “Has Row Changed?”
Error Message Stops on First
Error Does Not Show Row/Column of Error
Too Loose!
Bad Data
In …
Bad Data
Need
to Tighten
Up Out
… …
APEX Tight Tabular Forms
TH Technology
Tabular Forms <= APEX 3.2
Plus Manual Validations
~ Tighter, Tedious ~
APEX Tight Tabular Forms
TH Technology
Tabular Form
No Element Names
Where to Add Validations?
APEX Tight Tabular Forms
TH Technology
Tabular Form Validations - Solutions
Use a Developer Framework
i.e. APEXLib
Manually Code Validations
APEX Tight Tabular Forms
TH Technology
APEXLib
APEXLib Development Framework
http://apexlib.oracleapex.info/index.html#index
For APEX <- 3.2, Tabular form Enhancements
Help
Not an Oracle Product
Not part of APEX
APEX Tight Tabular Forms
TH Technology
APEXLib Tab Form Features
Reference columns by name without having to find
which array your column is mapped to
To get the correct number of rows to process
Identify which row has changed
Get your data with the correct data type (NUMBER,
DATE) without having to take care about format masks.
Add a required column check by declaration, instead of
coding it.
Get more meaningful error message than APEX
provides.
These Features are in APEX 4.0
APEX Tight Tabular Forms
TH Technology
To Manually Code Validations:
Get the Generated Item Names
or
Use APEX_ITEM in SQL Query
then
Use APEX_APPLICATION.g_fxx Arrays to
Manually Write Validation/Processing
Procedures
APEX Tight Tabular Forms
TH Technology
Generated Item Names
Each Item has a Handle
per Wizard’s Standard Naming Convention
Handle   Aligns with an
APEX_APPLICATION.g_fxx Array
APEX Tight Tabular Forms
WebDeveloper Show ID & Class Details
TH Technology
APEX Tight Tabular Forms
TH Technology
Manual Tab Form Processing
Has the Row Changed?
APEX Tight Tabular Forms
TH Technology
Tabular Forms APEX 4.0+
~ Tighter, Less Work ~
APEX Tight Tabular Forms
TH Technology
APEX 4.0 Tabular Forms
Validations!!
NOT NULL
Column String Comparison
e.g. value is numeric, value is a date, value only
contains the following characters etc.
Error Message
Error Cell Highlighting
APEX Tight Tabular Forms
TH Technology
Tabular Form - Validations
APEX Tight Tabular Forms
TH Technology
Tabular Form - Validations
Columns
APEX Tight Tabular Forms
TH Technology
Tabular Form Validations
NOT NULL
String Comp
APEX Tight Tabular Forms
TH Technology
Tabular Form – String Comp
String Comparison
Options
APEX Tight Tabular Forms
TH Technology
Tabular Form Validations
Error
Display
Location
APEX Tight Tabular Forms
TH Technology
Tabular Form – Error Messages
Column and Row
APEX Tight Tabular Forms
TH Technology
Tabular Form Validations
Page
APEX Tight Tabular Forms
TH Technology
Tabular Form Validations - Page
SQL, PL/SQL
Use APEX_APPLICATION.g_fnn Arrays
APEX Tight Tabular Forms
TH Technology
4.0 Tabular Forms - Limitations
Cannot Directly Reference Column Values
i.e. like Page Items
Field Highlighting Only via Declarative
Validations
Default NOT NULL Validations Do Not Highlight
LOV Columns Do Not Highlight
Limited Options
“… We have plans to further extend this to support additional
types of validations in future release of Application Express.”
APEX Tight Tabular Forms
TH Technology
Tabular Forms 4.0 - Limitations
No Declarative Per-Row Validation
No Declarative Page Validation (ok)
No Easy Reference to Tabular Form Cells
Field Highlighting Only Through Declarative
Validations
To Code These Use:
APEX_ITEM in Query
APEX_APPLICATION.g_f0nn Arrays
Javascript
APEX Tight Tabular Forms
TH Technology
Tabular Forms APEX 4.0+
Plus Manual Validations
~ Tightest, Less Tedious ~
APEX Tight Tabular Forms
TH Technology
Manual Tabular Forms - Possibilities
Highlight Rows
Disable Columns
Custom Validations
Row-level, Page-level, Complex
Whatever You Can Code
Second Tabular Form on page
Whatever You Can Code!
APEX Tight Tabular Forms
TH Technology
Generated Item Names
Each Item has a Handle
per Wizard’s Standard Naming Convention
Per APEX_ITEM Call
Handle  
APEX_APPLICATION.g_fxx Array
APEX Tight Tabular Forms
WebDeveloper Show ID & Class Details
TH Technology
APEX Tight Tabular Forms
TH Technology
Manual Tabular Forms
User APEX_ITEM in Query to Build Element
Embed JavaScript in the APEX_ITEM call
Use APEX_APPLICATION.g_f0nn Arrays to
Code Custom Validations, Other Processing
Essentially, you are Manipulating Arrays …
APEX Tight Tabular Forms
TH Technology
SQL for Manual Tabular Form:
Regular SQL Query
APEX Tight Tabular Forms
TH Technology
Manual SQL for Tabular Form
APEX Tight Tabular Forms
TH Technology
Resulting Item Names
APEX Tight Tabular Forms
TH Technology
APEX_ITEM.TEXT Call
Syntax:
APEX Tight Tabular Forms
TH Technology
APEX_ITEM.TEXT Example
APEX Tight Tabular Forms
TH Technology
APEX_ITEM.TEXT - Sorting
APEX Tight Tabular Forms
TH Technology
APEX_ITEM.MD5 - Checksum
Use When Need Checksum
Use for Has Row Changed Checks
APEX Tight Tabular Forms
TH Technology
APEX Tight Tabular Forms
TH Technology
APEX_APPLICATION.g_fXX Arrays
APEX Tight Tabular Forms
TH Technology
PL/SQL to Process Manual Tab Form
Use APEX_APPLICATION.g_f0n arrays to
Process Data
Example:
Landed Lbs Must = Landed Qty If Unit = ‘LB’
APEX Tight Tabular Forms
TH Technology
Landed Lbs Must = Landed Qty If Unit = ‘LB’
Pseudocode:
IF g_f13=‘LB’ THEN
IF g_f11 <> g_f12 THEN
.. ‘Landed Lbs must match Landed Qty when Unit is LB’;
END IF;
END IF;
APEX Tight Tabular Forms
TH Technology
The Real Code – Assessment Form Validation
DECLARE
v_msg VARCHAR2(4000) := ' ';
v_br VARCHAR2(6) := '<br>';
BEGIN
FOR i IN 1 .. APEX_APPLICATION.g_f11.COUNT LOOP
IF APEX_APPLICATION.g_f13(i) = 'LB' THEN
IF APEX_APPLICATION.g_f11(i) <> APEX_APPLICATION.g_f12(i) THEN
v_msg := v_msg
||'Row '||TO_CHAR(i)
||‘ - Landed Lbs must equal Landed Qty if unit is LBs‘
||v_br;
END IF;
END IF;
END LOOP;
RETURN v_msg;
END
APEX Tight Tabular Forms
TH Technology
APEX Tight Tabular Forms
TH Technology
Tabular Forms – Workarounds
Manual Tabular Forms
APEX_ITEM to build query, APEX g_f0nn arrays
Validations not covered by APEX
By-Row Validations
Embed JavaScript for all those extras:
Highlighting
Cascading LOVs
Conditional Validations
Development Framework (ex: APEXLib - Tab form
help for < APEX 4.0)
APEX Tight Tabular Forms
TH Technology
Manual Tabular Forms - Possibilities
Highlight Rows
Disable Columns
Custom Validations
Row-level, Page-level, Complex
Whatever You Can Code
Second Tabular Form on page
Whatever You Can Code!
APEX Tight Tabular Forms
TH Technology
Tabular Form - Manual
Manual
Tabular Form
Using
APEX_ITEM
APEX Tight Tabular Forms
TH Technology
APEX Tight Tabular Forms
TH Technology
Manual Tab Form Processing
Has the Row Changed?
APEX Tight Tabular Forms
TH Technology
Tabular Form - Online Examples
Great Examples at
Denes Kubicek
http://htmldb.oracle.com/pls/otn/f?p=31517:170
Many others out there!
APEX Tight Tabular Forms
TH Technology
Manual Tabular Forms Demo
~ in case I forgot ~
APEX Tight Tabular Forms
TH Technology
Tight Tabular Forms - Summary
<= APEX 3.2
- No Validations
- Anything can get in
Unless You Build It …
APEX 4.0 +
- Out-of-the-Box Validations Improves
- Still only part way there!
Be Prepared to Code, Still …
APEX Tight Tabular Forms
TH Technology
References
APEX_ITEM Documentation
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b32258/api.
htm#HTMDB15003
APEX_APPLICATION Documentation
http://download.oracle.com/docs/cd/E10513_01/doc/apirefs.310/e
12855/apex_app.htm
APEX Documentation and Examples
http://apex.oracle.com
Denes Kubicek (Tabular Form Examples)
http://htmldb.oracle.com/pls/otn/f?p=31517:170
APEXLib (Tabular Form Help)
http://apexlib.oracleapex.info/
APEX Tight Tabular Forms
TH Technology
K’Scope 2011!!
APEX Tight Tabular Forms
TH Technology
Share Your Knowledge !
Call for Articles/Reviewers
ODTUG Technical Journal
ALWAYS Looking for Content!
[email protected]
APEX Tight Tabular Forms
Questions ?
Tight Tabular
Forms
[email protected]
http://tunahuntress.com/apex
TH Technology
Thank You ~
Tight Tabular
Forms
[email protected]
http://tunahuntress.com/apex
TH Technology
TH Technology
<= 3.2 Tabular Form - Drawbacks
Limited Validations on Columns
No Row-Changed Function
Error Messages Do Not Indicate Row/Column
Stops After First Error
To Code Validations, use
APEX_APPLICATION.g_fxx Arrays
All Custom Code
If Order Changes, Re-Code
Conditions Change g_fxx Array
Checkbox Columns Use g_fxx Arrays Differently
…
APEX Tight Tabular Forms
TH Technology
Tabular Form - Manual
Manual
Tabular Form
Using
APEX_ITEM
APEX Tight Tabular Forms
TH Technology
APEX Tight Tabular Forms