For this year's Australian Oracle User Group conference I decided to consolidate some of my favourite things to do with 3 of my favourite APEX features.
I considered this a bit of a 'lazy' decision, as it didn't really require any new research - but I think sometimes that can produce some good content, since I'm consolidating years of experience into a session.
It did give me a little leverage for one of my Kscope19 abstract submissions - another concept I've been working on for some time now regarding the management of multiple applications acting as one.
Some of my favourite features? I covered Dynamic Actions, of course, plus some use cases for Build Options, and the REQUEST value. Why? Well, check it out.
One thing I learned from this session is to concentrate on the group in the room. I was asked to record my session, which I was happy to do, but I wasn't mic'd up, so I had to spend my time behind the podium so the laptop mic would record my voice. I prefer to move about & show my enthusiasm, but a colleague said I ended up spending too much time looking at my laptop instead of the people in the room.
And in the end, the recording didn't even work!
Connor suggested a better idea would probably be a phone/camera on a tripod. Lesson learned.
I aim to create a few blog posts out of this session, so some of the ideas can be consumed as such, so stay tuned.
Thursday, 6 December 2018
Thursday, 1 November 2018
Lazy Loading Menu Count
I've been using the lazy loading concept demonstrated in Maxime's post quite a lot recently, I'd love to see this as a declarative feature one day.
I also wondered if I could apply this concept to the badge count in the side menu - not slow the page load by a longer running query that populates the count.
Turns out it wasn't that hard, particularly since I already had the jQuery I needed from a previous requirement.
We first need to add a unique class to the link definition, so we can identify the menu item that needs updating.
The label contains the substitution string reference to an application item called LAZY_MENU, that is just computed to zero on each page load.
Surrounded by square brackets, this turns it into the count badge.
Add a dynamic action that executes PL/SQL on Page Load, probably to the global page, though this demo only fires when on page 15.
The demo populates the item based on a query that counts how many columns in my schema, plus a random number, so we can see it change each time.
A subsequent JavaScript action is where the real action is at
A more complete solution may also update LAZY_MENU application item, ready to have reasonably fresh information for the next page load.
Or we could make our queries faster ;p
I also wondered if I could apply this concept to the badge count in the side menu - not slow the page load by a longer running query that populates the count.
Turns out it wasn't that hard, particularly since I already had the jQuery I needed from a previous requirement.
We first need to add a unique class to the link definition, so we can identify the menu item that needs updating.
The label contains the substitution string reference to an application item called LAZY_MENU, that is just computed to zero on each page load.
Surrounded by square brackets, this turns it into the count badge.
Add a dynamic action that executes PL/SQL on Page Load, probably to the global page, though this demo only fires when on page 15.
The demo populates the item based on a query that counts how many columns in my schema, plus a random number, so we can see it change each time.
A subsequent JavaScript action is where the real action is at
$('#t_TreeNav span.lazy-menu')
.closest('div.a-TreeView-content')
.find('span.a-TreeView-badge')
.text($v('P15_COUNT'));
The selector identifies the menu item based on the lazy-menu class entered in the link definition, then traverses the tree to locate the relevant badge, which is then updated to the page item.A more complete solution may also update LAZY_MENU application item, ready to have reasonably fresh information for the next page load.
Or we could make our queries faster ;p
Thursday, 11 October 2018
ODC Appreciation Day : APEX Workspace Activity Logs
Everything gets a special day these days, and thanks to Tim Hall's encouragement, you can enjoy a vibrant display of appreciation for what the Oracle Developer Community embraces about the technology they use daily.
The first year I talked about Dynamic Actions.
Last year I missed the boat - too busy preparing for some holidays.
This year I'd like to lay down my love for a supply of data every APEX developer has access to: apex_workspace_activity_log.
Yup, I'm talking about a log table.
It's enabled in your Oracle APEX applications by default, and inserts a record in a log table every time APEX renders a page, or a dynamic action interacting with the database thanks to a PL/SQL or Refresh action.
Every time a user opens a page, you know when, who, what, why, and how long it took to generate.
We can combine this information to produce a wealth of information. Oracle provides some out of the box under Administration -> Monitor Activity.
I think the first report worth noting is By Weighted Page Performance.
This report multiplies how many times certain pages were loaded by how long it took to load, so ordering gives weight to those pages consuming overall time. Here I've also highlighted the Median column, since that too can be an indicator. A higher average that maintains a low median can indicate outliers, while a raised median really should be looked at.
Fundamentally, we're asking - should I choose to tune a 3 second page that runs once a day, or tune a 1 second page that runs thousands of times a day?
I've taken this information to produce a range of drillable reports, starting with a beautiful OracleJET chart. This starts by day - guess where the weekend is?
Another favourite of mine plots performance over time, allowing me to target specific pages.
It all reminds me of the first time I remember implementing such a practice in Oracle Forms. We had a pre-form trigger that would log who opened which Form when.
Using this information, we could communicate with the business about how often how many people really are using those 'super important' Forms.
Now APEX gives me this information out-of-the-box, and also tells me how long it spent working on each result. Awesome!
It's proof.
Proof that a user has (or hasn't) opened a particular page.
Proof that the application 'isn't running slow', well, the database is certainly ok.
Proof that people are in fact using your application.
From 5.x it also logs the Request value, allowing more granularity than ever before. With this I can do things like measure how often a page 3 is opened from page 1 vs page 2. Where are users clicking to navigate?
So thank you, Oracle APEX team, for continuing to baking such a resourceful feature into the product.
I use it every day.
#ThanksODC
Further reading:
- Start reading related documentation here.
- Martin shows us how to get started with archiving this data. It is possible to change the interval between the background table switching.
- Jeff offers an example showing recent users of APEX.
- I removed outliers in this post.
- I pasted a few other SQL examples in this forum question.
The first year I talked about Dynamic Actions.
Last year I missed the boat - too busy preparing for some holidays.
This year I'd like to lay down my love for a supply of data every APEX developer has access to: apex_workspace_activity_log.
Yup, I'm talking about a log table.
It's enabled in your Oracle APEX applications by default, and inserts a record in a log table every time APEX renders a page, or a dynamic action interacting with the database thanks to a PL/SQL or Refresh action.
Every time a user opens a page, you know when, who, what, why, and how long it took to generate.
select apex_user as who ,application_id||':'||page_id as what ,view_date as when ,elapsed_time as how_long ,page_view_type as why ,request_value as more_why from apex_workspace_activity_log where application_id >= 4000 and apex_user != 'nobody'
 |
| How APEX Builder dashboard knows who's been the busiest developer |
We can combine this information to produce a wealth of information. Oracle provides some out of the box under Administration -> Monitor Activity.
I think the first report worth noting is By Weighted Page Performance.
 |
| Packaged Reports |
This report multiplies how many times certain pages were loaded by how long it took to load, so ordering gives weight to those pages consuming overall time. Here I've also highlighted the Median column, since that too can be an indicator. A higher average that maintains a low median can indicate outliers, while a raised median really should be looked at.
 |
| Weighted Page Performance |
Fundamentally, we're asking - should I choose to tune a 3 second page that runs once a day, or tune a 1 second page that runs thousands of times a day?
I've taken this information to produce a range of drillable reports, starting with a beautiful OracleJET chart. This starts by day - guess where the weekend is?
 |
| OracleJET Plots Pretty Popular Page Performance |
Another favourite of mine plots performance over time, allowing me to target specific pages.
It all reminds me of the first time I remember implementing such a practice in Oracle Forms. We had a pre-form trigger that would log who opened which Form when.
Using this information, we could communicate with the business about how often how many people really are using those 'super important' Forms.
Now APEX gives me this information out-of-the-box, and also tells me how long it spent working on each result. Awesome!
It's proof.
Proof that a user has (or hasn't) opened a particular page.
Proof that the application 'isn't running slow', well, the database is certainly ok.
Proof that people are in fact using your application.
From 5.x it also logs the Request value, allowing more granularity than ever before. With this I can do things like measure how often a page 3 is opened from page 1 vs page 2. Where are users clicking to navigate?
So thank you, Oracle APEX team, for continuing to baking such a resourceful feature into the product.
I use it every day.
#ThanksODC
Further reading:
- Start reading related documentation here.
- Martin shows us how to get started with archiving this data. It is possible to change the interval between the background table switching.
- Jeff offers an example showing recent users of APEX.
- I removed outliers in this post.
- I pasted a few other SQL examples in this forum question.
Thursday, 27 September 2018
Oracle Forms to APEX IDE Transition
If you would like a good high level run-down on why Oracle APEX is a great choice to modernise Oracle Forms, have a read of this.
After listening in on the AskTom Office Hours on this topic (make sure you also read the chat transcript), I had a few ideas for posts to help Forms developers transition to APEX, before my Forms knowledge gets too stale!
If you put the Forms & APEX IDEs together, it's not hard to see they're sown from the same cloth, so to speak.
Navigator on the left, properties on the right, layout editor in them middle. All with similar behaviours (link to my videos from a while back)
I thought I'd run down some components within the Forms IDE, and add some commentary.
The key thing to remember when transitioning from Forms to APEX is ... empty the cup.
Web behaviour changes some UX fundamentals, but it's an environment in which you should already be rather fluent. So forget about how you build applications in Forms, and think about how you want your website to behave.
Forms renders the Form to the pixel from compiled source files, and you can show/hide elements on the canvas upon certain triggers firing.
A user visiting a URL in APEX will have their HTML page dynamically generated by PL/SQL, with a variety of conditions on components that decide if they're included in the render.
After the page is delivered to the browser, Dynamic Actions then respond to events that may happen on the page before the next page submit, which allows validation and processing of all the values on the page, or a page redirect, which just opens the target, leaving page data behind.
The behavioural difference to remember is that the database knows nothing about the values on the web page until you either
1) submit the page (Processing)
2) execute a PL/SQL dynamic action that submits page items to session state
Many of your initial problems will probably relate to forgetting to submit/return these values.
In a stateful environment, this behaviour is fine, and made Forms powerful. In the web environment, these triggers don't have much of a 1-to-1 translation, but the main analog here might be Dynamic Actions.
Many APEX pages might handle processing when you submit the page, so you may have validations, computations, and processes that fire, based on flexible conditions.
Interactive pages may have dynamic actions that respond to specific clicks, but not submit or refresh the entire page, just some of it. This reduces network traffic, and enables clever user experiences.
For instance, you could set the value of a common item P0_RESULT within your process, and display it as the success message using the
For Dynamic Actions interacting with the database, you may want to display the same style message.
Check out this example from Martin using the Supplied API.
Or for dialog behaviour where you control button naming, you might consider using a plugin such as Alertify or Pretius Enhanced Notifications.
You can include your own JavaScript and CSS in several ways, mostly via the page attributes.
You can choose to add inline code, reference files that exist either in the database as a BLOB (#APP_IMAGES#), or sitting on the middle tier, typically served by ORDS, perhaps sitting on a Web Logic Server, prefixing the file location with #IMAGE_PREFIX#.
If you use a decent amount of custom JavaScript, you may wish to consider APEX Nitro.
In APEX, you could say similar things about Regions. Each page is broken up into a bunch of regions, which could come in many forms. Check out the Component Templates available in the Universal Theme.
You can even construct master-detail relationships in APEX. There are no Relations to define, but relationship properties are set within the Region attributes.
If there is something that APEX doesn't do out of the box, it will probably be worth looking to see if anyone has built a plugin for APEX to do so. Some of these are absolute crackers. Once I did a session on consuming plugins.
An LOV in APEX is more coupled with the Item type. A radio group works well with a small handful of values, Select Lists for middle sized sets, and perhaps the Select2 or I really like the Modal LOV plugin for larger sets to be dynamically queried, while still mastering the keyboard entry.
Or you might even define your own modal page to fetch a value.
There is a native Popup LOV, but I don't like it.
I think a comparison for grouping objects in APEX might be item plugins, possibly project specific.
Or maybe blueprints.
Larger forms with a number of items are generally submitted as a whole, where the page process handles the data, and branches to the next page.
These values can be protected from URL tampering by session state security, at item, page, and application levels, often by default.
The APEX team have implemented them within the Page Designer - my favourite is the duplicate option.
But we've all seen Forms written with a spectrum of best practices applied. This is where many of the performance problems could lie, especially poorly used post-query triggers.
For Forms to APEX migration projects, this is probably where most of your gold lies - all the business rules you want to keep. Depending on how well the application is written, you might get to re-use a lot of it, but in my experience, few Forms projects used SmartDB concepts.
If you're aiming for a certain % of re-use, don't aim too high. The code in here can be good guide for the developer in refactoring behaviour, but the APEX page(s) could end up looking and behaviour different to what you had in Forms, if only because you're now in a browser. Thought it could be because you've reviewed the business process since the Form was written 2 decades ago, or you've replaced a lot of the keystrokes with button taps.
In APEX we can define User Interface Defaults in the data dictionary, so that each time items or reports are created for specific tables or columns, the same features are applied by default.
As for the style of the application, classes could be applied to components that carry a particular look & feel. The Universal Theme has a default skin that can be reconfigured declaratively.
Head to an application's Shared Components, then locate LOVs. They can be dynamically driven by a SQL query, or be statically defined. Static definition allows a variety of conditions to be applied to each entry.
These LOVs can then be associated with Items such as Radio Groups & Select Lists, or with a column in a report, to translate a code to a label.
Your power users can be given Interactive Reports with a number of runtime manipulation options.
Oracle JET charts can respond to clicks, allowing drill down functionality, in whatever way you want.
Or you could go down the yellow brick road and choose a tool to dynamically generate PDF documents, Excel spreadsheets, Word files etc.
In APEX most interactions happen within the browser window, within an inline modal (just a special looking page region), within a page modal. From APEX 5.x this became native and easy, allowing modals to have validations and branches, just like standard pages.
The menu in APEX can either be across the top, or down the left side. These menus can be statically defined, or dynamically driven, even by the meta-data that is apex_application_pages view. (my link)
Static navigation entries could be controlled by authorisation schemes, or custom conditions.
Dynamic menus can have security tables integrated within the SQL.
There are a few list templates that follow the same data structure, just rendered in different ways.
There is a smaller Navigation Bar at the top right, typically starting with the login/logout buttons.
APEX also includes a palette of components ready to drag onto the layout editor, which I primarily use to check if items/regions are set to Start New Row or not.
As with Forms, finding the right property can be tricky, or even being aware of its existence.
I highly recommend you take note of the inline help associated with each attribute. I'm sure this content improves with every version, even without inline feedback (hint hint).
Let me know if you think I've forgotten something vital here, or misaligned features.
Here are some other links on the topic of Forms transition to APEX:
When Forms to APEX Projects get thorny - Rodrigo also responding to the AskTom session
Oracle Forms Migration - Some recent thoughts (that need some revision)
APEX 5 Page Designer walkthrough - my prezi session looking specifically at the Page Designer IDE.
AskTom Checklist
Office Hours Q&A
APEX for Forms Developers - Kscope presentation
After listening in on the AskTom Office Hours on this topic (make sure you also read the chat transcript), I had a few ideas for posts to help Forms developers transition to APEX, before my Forms knowledge gets too stale!
If you put the Forms & APEX IDEs together, it's not hard to see they're sown from the same cloth, so to speak.
 |
| Oracle Forms |
Navigator on the left, properties on the right, layout editor in them middle. All with similar behaviours (link to my videos from a while back)
 |
| Oracle APEX 5.1, with custom skin |
Before you begin...
The key thing to remember when transitioning from Forms to APEX is ... empty the cup.
Web behaviour changes some UX fundamentals, but it's an environment in which you should already be rather fluent. So forget about how you build applications in Forms, and think about how you want your website to behave.
Render vs Process
Forms renders the Form to the pixel from compiled source files, and you can show/hide elements on the canvas upon certain triggers firing.
A user visiting a URL in APEX will have their HTML page dynamically generated by PL/SQL, with a variety of conditions on components that decide if they're included in the render.
After the page is delivered to the browser, Dynamic Actions then respond to events that may happen on the page before the next page submit, which allows validation and processing of all the values on the page, or a page redirect, which just opens the target, leaving page data behind.
 |
| Render, Interaction, or Page Process? |
The behavioural difference to remember is that the database knows nothing about the values on the web page until you either
1) submit the page (Processing)
2) execute a PL/SQL dynamic action that submits page items to session state
 |
| PL/SQL Dynamic Action |
Many of your initial problems will probably relate to forgetting to submit/return these values.
Object Navigator Components
Triggers
Straight into a topic with conceptually different behaviours between Forms & APEX. Triggers in Forms drove pretty much everything. You're responding to some event that user has manifested in some way, often driven by moving the cursor.In a stateful environment, this behaviour is fine, and made Forms powerful. In the web environment, these triggers don't have much of a 1-to-1 translation, but the main analog here might be Dynamic Actions.
Many APEX pages might handle processing when you submit the page, so you may have validations, computations, and processes that fire, based on flexible conditions.
Interactive pages may have dynamic actions that respond to specific clicks, but not submit or refresh the entire page, just some of it. This reduces network traffic, and enables clever user experiences.
Alerts
Half your messages may come the process that fires when you submit the page, to be shown on the subsequent page.For instance, you could set the value of a common item P0_RESULT within your process, and display it as the success message using the
&ITEM. substitution syntax. |
| Page Process |
For Dynamic Actions interacting with the database, you may want to display the same style message.
Check out this example from Martin using the Supplied API.
Or for dialog behaviour where you control button naming, you might consider using a plugin such as Alertify or Pretius Enhanced Notifications.
 |
| Alertify Dynamic Action instance |
Attached Libraries
APEX takes care of the JavaScript and CSS libraries that support the Universal Theme, which supports all the components you need for flexible, dynamic applications.You can include your own JavaScript and CSS in several ways, mostly via the page attributes.
 |
| Page CSS attributes |
You can choose to add inline code, reference files that exist either in the database as a BLOB (#APP_IMAGES#), or sitting on the middle tier, typically served by ORDS, perhaps sitting on a Web Logic Server, prefixing the file location with #IMAGE_PREFIX#.
If you use a decent amount of custom JavaScript, you may wish to consider APEX Nitro.
Data Blocks
As a Forms developer, I remember spending a lot of time managing details of the data block, and they generally either broke up the page into chunks of data, or held buttons & hidden items.In APEX, you could say similar things about Regions. Each page is broken up into a bunch of regions, which could come in many forms. Check out the Component Templates available in the Universal Theme.
You can even construct master-detail relationships in APEX. There are no Relations to define, but relationship properties are set within the Region attributes.
Editors
I don't recall using Editors much in Forms, but if you want to handle large objects of the character variety, then you might consider the OraOpenSource plugin.Events
I don't remember using Events, but I believe they allow die-hard Forms developers to extend their application with JavaBeans.If there is something that APEX doesn't do out of the box, it will probably be worth looking to see if anyone has built a plugin for APEX to do so. Some of these are absolute crackers. Once I did a session on consuming plugins.
LOVs
I think the Record Group element is manifested as an LOV definition now, that can be shared in a number of places.An LOV in APEX is more coupled with the Item type. A radio group works well with a small handful of values, Select Lists for middle sized sets, and perhaps the Select2 or I really like the Modal LOV plugin for larger sets to be dynamically queried, while still mastering the keyboard entry.
Or you might even define your own modal page to fetch a value.
There is a native Popup LOV, but I don't like it.
Object Groups
Object Groups and Property Classes in Forms are little more vague than the rest, since this was the sort of thing that would be set up once in a blue moon, utilised many times.I think a comparison for grouping objects in APEX might be item plugins, possibly project specific.
Or maybe blueprints.
Parameters
Page Items are populated between pages to pass information to the next page, such as the selected record in a report. |
| Declarative Page Target |
Larger forms with a number of items are generally submitted as a whole, where the page process handles the data, and branches to the next page.
These values can be protected from URL tampering by session state security, at item, page, and application levels, often by default.
Popup Menus
I've seen explorations of contextual popup menus here, and here.The APEX team have implemented them within the Page Designer - my favourite is the duplicate option.
Program Units
When I used Forms, the general mantra was to keep code out of item/block level events, and manage packaged procedure calls from within program units.But we've all seen Forms written with a spectrum of best practices applied. This is where many of the performance problems could lie, especially poorly used post-query triggers.
For Forms to APEX migration projects, this is probably where most of your gold lies - all the business rules you want to keep. Depending on how well the application is written, you might get to re-use a lot of it, but in my experience, few Forms projects used SmartDB concepts.
If you're aiming for a certain % of re-use, don't aim too high. The code in here can be good guide for the developer in refactoring behaviour, but the APEX page(s) could end up looking and behaviour different to what you had in Forms, if only because you're now in a browser. Thought it could be because you've reviewed the business process since the Form was written 2 decades ago, or you've replaced a lot of the keystrokes with button taps.
Property Classes
Property Classes in Forms allowed the developer to utilise common attributes among each instance of a component.In APEX we can define User Interface Defaults in the data dictionary, so that each time items or reports are created for specific tables or columns, the same features are applied by default.
As for the style of the application, classes could be applied to components that carry a particular look & feel. The Universal Theme has a default skin that can be reconfigured declaratively.
Record Groups
Record groups where just the SQL component of the LOV.Head to an application's Shared Components, then locate LOVs. They can be dynamically driven by a SQL query, or be statically defined. Static definition allows a variety of conditions to be applied to each entry.
These LOVs can then be associated with Items such as Radio Groups & Select Lists, or with a column in a report, to translate a code to a label.
Reports
Reporting in APEX is a deep rabbit hole. Conceptually, all your reporting can now be inline - live.Your power users can be given Interactive Reports with a number of runtime manipulation options.
Oracle JET charts can respond to clicks, allowing drill down functionality, in whatever way you want.
Or you could go down the yellow brick road and choose a tool to dynamically generate PDF documents, Excel spreadsheets, Word files etc.
Visual Attributes
Template Options seem to be a fair comparison here, where common group of settings can be tailored to each instance of a component.Windows
Application workflow differs between Forms and APEX due to the nature of their environments.In APEX most interactions happen within the browser window, within an inline modal (just a special looking page region), within a page modal. From APEX 5.x this became native and easy, allowing modals to have validations and branches, just like standard pages.
Menus
Forms had specific menu files, controlled by database roles, and there had to be no active users to be able to update the .mmx file.The menu in APEX can either be across the top, or down the left side. These menus can be statically defined, or dynamically driven, even by the meta-data that is apex_application_pages view. (my link)
Static navigation entries could be controlled by authorisation schemes, or custom conditions.
Dynamic menus can have security tables integrated within the SQL.
There are a few list templates that follow the same data structure, just rendered in different ways.
There is a smaller Navigation Bar at the top right, typically starting with the login/logout buttons.
Canvas
Forms is WYSIWYG, I'm sorry for your loss. An APEX page needs to be rendered in a variety of devices sizes, so items go where the responsive templates tell them to go. You can impact this using the grid layout, but it can take a little while to get the hang of it. Showing the columns helps, particularly with item layout. |
| Show Layout Columns |
Properties
The Page Designer introduced in APEX 5.0 is rather reminiscent of Oracle Forms, particularly with regard to the ability to edit multiple components at once, only intersecting attributes, and obscuring heterogenous values.As with Forms, finding the right property can be tricky, or even being aware of its existence.
I highly recommend you take note of the inline help associated with each attribute. I'm sure this content improves with every version, even without inline feedback (hint hint).
 |
| Inline Attribute Help |
Let me know if you think I've forgotten something vital here, or misaligned features.
Here are some other links on the topic of Forms transition to APEX:
When Forms to APEX Projects get thorny - Rodrigo also responding to the AskTom session
Oracle Forms Migration - Some recent thoughts (that need some revision)
APEX 5 Page Designer walkthrough - my prezi session looking specifically at the Page Designer IDE.
AskTom Checklist
Office Hours Q&A
APEX for Forms Developers - Kscope presentation
Wednesday, 19 September 2018
Remove duplicate from APEX collection
One of my favourite SQL analytic functions is
In this case, I have an APEX collection that represents a session based view history of products/people/events, or whatever your users might be browsing.
I've created an option to consolidate that view history, and remove any record you might have opened more than once.
Collections are a little hard to play with outside of APEX, so I use the create session procedure in the OraOpenSource libraries to simulate an APEX session within SQL Developer.
For my test case I simulate adding a few entries in my collection, varying a date column slightly.
Here is a query that will use an analytical function to add a computed column that identifies the most recent entry for any name, and assign it a 1. Any subsequent entries for that name will get a 2, 3, 4 etc.
In this case, seq_id 1, 5 & 6 represent the records I want to trim from the list.
Turn the query into an in-line view to filter out any records where the row_number() is not 1, as we are not allowed to have window functions in the where clause (ORA-30483).
Reverse the order by, and I'll keep the first entry instead.
Got more columns that signify uniqueness? Expand the partition by clause.
A standard aggregate query would be able to identify the names of those duplicates, plus how many you have, but no set of records with the unique identifiers to remove. Using
We can fold the analytical query into a PL/SQL loop and remove the duplicates from my collection.
Where the resulting collection is minus the older duplicate.
Of course, this is not limited to APEX collections, but it gave me a chance to play with more toys.
row_number(), and I've used it in the past to identify, then remove duplicates.In this case, I have an APEX collection that represents a session based view history of products/people/events, or whatever your users might be browsing.
I've created an option to consolidate that view history, and remove any record you might have opened more than once.
Collections are a little hard to play with outside of APEX, so I use the create session procedure in the OraOpenSource libraries to simulate an APEX session within SQL Developer.
exec oos_util_apex.create_session(120,'WESLEYS')For my test case I simulate adding a few entries in my collection, varying a date column slightly.
begin
apex_collection.create_or_truncate_collection('TEST');
apex_collection.add_member('TEST', 'Scott', p_d001 => sysdate - 5);
apex_collection.add_member('TEST', 'Dmitri', p_d001 => sysdate - 2);
apex_collection.add_member('TEST', 'Lino', p_d001 => sysdate - 4);
apex_collection.add_member('TEST', 'Scott', p_d001 => sysdate - 3);
apex_collection.add_member('TEST', 'Joel', p_d001 => sysdate - 2);
apex_collection.add_member('TEST', 'Sabine', p_d001 => sysdate - 4);
apex_collection.add_member('TEST', 'Penny', p_d001 => sysdate - 5);
apex_collection.add_member('TEST', 'Scott', p_d001 => sysdate - 6);
apex_collection.add_member('TEST', 'Jackie', p_d001 => sysdate - 3);
apex_collection.add_member('TEST', 'Joel', p_d001 => sysdate - 1);
end;
/
Here is a query that will use an analytical function to add a computed column that identifies the most recent entry for any name, and assign it a 1. Any subsequent entries for that name will get a 2, 3, 4 etc.
select seq_id, c001 name
,row_number() over
(partition by c001 -- look for duplicates in this set of columns
order by d001 desc -- put records I want to keep first
) rn
,d001 dt
from apex_collections
where collection_name = 'TEST'
order by name;
In this case, seq_id 1, 5 & 6 represent the records I want to trim from the list.
 |
| Duplicate entries highlighted |
select seq_id, rn from
(select seq_id, row_number() over (partition by c001 order by d001 desc) rn
from apex_collections
where collection_name = 'TEST'
) where rn != 1;
SEQ_ID RN
---------- ----------
6 2
1 2
5 3
Reverse the order by, and I'll keep the first entry instead.
Got more columns that signify uniqueness? Expand the partition by clause.
A standard aggregate query would be able to identify the names of those duplicates, plus how many you have, but no set of records with the unique identifiers to remove. Using
min(seq_id) would be insufficient once you more than two entries.select count(*), c001 name, min(seq_id) from apex_collections where collection_name = 'TEST' group by c001 having count(*) > 1 C NAME MN --- ---------- --- 2 Joel 6 3 Scott 1
We can fold the analytical query into a PL/SQL loop and remove the duplicates from my collection.
begin
<< remove_duplicates >>
for r_rec in (
select seq_id, rn from
(select seq_id, row_number() over (partition by c001 order by d001 desc) rn
from apex_collections
where collection_name = 'TEST'
) where rn != 1
) loop
APEX_COLLECTION.DELETE_MEMBER
(p_collection_name => 'TEST'
,p_seq => r_rec.seq_id);
end loop remove_duplicates;
end anon;
/
Where the resulting collection is minus the older duplicate.
SEQ_ID NAME RN DT
---------- ---------- --- ----------
2 Dmitri 1 2018-09-16
9 Jackie 1 2018-09-15
10 Joel 1 2018-09-17
3 Lino 1 2018-09-14
8 Penny 1 2018-09-13
7 Sabine 1 2018-09-14
4 Scott 1 2018-09-15
Of course, this is not limited to APEX collections, but it gave me a chance to play with more toys.
Monday, 17 September 2018
Hide region if no data found
I have a diagnostic page where I hide classic report regions that aren't relevant - ie, have no data returned.
Here I create a dynamic action that executes After Refresh of the relevant region.
The client-side condition evaluates the presence of the
The dynamic action then shows/hides the region, depending on the result of the JavaScript expression:
The triggering element is the region, so find the class within that region, count the result set, and compare to value 1.
Instead of the region itself, this could relate to other components only relevant when records are returned.
As Maxime pointed out in the comments, here I was focussing on classic report regions. The relevant classes you may be looking for include:
This can mean there is no need for a server-side condition to test for existence.
With this dynamic action, the region could re-appear upon refresh with results, making the page more interactive without requiring full page submission.
Here I create a dynamic action that executes After Refresh of the relevant region.
 |
| Dynamic Action definition |
The client-side condition evaluates the presence of the
.nodatafound class within a classic report, which is present only when no records are returned. |
| Inspect element of region with no records |
The dynamic action then shows/hides the region, depending on the result of the JavaScript expression:
$(this.triggeringElement).find('.nodatafound').length == 1The triggering element is the region, so find the class within that region, count the result set, and compare to value 1.
Instead of the region itself, this could relate to other components only relevant when records are returned.
As Maxime pointed out in the comments, here I was focussing on classic report regions. The relevant classes you may be looking for include:
- Classic Report (versatile):
nodatafound - Interactive Report (IR):
a-IRR-noDataMsg - Interactive Grid (IG):
a-GV-noDataMsg
This can mean there is no need for a server-side condition to test for existence.
With this dynamic action, the region could re-appear upon refresh with results, making the page more interactive without requiring full page submission.
Friday, 14 September 2018
Add record count to collapsed region
I have a diagnostics page where I wanted to display how many records are in a collapsible region's title.
The following is a simple solution, but will only work properly if all records are displayed, and no pagination is used.
Otherwise, check this past post for alternative methods to get the number of rows in a region.
Pick a column in the region and add a class. I chose "cnt".
To be selective, it's always handy to add a static ID to the region.
Now we're ready to create an After Refresh Dynamic Action on the region, executing the following JavaScript, also firing on initialisation
It will relabel region title with the amount of
The final result looks like this:
An alternative that skips the need for a static region ID would be
$(this.trigginerElement).find('.t-Region-title')
.text('Sharing ('+$(this.trigginerElement).find('.cnt').length+')');
Edit - Trent offered a more declarative example.
Relatively simple, but effective.
The following is a simple solution, but will only work properly if all records are displayed, and no pagination is used.
Otherwise, check this past post for alternative methods to get the number of rows in a region.
Pick a column in the region and add a class. I chose "cnt".
 |
| Column attribute |
To be selective, it's always handy to add a static ID to the region.
 |
| This is not an 'advanced' feature |
Now we're ready to create an After Refresh Dynamic Action on the region, executing the following JavaScript, also firing on initialisation
$('#p99_share .t-Region-title').text('Sharing ('+$('#p99_share .cnt').length+')');It will relabel region title with the amount of
.cnt classes it could find in the region.The final result looks like this:
 |
| Collapsible region with modified title |
An alternative that skips the need for a static region ID would be
$(this.trigginerElement).find('.t-Region-title')
.text('Sharing ('+$(this.trigginerElement).find('.cnt').length+')');
 |
| After Refresh Dynamic Action |
Relatively simple, but effective.
Monday, 10 September 2018
Enhancing the APEX Error Handling Function with Logger
Many, many moons ago, I created an error handling function for Oracle APEX, just like the original sample provided by Patrick Wolf for the 4.1 error handling feature. You'll probably find a strong correlation between the two events.
We normally see this if an exception was propagated within a PL/SQL Dynamic Action.
In this case I thought it was time for a little upgrade.
I noticed the sample code, now part of the APEX_ERROR documentation, suggested including a reference ID, perhaps from some logging package.
Conveniently, we use such a package, a popular one among the PL/SQL community called Logger.
I glanced through the package specification, and for a moment I thought it was missing a function that returned the new log ID.
I found a procedure with an OUT parameter instead, so here I log some contextual information about the error.
So if we were to execute the following SQL, we could see further details.
I also wanted to add some convenience to the developer, so I added this same information to the error popup, but only when an active session is also present within the App Builder (or some special privilege present).
This is toggled by checking a built in substitution string (or my application item). I note this built-in was only documented from 18.1, but I believe it has been present for a while. It's certainly returns a value in 5.1.
 |
| OK, now what? |
In this case I thought it was time for a little upgrade.
I noticed the sample code, now part of the APEX_ERROR documentation, suggested including a reference ID, perhaps from some logging package.
Conveniently, we use such a package, a popular one among the PL/SQL community called Logger.
I glanced through the package specification, and for a moment I thought it was missing a function that returned the new log ID.
I found a procedure with an OUT parameter instead, so here I log some contextual information about the error.
logger_user.logger.ins_logger_logs(
p_unit_name => 'error_handler' ,
p_scope => 'apx_util.error_handler' ,
p_logger_level => logger.g_error,
p_extra => p_error.component.name
||'~'||p_error.component.type
||'~'||p_error.message,
p_text => 'apx_util.error_handler()',
p_call_stack => dbms_utility.format_call_stack,
p_line_no => null,
po_id => l_reference_id
);Now the user has some context to report. I figured the wording could be softened a little, too ;p |
| Standard error message, with reference |
So if we were to execute the following SQL, we could see further details.
select time_stamp, module, client_identifier, extra from logger_user.logger_logs where id = 12292314;The log message prior to this ID may also help to provide clues as to the problem.
 |
| SQL results, slightly redacted |
I also wanted to add some convenience to the developer, so I added this same information to the error popup, but only when an active session is also present within the App Builder (or some special privilege present).
 |
| Immediate context for the developer |
This is toggled by checking a built in substitution string (or my application item). I note this built-in was only documented from 18.1, but I believe it has been present for a while. It's certainly returns a value in 5.1.
l_result.message := 'We had a problem completing this request. '||
'Please contact IT Support'||
' for further investigation. Reference: '||l_reference_id
||case when v('F_SEC_DEV') = 'Y' -- anyone with privilege
-- or has builder open (from oos_util_apex.is_developer)
or coalesce(apex_application.g_edit_cookie_session_id
,v('APP_BUILDER_SESSION')) is not null then
' Dev only: '||p_error.component.name
||' ~ '||p_error.component.type
||' ~ '||p_error.message
end;This brightened the day of some of my colleagues.
Wednesday, 5 September 2018
Client Side Dynamic Actions using jQuery Selectors
Consider a data entry page where it might be nice to capitalise the first letter of a person's name, for a number of fields.
I understand I may be anglicising a problem that contains minutia, but focus instead on the thought processes and options we have available using Dynamic Actions.
Let's say we want to create a dynamic action that responds to change on any of those name fields, then runs some JavaScript to apply sentence-case to the name value, before the user submits the page.
We can do this declaratively just using the mouse, APEX will construct a comma delimited list for you as you select page items from the list.
But that's not the only way we can nominate components on a web page. The example above might create a selector for those items that is a comma delimited list of IDs.
When we have a look at the underlying HTML defined for these items using Inspect Element, the selector would locate these fields based on the
Alternatively, we could use classes. This is how web developers of any ilk build their web pages. They make up a string that means something to them, then associate some behaviour with it.
You don't need to "define" a class anywhere, but it pays to ensure it's unique, and follows a standard.
In our case, if we could add a class to each item, then we would only need to list one class in the dynamic action.
In some circumstances, this style of coupling behaviour could be more advantageous.
To make "initcap" appear as a class, as shown in the item as highlighted above, add the string to the 'CSS Classes' in the Advanced section of the item properties.
APEX Page Builder makes this task quicker, thanks to behaviour inspired by Oracle Forms - well, some of us ex-Forms programmers will recognise it as such.
If you select multiple items, you can change some properties in bulk.
Over in the properties, we can modify CSS Classes attribute for all three items at once.
Note the 'Value Placeholder' attribute - this is different for each field, so the delta symbol is shown with the attribute value blued-out. I love this concept.
So back on the dynamic action, we can change the 'Selection Type' to jQuery Selector, and reference the class with the relevant syntax - prefixed with a full-stop, as opposed to the hashtag for IDs.
We also didn't want this behaviour to apply only when all the letters are still lower-case. If the user wants to enter "von Braun", I won't overwrite their particular usage.
So note the client-side condition, emphasis on the 'client'. Most conditions on APEX components are server-side, which generally means they are evaluated during page render - do we or do we not include this button/region/item? Dynamic actions have client side conditions to decide whether to apply the True actions, or the False actions.
Here I've referred to the item being triggered using this.triggeringElement, mentioned in the inline help for this particular attribute. Wrapping that expression with $().val() gives me the item value, or I could have used apex.item().getValue. jQuery built in .toLowerCase() does what you would hopefully infer.
As with all things programming, there are many ways to cook an egg, and this goes for setting the value. Here I've define a Set Value action that uses a JavaScript Expression, as there is no need for a round trip to the database server.
This expression uses a function I found on Stack Overflow that I was happy with
I placed in an application level JavaScript file, included via User Interface attributes of the application.
Don't let a little JavaScript scare you away from enabling useful interactivity for the end user. Dynamic actions do most of the work for you :p
I understand I may be anglicising a problem that contains minutia, but focus instead on the thought processes and options we have available using Dynamic Actions.
Let's say we want to create a dynamic action that responds to change on any of those name fields, then runs some JavaScript to apply sentence-case to the name value, before the user submits the page.
We can do this declaratively just using the mouse, APEX will construct a comma delimited list for you as you select page items from the list.
 |
| Declarative item selection |
But that's not the only way we can nominate components on a web page. The example above might create a selector for those items that is a comma delimited list of IDs.
$('P18_FIRST_NAME,#P18_MIDDLE_NAME,#P18_LAST_NAME');When we have a look at the underlying HTML defined for these items using Inspect Element, the selector would locate these fields based on the
id="P18_FIRST_NAME" |
| Inspect Element to see underlying details of page components |
Alternatively, we could use classes. This is how web developers of any ilk build their web pages. They make up a string that means something to them, then associate some behaviour with it.
You don't need to "define" a class anywhere, but it pays to ensure it's unique, and follows a standard.
In our case, if we could add a class to each item, then we would only need to list one class in the dynamic action.
In some circumstances, this style of coupling behaviour could be more advantageous.
To make "initcap" appear as a class, as shown in the item as highlighted above, add the string to the 'CSS Classes' in the Advanced section of the item properties.
If you select multiple items, you can change some properties in bulk.
Over in the properties, we can modify CSS Classes attribute for all three items at once.
 |
| Multi-item select in Page Designer |
Note the 'Value Placeholder' attribute - this is different for each field, so the delta symbol is shown with the attribute value blued-out. I love this concept.
.initcap |
| Dynamic action, only when item value all lower case |
We also didn't want this behaviour to apply only when all the letters are still lower-case. If the user wants to enter "von Braun", I won't overwrite their particular usage.
So note the client-side condition, emphasis on the 'client'. Most conditions on APEX components are server-side, which generally means they are evaluated during page render - do we or do we not include this button/region/item? Dynamic actions have client side conditions to decide whether to apply the True actions, or the False actions.
$(this.triggeringElement).val().toLowerCase() == $(this.triggeringElement).val()Here I've referred to the item being triggered using this.triggeringElement, mentioned in the inline help for this particular attribute. Wrapping that expression with $().val() gives me the item value, or I could have used apex.item().getValue. jQuery built in .toLowerCase() does what you would hopefully infer.
As with all things programming, there are many ways to cook an egg, and this goes for setting the value. Here I've define a Set Value action that uses a JavaScript Expression, as there is no need for a round trip to the database server.
 |
| True Action - apply change to item value |
This expression uses a function I found on Stack Overflow that I was happy with
toProperCase($(this.triggeringElement).val())I placed in an application level JavaScript file, included via User Interface attributes of the application.
// Turn mcdonald into McDonald
// only run/apply if current string lowercase
// $(this.triggeringElement).val().toLowerCase() == $(this.triggeringElement).val()
function toProperCase(s)
{
return s.toLowerCase().replace( /\b((m)(a?c))?(\w)/g,
function($1, $2, $3, $4, $5) { if($2){return $3.toUpperCase()+$4+$5.toUpperCase();} return $1.toUpperCase(); });
}
Most of the JavaScript usage I have in my applications these days tend to be one line, or is some form of expression, so commonly used there is only a small handful.Don't let a little JavaScript scare you away from enabling useful interactivity for the end user. Dynamic actions do most of the work for you :p
Thursday, 23 August 2018
Pseudo Radiogroup in APEX Report
I'd be surprised if you've ever tried to put a radio group in a report, but if you've ever attempted it you might come across a post from Vincent Deelan.
When it comes to checkboxes and radio groups, the nature of HTML haunts us.
So it turns out it's possible to do the same task within a report, and we can build it so we don't even need to submit the page. It really just depends on how you want the page to interact. The following example simply logs the rating selected, rather than a rating being stored as an attribute of Emp.
This is no doubt even easier with Interactive Grids, but I'm not there yet, and I'm sure others aren't either.
And it's fun using Dynamic Actions to make applications more interactive, and revive draft blog posts from a good 18 months earlier. (I've got a few of these...)
I built a traffic light style report once before, but leveraging off the Universal Theme made this effort so much easier.
Consider the template options for creating a pill button group using standard button components.
If you create three buttons, all with first, inner, last button sets respectively, you get something that looks like this:
I refer to them as such because if you use the Inspect Element tool on these buttons, the template option classes are named as such
Check out the UT button builder for more examples.
So to utilise this in a report, I'll combine three manually constructed buttons, levering other existing classes.
If you remember to Escape Special Characters for the rate columnm you'll see something like this
Now you could slice and dice the dynamic actions in a number of ways, but here I created one for each type of button. So on click of the relevant class I associated with each button type, it will get the value from the data- attribute, put it in a hidden item, then insert a record.
The Set Value action would set some hidden item using the JavaScript expression
The subsequent PL/SQL process would submit this item to session state, and insert the empno/rating selection into the log table. The third action refreshes the Rating region so we can see the new data.
We can make the buttons more pill-like but adding a border radius
To dull the colours a touch
It would only take a few more lines of jQuery to turn this into a status, leaving the active button highlighted. Plus a slight tweak to the SQL to match the relevant record.
Obviously I had a demo of this, but this draft was so old I've forgotten where I put it ;p
When it comes to checkboxes and radio groups, the nature of HTML haunts us.
So it turns out it's possible to do the same task within a report, and we can build it so we don't even need to submit the page. It really just depends on how you want the page to interact. The following example simply logs the rating selected, rather than a rating being stored as an attribute of Emp.
This is no doubt even easier with Interactive Grids, but I'm not there yet, and I'm sure others aren't either.
And it's fun using Dynamic Actions to make applications more interactive, and revive draft blog posts from a good 18 months earlier. (I've got a few of these...)
I built a traffic light style report once before, but leveraging off the Universal Theme made this effort so much easier.
Consider the template options for creating a pill button group using standard button components.
 |
| Button Template Options |
If you create three buttons, all with first, inner, last button sets respectively, you get something that looks like this:
 |
| Pill buttons |
t-Button--pillStartCheck out the UT button builder for more examples.
So to utilise this in a report, I'll combine three manually constructed buttons, levering other existing classes.
select EMPNO,
ENAME,
JOB,
'<span style="white-space: nowrap;">'
||'<a href="javascript:void(0);" data-emp="'||empno||'"'
||' class="high t-Button t-Button--success t-Button--simple t-Button--pillStart">H</a>'
||'<a href="javascript:void(0);" data-emp="'||empno||'"'
||' class="medium t-Button t-Button--warning t-Button--simple t-Button--pill">M</a>'
||'<a href="javascript:void(0);" data-emp="'||empno||'"'
||' class="low t-Button t-Button--danger t-Button--simple t-Button--pillEnd">L</a>'
||'</span>' rate
from scott.emp
If you remember to Escape Special Characters for the rate columnm you'll see something like this
Now you could slice and dice the dynamic actions in a number of ways, but here I created one for each type of button. So on click of the relevant class I associated with each button type, it will get the value from the data- attribute, put it in a hidden item, then insert a record.
The Set Value action would set some hidden item using the JavaScript expression
$(this.triggeringElement).data('emp')The subsequent PL/SQL process would submit this item to session state, and insert the empno/rating selection into the log table. The third action refreshes the Rating region so we can see the new data.
We can make the buttons more pill-like but adding a border radius
span a.t-Button {border-radius: 10px;}
To dull the colours a touch
span a.t-Button{border-color: lightgrey; box-shadow: 0 0 0 1px lightgrey inset !important;}
It would only take a few more lines of jQuery to turn this into a status, leaving the active button highlighted. Plus a slight tweak to the SQL to match the relevant record.
Obviously I had a demo of this, but this draft was so old I've forgotten where I put it ;p
Wednesday, 1 August 2018
APEX 18.2 Statement of Direction
I've been thinking recently it's been a while since I remember seeing a revised Statement of Direction, and sure enough I see news of an update for 18.2.
It was back in 2015 that I last made my own conjecture about what each statement means (without the benefit of listening to as many conference sessions) and look at some of the outcomes now!
As for APEX 18.2, we now see:
It was back in 2015 that I last made my own conjecture about what each statement means (without the benefit of listening to as many conference sessions) and look at some of the outcomes now!
- IG - well, I'm still a little late to that party, but missing out on early cuts & bruises ;p
- Master detail detail - I was training people last week and it occurred to me this is another feature I don't really work with, but maybe should give a go.
- New charting engine - well JET is going really well! Though I'm still experiencing teething issues with the data densification.
As for APEX 18.2, we now see:
- Improved workspace provisioning wizard - not a big drawcard for myself, but I can see how some fresh attention in this space could be warranted.
- New side-by-side master detail page type available in create page wizard - as I just noted, I need to experiment with recent master-detail development, to perhaps integrate with regular design patterns.
- New dashboard page type available in create page wizard - Now I’m guessing this is just a wizard to help build an appropriate page – full of components already available to us, but used in an effective way. Either way, I'm interested.
- Improved warnings with REST workshop to prevent loss of custom definitions - well, that sounds useful.
- More comprehensive JavaScript API documentation - even more? I've barely had a chance at using the new set. I hope it comes with more examples. I think I saw a glimpse of Shakeeb's /ut application the other night with SQL examples inline for classic report variations. +1 from me.
- Ability to update Font APEX stylesheets and font files independent of Oracle APEX releases - Maxime already showed us this was possible, and easy. I'm glad behaviour like this is enabled by the APEX team.
- Installing sample datasets now enables the creation of a complete sample application - well, that sounds intriguing.
- EMP / DEPT sample dataset now available in different languages - sometimes I'm grateful my first language is English, but sometimes I really wish I was natively bilingual.
- Updated productivity and sample apps - seems to go without saying now, but I haven't farmed them for ideas for a while.
They've always come through pretty well with these lists, plus a whole bunch of other extra stuff we find along the way. I also found this nugget from Oliver.
After recent experiences I thought I'd add a few examples of what I'd like to see, no doubt forgetting/neglecting some other needs & wants, includes:
- IR - support for saved IR (& IG?) are still hidden away a few layers deep in a task menu. Saved IR are still inseparable from app_id. We could use some love here.
- Excel2Collection - considering the efforts made in assisting Access & Forms users to migrate their information to APEX, perhaps it might be wise to integrate such a simple method of transferring Excel workbook content to a table in the database.
The ORDS solution hung around for a while, and I find the data loading wizard somewhat clunky. - PWA - Vincent recently published a comprehensive guide on turning APEX into a PWA. This technology is early days (certainly in my learning bubble), but I think APEX would benefit from internal support in regard to further penetrating the mobile market.
- Native Super LOV - this one from Menno is great, but surely it's time this was baked in?
- Dynamic Action support for inline modals - there are a few lines of JavaScript I repeat often that I'm sure could be replaced by declarative actions
Wednesday, 11 July 2018
Oracle Forms Migration
This post is one of a series on what I learned while not at Kscope18.
I realise that's a month ago now, but the mind still ponders, and I've had these lined up for a while. Just got busy with a deployment, as you do. Went nice and smooth though, APEX sure does make staggered deployments easier with build options. Underrated feature. /tangent
Anyhoo - every now and then someone on the forum asks about the Oracle Forms migration tool. I've noticed it a bit recently, I guess that's a good thing.
Oracle Forms Migration. I guess what I'm learning here is that sometimes I'm a little shocked they keep at it, but I guess you've got to play the odds. And I haven't heard what was said with this slide.
I'd like to start and alternate list:
1) Setup APEX. Of course. OracleRAD will make this a breeze.
2) Create a schema that will serve as your conduit to your existing data. Privileges become additive, adding another layer to protect your data, which may include information not for the relevant APEX application.
3) Analyse what needs to change, what's broken, what's required.
Important note - workflow in a browser will be different from Oracle Forms.
While the components, capabilities, and IDE are astonishingly similar, I would never bother converting Form->Page. You'll spend more time cleaning when you could be doing more productive building.
The web universe just works different to Forms. It offers different opportunities. For instance, it's easier to build walk-through wizards for data entry. This can be important in touch device world, offering interaction with buttons instead of virtual keyboard is a plus.
Our applications now tend to generally require less keystrokes for information to be added, it's more of an interaction of buttons.
4) Import your Forms code if you must, but only for the ability to annotate existing code while you build the fresh interface. You may get better mileage by analysing straight from Oracle Forms.
The spectrum of code you'll need to ditch/refactor/keep will vary depending on the quality of the legacy application.
Continue analysis of business rules. Are they fully known? stale? Does workflow need improvement? Maybe concessions were made during the Forms development.
5) Enhance - not always necessary, but always good to add polish. But this notion of RAD? Yep, you get pretty darn good apps without even trying to add polish. And polish with a few Dynamic Actions go a long way.
And with APEX plugins, polish comes in big, free tubs. And yes, you should embrace plugins.
6) Test. Of course. And with clicky people. People who click about for the sake of clicking stuff. They're the people that are going to find those left field holes.
7) Train. While you can never replace face-to-face explanations, hopefully your application will explain itself.
8) Rollout, and enjoy applications that aren't easily obstructed as new versions of APEX arrive.
Applications that run fast, derived directly from business data, data delivered to the right people, with a natural history of who visited what and how long it took them.
We've got a keeper.
I realise that's a month ago now, but the mind still ponders, and I've had these lined up for a while. Just got busy with a deployment, as you do. Went nice and smooth though, APEX sure does make staggered deployments easier with build options. Underrated feature. /tangent
Anyhoo - every now and then someone on the forum asks about the Oracle Forms migration tool. I've noticed it a bit recently, I guess that's a good thing.
From @orcl_dpeake presentation. I think there is a step „do some magic“ missing. pic.twitter.com/h4Bgyy2fm5— PeterRaganitsch (@PeterRaganitsch) June 13, 2018
Oracle Forms Migration. I guess what I'm learning here is that sometimes I'm a little shocked they keep at it, but I guess you've got to play the odds. And I haven't heard what was said with this slide.
I'd like to start and alternate list:
1) Setup APEX. Of course. OracleRAD will make this a breeze.
2) Create a schema that will serve as your conduit to your existing data. Privileges become additive, adding another layer to protect your data, which may include information not for the relevant APEX application.
3) Analyse what needs to change, what's broken, what's required.
Important note - workflow in a browser will be different from Oracle Forms.
While the components, capabilities, and IDE are astonishingly similar, I would never bother converting Form->Page. You'll spend more time cleaning when you could be doing more productive building.
Our applications now tend to generally require less keystrokes for information to be added, it's more of an interaction of buttons.
4) Import your Forms code if you must, but only for the ability to annotate existing code while you build the fresh interface. You may get better mileage by analysing straight from Oracle Forms.
The spectrum of code you'll need to ditch/refactor/keep will vary depending on the quality of the legacy application.
Continue analysis of business rules. Are they fully known? stale? Does workflow need improvement? Maybe concessions were made during the Forms development.
5) Enhance - not always necessary, but always good to add polish. But this notion of RAD? Yep, you get pretty darn good apps without even trying to add polish. And polish with a few Dynamic Actions go a long way.
And with APEX plugins, polish comes in big, free tubs. And yes, you should embrace plugins.
6) Test. Of course. And with clicky people. People who click about for the sake of clicking stuff. They're the people that are going to find those left field holes.
7) Train. While you can never replace face-to-face explanations, hopefully your application will explain itself.
8) Rollout, and enjoy applications that aren't easily obstructed as new versions of APEX arrive.
Applications that run fast, derived directly from business data, data delivered to the right people, with a natural history of who visited what and how long it took them.
We've got a keeper.
Monday, 18 June 2018
Free Oracle Learning Tools
This post is one of a series on what I learned while not at Kscope18.
Would you like to learn something from the Oracle technology stack?
Here's a slide probably in a bunch of Oracle employee decks.
I think this collection represents the commitment Oracle is making to the developer community, in part thanks to the developer advocates team. Upon seeing these listed together, and considering the amount of work done to keep raising the bar, I've learned Oracle really are serious about engaging with the greater community.
These are all included in the APEX shortcuts page, along with another learning tool that could fill that 6th spot (besides 18c XE) - the forum. Here I do what I used to do at AskTom - read anything comes through; start listening to topics I need/want to learn about; and here I also get to contribute where/when I can add some of my knowledge & experience. Or if I have a bookmark to a solution someone previously documented.
At the forum you'll find a collection of volunteers willing to help you with your APEX/Oracle questions. Remember, "the #orclapex community is like no other".
ps - we could probably also add the Oracle Learning Library.
Would you like to learn something from the Oracle technology stack?
Here's a slide probably in a bunch of Oracle employee decks.
— Oracle Over Coffee (@oraclecoffee) June 10, 2018
I think this collection represents the commitment Oracle is making to the developer community, in part thanks to the developer advocates team. Upon seeing these listed together, and considering the amount of work done to keep raising the bar, I've learned Oracle really are serious about engaging with the greater community.
- quicksql.oracle.com - one of the packaged apps given it's own URL. Construct your DDL/DML really fast, if you're happy to learn more markdown syntax.
- livesql.oracle.com - it's ridiculously easy to start playing with the Oracle product at this site. Recently I wanted to check some behaviour with a JSON query in the database version above what we had available, and I was able to confirm a database upgrade would solve the problem we had.
- devgym.oracle.com - I spend 5-10 minutes a week playing 3 quizzes, targeting PL/SQL, SQL, and database design. Sometimes I learn something new, something re-enforce something I'm familiar with, something I'd forgotten, or something interesting the database can do but I might never use.
- apex.oracle.com - surely we've all been here, playing with the most up-to-date release of the best feature of the a great database.
- asktom.oracle.com - I learned all my best habits from here, and highly recommend anyone ask a question. Especially anyone learning (hint - that's all of us). It's also a great place to learn how to ask a question.
These are all included in the APEX shortcuts page, along with another learning tool that could fill that 6th spot (besides 18c XE) - the forum. Here I do what I used to do at AskTom - read anything comes through; start listening to topics I need/want to learn about; and here I also get to contribute where/when I can add some of my knowledge & experience. Or if I have a bookmark to a solution someone previously documented.
At the forum you'll find a collection of volunteers willing to help you with your APEX/Oracle questions. Remember, "the #orclapex community is like no other".
ps - we could probably also add the Oracle Learning Library.
Thursday, 14 June 2018
Community Recognition at Kscope18
This post is one of a series on what I learned while not at Kscope18.
Some well deserved people have been recognised by the Oracle ACE program. One in particular I noticed was Daniel, creator of many practical APEX plugins.
Other mentions include, but not limited to, Adrian Png, Maxime Tremblay, Kai Donato, Moritz Klein, Becky Wagner, Eugene Fedorenko, Opal Alapat.
People like these really help keep our community strong, as Monty says,
And Juergen Schuster, createor of apex.world, received special recognition from the APEX community.
It was a pleasure to meet this man, and I really need to catch up on his APEX podcast.
To everyone, a very Australian good onya!
Some well deserved people have been recognised by the Oracle ACE program. One in particular I noticed was Daniel, creator of many practical APEX plugins.
— Daniel Hochleitner (@Dani3lSun) June 13, 2018
Other mentions include, but not limited to, Adrian Png, Maxime Tremblay, Kai Donato, Moritz Klein, Becky Wagner, Eugene Fedorenko, Opal Alapat.
People like these really help keep our community strong, as Monty says,
"The #orclapex community is like no other."
And Juergen Schuster, createor of apex.world, received special recognition from the APEX community.
Passionate and selfless. That describes @JuergenSchuster. We are proud to honor him with the first ever APEX Distinguished Community Member Award. #orclapex pic.twitter.com/PVPdavNUv9— Joel R. Kallman (@joelkallman) June 10, 2018
It was a pleasure to meet this man, and I really need to catch up on his APEX podcast.
To everyone, a very Australian good onya!