Downloaded 479 times
Uploaded byScott Wesley
Oracle Forms to APEX conversion tool
The document outlines the transition from Oracle Forms to Oracle Application Express (APEX), detailing processes, strategies, and key considerations for conversion. It includes step-by-step procedures for migration, differences between Oracle Forms and APEX, and project management aspects essential for successful implementation. Various resources, examples, and hypothetical case studies are provided to assist developers in adapting to the new development environment.
In this document
Powered by AI
Introduction to SAGE Computing Services and a guide to strategies, differences, considerations, Oracle’s product strategy, and recommendations for transitioning from Oracle Forms to APEX.
Detailed breakdown of differences in architecture, languages, page layout, and user interface between Oracle Forms and APEX.
Factors to consider in migration projects, including customer expectations, architecture changes, and retraining needs.
Case study on various features and components involved in converting Oracle Forms applications to APEX.
Detailed steps in the conversion process from Oracle Forms to APEX, including tools and methods to convert components.
Specialized steps for application migration, error analysis, managing forms objects, and customizing applications in APEX.
Discussion of potential bottlenecks, what's missing in migration, and alternatives for conversion strategies.
Summary of strategies for migrating applications, references for further information, and concluding remarks.
Oracle Forms to APEX conversion tool
- 1. SAGE Computing Services CustomisedOracle Training Workshops and Consulting Trials & Tribulations of an Oracle Forms -> Apex Conversion A Hypothetical Case Study Scott Wesley Systems Consultant
- 2.
- 3.
- 4.
- 5. Oracle Product Strategy TraditionalDevelopment Tools Java Development Tools
- 6. 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
- 7.
- 8. Oracle’s Recommendations forCustomers • Based on E-Business Suite – Move from client-server to web – Upgrade to latest versions – Interoperate with Java/J2EE – Develop new modules with JDeveloper
- 9. 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.
- 10. 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
- 11.
- 12.
- 13. Basic Differences Feature FormsApplication Express 4GL Declarative Renders applications with metadata in .fmx Renders applications using metadata stored in database 4GL Languages Client Side PL/SQL Server Side PL/SQL User Interface Java HTML Web Services Yes Yes
- 14. Key Differences Feature FormsApplication Express Page Layout Windows / Canvases Pages / Regions Client-side Field Control Forms Triggers Javascript / AJAX Locking Pessimistic / Optimistic / Custom Optimistic Database Connection Synchronous Asynchronous Concurrent User Support Connectivity by user session Connection only maintained for processing requests Architecture 3 Tier 2/3 Tier Reporting Oracle Reports / BI Beans Flash Charts / BI Publisher
- 16.
- 17. Project Expectations • Suitableapplication development environment (why, what, who, where) Right Reason • Change of behaviour • Best out of Apex? • Retraining required? Conversion • Stateful -> Stateless • UI – layout / flow Architecture • Saving v Conversion CostCost • Case studies • Feedback Customer
- 18. Technical Expectations • Formstriggers • 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
- 19. A Hypothetical CaseStudy
- 20. A Hypothetical CaseStudy • PLL • Menu • Reports • Forms • LOVs • Program Units • Validation triggers • Event triggers • Alerts • Images • Parameters • Visual Attributes • Hierarchical Tree • Procedure based block
- 21.
- 22. 2) Basic Form,Various item types, LOV, Post-Query
- 23.
- 24. 4) Tabular Form,Basic validation, Pre-Insert, Overflow
- 25. 5) Hierarchy, SingleRecord Entry
- 26. 6) View only,Procedural Entry Example
- 27.
- 28. 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
- 30.
- 31.
- 32. Microsoft Access notforgotten
- 33. Step 1 -Convert Modules to XML
- 34. 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
- 35. Convert .pll to.pld
- 36. Convert .rdf, .jspto XML
- 37.
- 38. Step 3 -Create Migration Project
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 46.
- 47.
- 48.
- 49.
- 50. Step 4 –Analyse the Application
- 51.
- 53.
- 54.
- 55.
- 56. Getting your queryright
- 57.
- 58. alter table organisationsadd (close_btn raw(1), save_btn raw(1));
- 59.
- 60. SAGE@sw10g> break onpresentation skip 10
- 61. Register for theNext Application Express V3.2 course: 23rd ,25th and 26th November Advanced course: 8th – 9th December Talk to Sage about moving your Forms applications to Apex SAGE Computing Services (Unrepentant Marketing)
- 62. Step 5 –Generate the Apex Application
- 63. Generate Application -Theory
- 64.
- 65.
- 66. Program Units Computation | Validation| Process Server side packages
- 67.
- 68.
- 69.
- 70.
- 71. Apex (potential) PrintingInfrastructure
- 72. Generate Application -Process
- 73.
- 74.
- 75.
- 76.
- 79.
- 80.
- 81.
- 82.
- 83. So what doesmy new Application look like?
- 87.
- 88.
- 89. Step 6 –Customise your Apex Application
- 90.
- 91. Review • Page structures •UI - themes / images • Navigation components • Report implementation
- 92. Program Units /Triggers • Manually transfer code as validation/computation/process • Annotate
- 93. Enhance Apex Application •Apply conditional processing for regions / items • Apply security via authorisation schemes / conditions • Apply LOVs, alerts, specific item type attributes (checkboxes, dates) • Review all generated buttons • Add session state protection • Annotate
- 94. Add Apex Components •Pages, regions, buttons, items as required • Implement client-side JS & AJAX • Annotate
- 95. Develop Database Components •Ensure code reused in packages and not embedded in the Apex Application where possible • Annotate
- 96. Project Management •System test •Trainusers •Implement application
- 97. Where did mytime go?
- 99. Transform to XML CreateApplication 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
- 100. Consumption of Time XMLUpload Analyse GenerateCustomise Acceptance
- 101.
- 102. Bottlenecks / DangerZones “Oracle has no plans to offer a complete migration solution” Speedy annotation locate & update Custom Query modification Multi-row blocks Non- database items Beyond simple block source Control blocks PL/SQL Conversion UI Defaults
- 103. 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 – ?
- 105.
- 107. Conversion Alternatives • Integration –Wilfred van der Deijl – Visual Integration of Forms & Apex – Two way communication – Phased approach
- 108. Conversion Alternatives • Manuallabour – do it right – Storyboards – Balsamiq / MockupScreens / Sketchflow http://apex-smb.blogspot.com/2009/10/designing-apex-applications-ui-mockups.html
- 109. Conversion Alternatives • Mixture –Using the annotation functionality – Re-use logic where possible
- 110. Underlying Data -- Createview 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');
- 111.
- 112.
- 113. Strategies Forms vApex v ADF Differences Stateful v Stateless Considerations Right Decision Process Simple Converter Review Poor What next? Today’s Summary
- 114. 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_i d=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
- 115. SAGE Computing Services CustomisedOracle 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
- 116. Register for theNext Application Express V3.2 course: 23rd ,25th and 26th November Advanced course: 8th – 9th December Talk to Sage about moving your Forms applications to Apex SAGE Computing Services (Unrepentant Marketing)
Editor's Notes
- #4 Where is Oracle going? What are our options?
- #5 You want something sound, not one that will drive you in circles – one way or another.
- #6 From the Forms Statement of DirectionTwo major pathways / parallel offerings, Forms support is secure
- #7 Two major pathways / parallel offerings, Forms support is secure
- #8 Secure for quite a while
- #9 EBS always a bit of a measuring stickOur recommendation will be to seriously start moving, otherwise keep all your old hardware.Maintaining upgrades will cost more as application server needs to keep up -> WLS
- #10 Part in bold is the key statement, when we complain things are missing from converter – this line could be used.
- #11 Well oiled future with wide ranging improvements
- #13 Sometimes everything looks the same, not in this case.There are some fundamental differences between the two construction models, which is why it’s termed a conversion.
- #14 Off the apex page on OTN, some basic differences…
- #15 But the fundamental & key differences lie here. This chart deserves a little more respect.
- #16 Who here is expecting a silver bullet?
- #17 Because nobody wants to repeat history
- #18 Considered statement of direction/Fusion application infrastructure?Why convert/select particular environment, what’s converted, who’se converting/customer, where deployed?Behaviours must change to get best out of Apex – don’t’ fight productWill saving outweigh conversion cost? Small projects will get cost benefit, large maybe not so much, particularly if you want to keep reports.Large conversion will take a while to get off.
- #20 There is a downloadable application you can walk through, I’ve got my own so you can have something fresh to compare (and I wanted to be different)
- #21 Plus a couple of reports.
- #22 Just the image and boilerplate text.But don’t forget menus
- #23 Post-query gets parent nameLOV on parent org.
- #24 No special code whatsoever, all forms built-ins. Main difficulty will be the other end with the multi-row checkboxes.
- #25 Populates poplist via record group from queryHas record group for resource codeMulti-table data source – only updates bookings.Pre-insert to populate made_by = userEvent Wvi to get event informationResource wvi to clear resource if nullCost display itemStatic lov
- #26 Simple message on date validationAlert displayed when bookings btn pressed with no event selectedBookings form opened on btn pressPre-insert for org selection
- #27 Current record VAHalf attempt at procedure based block.
- #28 I’m going to walk through the conversion process, just so you can see the sort of thing going on. Then I’ll go into detail about what I think of the product.
- #29 Actually different to that shown on the Applications Migration page in ApexMissing a HUGE step
- #30 We really need to be sure to factor this in…But further to that, we really need a step 0 – thinking about what we’re doing…
- #31 So instead, a picture paints a thousand words.Here is a simple version of the diagram I found (Apex for Forms Developers), really high level
- #32 I prefer this diagram, it’s a little more representative of the process.Later I’ll mention where I think the bulk of the work is done.
- #33 Good for those basic applications created by your user base.
- #34 This can be scripted
- #37 Just File->Save as XML.
- #38 You can see XML gives it the power to record just about everything. Just about. I found some missing components, especially with reports.
- #39 I’m going to presume the workspace and objects are there, I think everyone here knows what Apex is about.
- #40 This really isn’t a phrase to consider lightly. It’s not just a matter of processing clicks. We are creating a project where we need to carefully manage content and progress.
- #41 Here is another point to note. Forms really is a *conversion* rather than a sheer migration (without trying to belittle the Access job).And it just conversion, not redesign of business functionality – avoid scope creep.
- #42 I’ll try summit application first
- #43 My project description is too longEven though the underlying table handlesapex_migration_projects -- couldn‘t select rowsapex_030200.wwv_mig_projects.description VARCHAR2(2000)
- #44 Tried converting PLL as my first file
- #47 When I found out I converted the report module incorrectly.(Don’t use convert, use save as XML)
- #48 Note the components, completed, % complete.Now let’s compare to the simple application I created.
- #49 Less components, but less % complete!Complete seems to include things that aren’t applicable? Metadata doesn’t seem to match up, correspond, be consistentSo work remaining will be based on what you start with, how complex your forms are.Now let’s consider what that work’s about.
- #50 To put the level of completion in perspective, here’s an example of annotation. I’ll talk about this more throughout the topic
- #51 Now we have all our meta-data within Apex, let’s see what we can do with it.we can annotate, include, exclude, set to complete, massage queries etc.
- #52 Note IR layout.Implementation details, equivalent component, Applicable
- #54 Where is my where clause/order by?
- #56 Custom query where I have the ability to correct.Had to state this form was one record displayed before I could edit the custom query.
- #57 Modify for my post query.Note buttons in query. Summit application had select into, which was detected for enhanced query – mine wasn’t.
- #58 Seems obvious when showing it to you.
- #59 Took long time to find.Applicable doesn’t explicitly exclude the item.In the end I had to add these as columns to the table!!alter table events add (prev_record raw(1), next_recod raw(1));alter table message_log add (current_ind raw(1));alter table organisations add (close_btn raw(1), save_btn raw(1));Alter tablemessage_log drop column current_ind;
- #61 After the break we’ll generate our Apex application and see what we get.
- #62 Call in at the Sage Computing Services exhibition booth, or contact Pennypenny@sagecomputing.com.au0419904458
- #63 We haven’t really had the chance to do too much except plan what needs to happen with annotations, completion/applicable indicators.We have an “include” checkbox, which does it’s job most of the time.What are we going to expect to get converted, and into what?
- #65 I feel this is one of the more obvious translations, but one of the most important
- #66 This is the immediate concern for anyone considering the concept, and is the most complex part of the conversion. Not only that, it needs to be done post-generation of the application. It’s not part of the conversion process. Only those features out of the box for APEX are converted – no Javascript or AJAX will be generated.
- #67 Again part of post-gen processing.Immediately you’ve crossed out major components of the existing application
- #68 Not generated, but consider recycling concepts as templates, shared lovs, item types, item attributes. UI defaults
- #69 I didn’t see evidence of this being converted automatically, but it’s the translation.
- #70 Need to implement post generation – mostly.
- #71 Page generated linking to interactive reports.Or depending on your infrastructure, you can call existing Oracle reports (for the time being)
- #72 BI Publisher, Apache FOP, Cocoon.Keep eye out for Apex 4 infrastructure
- #74 When I tried with the Summit app, it generated first time without any input from myself. Nice and tidy.When I attempted mine…Remember the columns I had to add to the table? This was the error
- #75 Recognise that query? It was the hardest nut to crack – in the end I couldn’t crack it – had to not include that form.
- #76 Sometimes you do get an indication of where the problem is.Unfortunately in this case due to a case issue.
- #77 Here we see a summary of how the converter has decided to categorise your modules.Documentation?Work to do now?! Post analysis?
- #78 Opening up one of these pages definitions we can see some fun facts and tailor a few minor aesthetics
- #79 Further down includes our reported columns.Creating UI defaults does help here
- #80 We can add pages to our generation.The level of detail here shows us the parallel to the typical level of conversion going on.
- #81 The important choice of theme…
- #82 Nothing exciting here.
- #83 You’ve done it!! Can we go home now?No, less than half the work is done. (depending on how good a planner you are)
- #85 Very exciting. Not really sure how much my menu had to steer this ship. Certainly no conditions on these buttons.
- #86 You can see we’ve gone from a single record forms screen to an IR for my org list and a link to a form page.
- #87 My edit org page is “converted”.Field order poor, can see my org_id, no LOV/poplist for parent organisation/state, no checkbox for internal?!
- #88 Tried doing iterations of generating the app to see what changes where kept in the application design model (since no documentation exists for it)If you add pages, the design model will note those, but any page level enhancements will be lost.I also see now a reports section for the reports I added.I also added the bookings pages to accommodate the form I didn’t include
- #89 So pre-gen tip – don’t rely on the converter for good ordering, item type selection – set up UI defaults for everything.
- #90 This section is going to need a lot of effort.It’s also where you have the opportunity to manage your project using the annotations, trying to ensure things don’t get missed in the conversion.
- #91 I’m not going to go into what you need to get your Apex application up to scratch. I’ve just demonstrated you’re starting with something 1001 monkeys could produce.The tool however can be used to annotate what has been done, and what needs to be done.But there are downfalls. Now onto a post-gen task list..
- #98 Out of all the tasks we need to achieve, where was my time spent?
- #99 My general assessment of where time would probably be spent.Convert to XML – scriptedXML upload – follow the bouncing ballAnalysis – application awarenessGeneration – storyboarding?Customisation – plug the gaps
- #100 Another way to look at it.
- #101 Whichever way you look at it, the conversion tool seems to make up 5% of time, but to what value?You still need to spend the bulk of the time customising.The best value of the tool appears to come from the analysis of metadata – annotations – which doesn’t include all metadata!
- #102 Now time to ponder what we’ve done, and how good it is.
- #103 Annotations – everything’s quite deep and exploration via IR isn’t intuitive. Not all thereCustom query – not always available, still doesn’t mitigate issueMulti-row – related to custom queryNon-db items – placed in query, have to un-include, even if button – had to add columns to table!Block source – can only really handle basic table + post-queryControl – not convertedPL/SQL – not convertedUI Defaults – are needed to get pages that even start to look like what you’ll want.All relate to the Forms SOD
- #104 No backupPL/SQL – expecting it, don’t know solution, oracle not offering conversion toolItem type/order – rely on UI defaultsViews – best feature not affirmed with viewsSearch - Quick way to find things that aren’t complete, compilation errorsBulk change – completion, not just “these triggers N/A”XML – things I’ve spotted.
- #105 Do you really need to browse forms logic – should your app documentation already cover this?
- #107 Ditch the converter.
- #109 Story boarding so important when converting applications between vastly difference architecture!
- #110 Annotations best feature of the converter,utilise these while tackling the conversion using a fresh start/integration.
- #111 Not project specific, but lists (most) annotations
- #112 Can start to get more granular – in the one place
- #113 Quick and dirty view of outstanding work.
- #114 Everyone needs to be aware of the fundamental differences – manager thru end user
- #117 Call in at the Sage Computing Services exhibition booth, or contact Pennypenny@sagecomputing.com.au0419904458