SAGE Computing Services

Customised Oracle Training Workshops and Consulting

Trials & Tribulations of an

Oracle Forms -> Apex Conversion

A Hypothetical Case Study

​

Scott Wesley

Systems Consultant

​

​

​

​

​

Today’s Guide

Strategies

http://strategy2c.wordpress.com/2009/01/10/strategy-for-goldfish-funny-illustration-by-frits/

Oracle Product Strategy

Traditional Development Tools

Java Development Tools

Oracle Forms – Statement of Direction

• Oracle Forms and Reports
• “Oracle has no plan to de-support these products, furthermore new versions are components of Oracle Fusion Middleware 11g”
• Oracle Designer
• Focus on stability and bug fixes, released via Oracle Development Suite 10g

Lifetime Support Document

Oracle’s Recommendations for Customers

• Based on E-Business Suite
• Move from client-server to web
• Upgrade to latest versions
• Interoperate with Java/J2EE
• Develop new modules with JDeveloper

​

To Quote Oracle…

• Oracle JDeveloper 11g with ADF is the tool of choice for Forms, Reports and Designer customers because it carries over a similar development model. However, given the architecture difference between Java EE and Forms or Reports, Oracle has no plans to offer a complete migration solution that would attempt to migrate applications built with these tools to Java EE.
• Instead, Oracle’s strategy is to provide a Java EE development environment exposing similar concepts as Forms and Reports, giving to Forms and Reports developers the opportunity to become productive in a new, but familiar environment.

Application Express – Statement of Direction (Feb09)

• Native Web application development framework
• Conceived February 2004 with Oracle Database 10g
• “APEX 4.0 will focus on declarative support for Web 2.0 features as well as extensibility of APEX framework”
• APEX Websheets – end user define data rules
• APEX Listener – J2EE alternative to mod plsql
• Improved tab forms – validation, collection integration
• AnyChart 4 – flash based maps, Gantt charts
• Dynamic Actions – page rendering on client events
• Declarative support for cascading select lists
• Extensible item framework – custom item types
• Updateable Interactive Reports – inline editing
• New Authentication schemes

​

Differences

Differences

Basic Differences

Key Differences

Project Considerations

Project Considerations

Project Expectations

Technical Expectations

• Forms triggers
• PLLs
• Forms built-ins
• Code split into UI / data layers?
• Blocks based on stored procedures
• Control blocks
• Ongoing changes
• Turnkey vs Phase

​

Developer

Architecture

• Menus (security)
• Object libraries
• Validation
• Hotkeys
• Reports (printing = $$)
• Turnkey vs Phase
• Storyboards

​

A Hypothetical Case Study

A Hypothetical Case Study

• PLL
• Menu
• Reports
• Forms
• LOVs
• Program Units
• Validation triggers
• Event triggers
• Alerts
• Images
• Parameters
• Visual Attributes
• Hierarchical Tree
• Procedure based block

​

1) Splash Screen

2) Basic Form, Various item types, �LOV, Post-Query

3) Master Detail

4) Tabular Form, Basic validation,�Pre-Insert, Overflow

5) Hierarchy, Single Record Entry

6) View only, Procedural Entry Example

Conversion Process

Conversion Process Theory

• Chapter 4 – Converting an Oracle Forms Application
• How to convert your application
• Step 1: Convert Oracle Forms to XML
• Step 2: Create a Workspace and add APEX Users
• Step 3: Upload database objects into the schema associated with your workspace
• Step 4: Create a conversion project
• Step 5: Review and edit forms metadata
• Step 6: Generate the Oracle APEX Application
• Deleting a migration project

Apex Migration Guide

Apex Migration Guide

Microsoft Access not forgotten

Step 1 - Convert Modules to XML

Step 1: Convert .fmb, .olb, .mmb files to XML

S:\SW-SHARE>frmf2xml overwrite=yes bookings.fmb

Oracle Forms 10.1.2 Forms to XML Tool

Copyright(c) 2001, 2005, Oracle. All rights reserved.

​

Processing module bookings.fmb

WARNING: Item STATUS - all ListItemElements must have an Index, Name and Value.

XML Module saved as bookings_fmb.xml

​

Convert .pll to .pld

Convert .rdf, .jsp to XML

Resulting XML

Step 3 - Create Migration Project

Application Migration?

Application Conversion

Define Project

First error…

Second error…

Upload more files

Third error…

Migration Summary (Summit)

Migration Summary (Sage)

Annotations

Step 4 – Analyse the Application

Forms Objects

Metadata differences

Forgotten Metadata

Custom Query

Getting your query right

Exclude Items

alter table organisations add

(close_btn raw(1), save_btn raw(1));

(Explore meta-data)

SAGE@sw10g> break on presentation skip 10

​

Register for the Next

Application Express V3.2 course:

23^rd ,25^th and 26^th November

Advanced course:

8^th – 9^th December

​

Talk to Sage about moving your Forms applications to Apex

​

​

​

​

Call in at the Sage Computing Services exhibition booth, or contact Penny

​

penny@sagecomputing.com.au

0419904458

SAGE Computing Services

(Unrepentant Marketing)

​

Step 5 – Generate the Apex Application

Generate Application - Theory

?

?

Apex (potential) Printing Infrastructure

Generate Application - Process

“Compilation errors”

“Compilation errors”

“Compilation errors”

Application Design Model

Add Extra Pages

Define Theme

Confirm Creation

Success!

So what does my new Application look like?

Iterative Process?

UI Defaults

Step 6 – Customise your Apex Application

Annotations

Where did my time go?

1%

2%

2%

30%

40%

Transform to XML

Create Application Express Workspace / Migration Project

Upload XML files to workspace

​

Analyse the application metadata

Generate the Application Express Application

Customise the Application Express Application

User Acceptance & Testing

Consumption of Time

Conversion Tool = ~5%

Review

Bottlenecks / Danger Zones

What’s missing?

• Export / Backup
• Ability to pre-build Apex validations during conversion
• PL/SQL Placeholders
• Define item type/order
• Conversion of LOVs, Alerts
• Apex Views
• Annotations
• Aggregation (combined listings)
• SQL Developer Integration
• Search facility (not IR)
• Bulk change on all object types (not application applicability)
• XML Metadata
• Report triggers
• Certain form properties (eg. WHERE clause)
• Menu roles
• Non-Database item recognition
• ?

​

So what’s next?

Conversion Alternatives

• Integration
• Wilfred van der Deijl
• Visual Integration of Forms & Apex
• Two way communication
• Phased approach

Conversion Alternatives

• Manual labour – do it right
• Storyboards – Balsamiq / MockupScreens / Sketchflow

http://apex-smb.blogspot.com/2009/10/designing-apex-applications-ui-mockups.html

Conversion Alternatives

• Mixture
• Using the annotation functionality
• Re-use logic where possible

Underlying Data

-- Create view of annotations

create or replace view annotations as

(output of)

select 'select '''||table_name||''' table_name, '

||nvl((select distinct first_value (column_name) over (order by column_id) from all_tab_columns s where m.table_name = s.table_name and s.column_name like '%NAME%') ,

(select distinct first_value (column_name) over (order by column_id) from all_tab_columns s where m.table_name = s.table_name and (s.column_name like '%ROLE%' or s.column_name like '%EXPRESSION%')))

||‘ name, select_for_app, applicable, complete, priority, assignee, notes, tags from '||owner||'.'||table_name||' union all ' a

from all_tab_columns m

where column_name like 'APPLICABLE'

and owner = 'APEX_030200'

and table_name not in ('WWV_MIG_PROJECT_COMPONENTS','WWV_MIG_FRM_COORDINATES','WWV_MIG_RPT_GRP_FILTER'

,'WWV_MIG_RPT_DATASRC_SELECT','WWV_MIG_RPT_DATA'

,'WWV_MIG_FRM_MENUS_MODULES','WWV_MIG_FRM_MODULES','WWV_MIG_OLB_MODULES');

​

Annotation Summary

Annotation Priority

Today’s Summary

Strategies Forms v Apex v ADF

Differences Stateful v Stateless

Considerations Right Decision

Process Simple

Converter Review Poor

What next?

References

• David Peake
• http://www.oracle.com/technology/obe/apex32/apex32frmmigr.htm
• http://www.oracle.com/technology/oramag/oracle/09-may/o39browser.html
• Grant Ronald
• http://groundside.com/blog/GrantRonald.php?title=migrate_oracle_forms_to_apex
• Oracle Documentation
• http://www.oracle.com/technology/products/database/application_express/html/doc.html
• http://www.oracle.com/technology/products/forms/pdf/10g/ToolsSOD.pdf
• http://www.oracle.com/technology/products/database/application_express/apex_sod.html
• Douwe Pieter van den Bos
• http://www.packtpub.com/oracle-application-express-forms-converter/book
• Marc Sewtz
• http://www.technicalconferencesolutions.com/pls/caat/caat_presenters_upd.display_document?conference_id=39&abstract_id=56&document_id=78793
• John Edward Scott and Scott Spendolini
• http://www.amazon.com/Oracle-Application-Express-Experts-Voice/dp/159059827X
• OraTransplant
• http://www.oratransplant.nl/oracle-forms-as-web-component/
• Roel Hartman
• http://roelhartman.blogspot.com/2009/10/integration-of-forms-and-apex-calling.html

SAGE Computing Services

Customised Oracle Training Workshops and Consulting

​

​

​

​

​

​

Questions and Answers?

​

​

​

​

​

Presentations are available from our website:

http://www.sagecomputing.com.au

​

enquiries@sagecomputing.com.au

scott.wesley@sagecomputing.com.au

http://triangle-circle-square.blogspot.com

​

​

​

​

​

​

​

Register for the Next

Application Express V3.2 course:

23^rd ,25^th and 26^th November

Advanced course:

8^th – 9^th December

​

Talk to Sage about moving your Forms applications to Apex

​

​

​

​

Call in at the Sage Computing Services exhibition booth, or contact Penny

​

penny@sagecomputing.com.au

0419904458

SAGE Computing Services

(Unrepentant Marketing)

​