Skip navigation

Oracle Forms and Oracle Application Express (APEX) are both technologies used to build data centric applications.

APEX uses the best of the modern web to create native web applications. Oracle Forms uses NPAPI plugins relying on Java technologies to render the page. Whilst we could go on and on describing the advantages of one over the other and believe us, there are strong opinions on both sides. We, as APEX enthusiasts, acknowledge that whilst many a Forms-to-APEX redevelopment project can be a trouble-free experience – there are some Oracle Forms features that makes the redevelopment a bit sticky.

Many of these thorny features are due to browser security vs. a standalone client-server application and some are just old-school features which don’t have an easy equivalent in a web application.

Let’s start with a list of Webutil features which cause a problem within APEX. The vast majority of Webutil features are going to be difficult to overcome in APEX. If you’ve never heard of Webutil, it’s an Oracle Forms Library that was built to provide access to modules which typically interact with the clients PC e.g. file system, environment variables, etc. If you try to think of any web page which tries to move files around, start programs or read information about the client PC well… that sounds a little scary, but luckily due to browser security we are protected. It’s that same browser security which prevents an APEX page from leaping outside of the browser performing those undesirable actions.

Let’s look at our list…

Running the Host command: Once again, due to browser security, we are unable to run a host command from a web application, and some Oracle Forms needed it to run simple Microsoft windows apps like a calculator or even execute .bat files as part of their functionality.

Manipulate client side files: Typically functionality includes, creating a folder on the clients HDD, checking if it exists and creating files in that folder. It’s not uncommon to have this functionality within an Oracle Forms application, and would definitely not be the norm within a web page.

Read information from the client machine (Tool_Env): We can easily authenticate on an APEX application using Active Directory however the browser cannot easily detect the user currently logged to the OS (without some trickery). The username and other client data are not accessible from the browser.

Read and write client side images: Within Oracle Forms, you can easily read and write images on the client side without any user interaction.

Integrate with client side OLE2 (e.g. Word and Excel): This feature enables Oracle Forms to open a local copy of Word/Excel on the clients machine, get and set values, and provides many other features including saving and printing. For the same reasons above, your web browser will restrict any OLE2 attempts from APEX; however a typical workaround involves moving that code to the Database and have the Database Server interact with  local copies of Word/Excel. (Its not recommended)

In Addition to WebUtil there are some general challenges when comparing a standalone client side application (Forms) to a Web Browser one (APEX).

Print Screen: This takes a picture of your Oracle Form and sends it to the printer; whilst attempting the same in APEX (or any web page) would send a responsive A4 layout version to the printer.

Java Beans: You can probably run java from your apex application, but you need to do additional work to make it work properly. In Oracle Forms this is straightforward with a java beans area.

Pixel Perfect Positioning: In Oracle Forms your items are placed at precise x / y coordinates; in APEX/Rest of the web, the layouts are responsive to the screen size and orientation – this is simplified for the APEX developer by using Universal Theme. Even if you can set the height and weight of items in the browser, it can differ between different browsers.

Multiple Forms Open in the same Run-Time: On Forms you can open another form (or even the same form) without closing the previous one.

Function Keys: You probably do not need the mouse to work on a form screen, this makes the user experience quite agile, when you move to a web application you lose the ability create shortcuts using function keys as these are reserved for the browser itself.

POST: From our experience, this is the most challenging thing to replicate in an APEX environment. Some Oracle Form applications, post the data to make it available through different forms or use it to fire some database trigger validations.

Text_IO: With TEXT_IO I can create a file and save it to a local folder. In a web environment, this automatic integration becomes very complicated.

Given the above list, it’s necessary to select the best approach either to replicate that tricky functionality or identify alternatives. There is no simple rule and it is a case of adapting each thorny issue as you encounter them;

A Dynamic Action could be a good or a bad choice to replicate an Oracle Forms Trigger. An Interactive Grid could replace the multi-row data block or you could be in for a world of trouble. There is no tool that takes an Oracle Form and magically creates an APEX page. The approach varies from Form to Form – sometimes two Forms can become one APEX page, and one Form can become many APEX pages.

Conclusion: Absolutely APEX is the best choice to redevelop your legacy Oracle Forms application. Developers can re-apply their SQL & PL/SQL skills to APEX development – making the leap easier. The larger the forms application, the larger the project… naturally. Those thorny issues may cause you to scratch your head; but it’s unlikely that an alternative cannot be found. APEX continues to be the best option for the future direction of your forms application.

APEX uses the best of the modern web to create native web applications; unfortunately, this also brings modern vulnerabilities that we always need to be aware of. To protect applications, APEX has a number of in-built security features available to prevent SQL Injection and Cross Site Scripting.

Often, important processes in our applications fire when a button is clicked. For UI reasons, if a process can’t be fired, or the users don’t have permission to do it, the button may become inactive or disabled. However, this is only HTML, if the application process is not safe, someone with a minor knowledge of HTML can revert this button back to life and boom! Fire you process!

Let’s first see what is behind a disabled button; if we inspect this button using the chrome console (right click on the button and select “inspect”), something similar to the code below will show.

<div class="t-ButtonRegion-buttons">

 

 

   <button onclick="apex.confirm('Are you sure you want to place this order?','PLACE_ORDER');"

 

 

   class="t-Button t-Button--hot lto6341306998453375987_0 apex_disabled" type="button" id="SAVE" disabled="">

 

 

   <span class="t-Button-label">Complete Order</span></button>

 

 

</div>

 

If we remove the property disabled=”” and the class apex_disabled, the button becomes active and clickable again.

So, should I hide the button?
In some cases, it could work, but if you have just an application process on the page that fires when you submit the page, someone can easily call it by using just the JavaScript below:

 

picture_1.png

Therefore, what if I hide the button and add a Server-side Condition in the application process to only fire when the SAVE button is clicked?

 

picture_2.png

 

Let’s look again to the button HTML above.id=”SAVE”, the condition: “only fire when the SAVE button is clicked” means the same as “request = ‘SAVE’”. The would-be hacker just needs to add the request to the JavaScript.

 

picture_4.png

In the next scenario, there is a status item that I set to a value (OK) when the process can be called; can I check this item value before calling the application process? The malicious user may also know the JavaScript below.

 

picture_5.png

We need also insure that each page has access protection, otherwise the hacker can set inject values in the URL.

 

picture_6.png

Available options include:

  • Unrestricted

The page may be requested using a URL, with or without session state arguments, and without having to have a checksum. The URL below includes the RIR request and set the value 1 for the P4_ITEM.https://apex.oracle.com/pls/apex/f?p=107300:4:6447557498631::NO:RIR:P4_ITEM:1

  • Arguments Must Have Checksum

If Request, Clear Cache, or Name/Value Pair arguments appear in the URL, a checksum must also be provided. The URL below includes the RIR request, set the value 1 for the P4_ITEM and include a checksum.https://apex.oracle.com/pls/apex/f?p=107300:4:6447557498631::NO:RIR::&cs=3hd-mtPQg-gw57elJnXo00nw5HlLLYFLePj5bMh0g8-Ybk–yuRYH8f0RHF6Pcl0HJc1LpyJ6fCOkjAvBY3oeogIf we manually try to change the value, 1 to another one, the user will get the following error.Session state protection violation: This may be caused by manual alteration of a URL containing a checksum or by using a link with an incorrect or missing checksum. If you are unsure what caused this error, please contact the application administrator for assistance.

  • No Arguments Supported

A URL may be used to request the page, but the URL cannot contain Request, Clear Cache, or Name/Value Pair arguments.

  • No URL Access

The page may not be accessed using a URL.However, the page may be the target of a Branch to Page branch type, as this does not perform a URL redirect.Tips:

  1. Avoid by applying conditions to the application process that check page items.
  2. Create page validations to double check using the same rule that made the button disabled.
  3. Use APEX authorization schemes to validate page access. Even if a page is not available on the menu to a specific user, the user can set the page number in the page URL (https://apex.oracle.com/pls/apex/f?p=107300:4:6447557498631::NO:::) and go to the page if there is no authorization is selected. To create authorization schemes, go to Shared Components \ Authorization Schemes and then any that you create will be available in the page properties.

 

picture_3.png

Summary:

APEX is very safe and used by numerous military and classified agencies around the world, but like most web technologies, the developers needs to know and use the security resources in order to avoid breaches that could make the app vulnerable.

One of the new features of APEX 5.1 is the introduction of APEX_THEME API which enables you to change the application theme style by user.

A very nice APEX 5.0 feature is to allow users to change the theme style using the APEX_UTIL.SET_CURRENT_THEME_STYLE procedure (deprecated in 5.1) but this change is only applied at application level. Therefore if one user changes the theme style, then it will be changed for all users.

Click here to read.

One of the new features of APEX 5.1 definitely changed my way of creating DDL on Oracle databases. With the Quick SQL packaged application you only need to write minimal lines of markdown text to describe tables, triggers, audit tables and many other objects. This low code development concept can save you so much time, and it is even faster than the other tools available, which specialise in this job.

To use Quick SQL, you do not need to be an APEX developer or even have APEX installed on your environment. You can simply navigate to QuickSQL.oracle.com and login using your Oracle account (or sign up for one). Therefore QuickSQL is suitable for all types of Oracle Developer, from Oracle Forms to backend PLSQL Development, it’s worth investigating further. However, the best about installing the QuickSQL packaged application on your APEX workspace is that you can modify it yourself and improve the application to fit your needs.

It is also worth mentioning that the theme of this blog is not to make a tutorial on how to use the Quick SQL tool, but instead to highlight the really great features. If you want to know more about how to use it, the APEX official video below provides a helpful introduction in only 2 minutes.

Here are some of the features I like:

Uploading spreadsheets

This is useful if you need to import spreadsheet data into your database quickly. You only need to cut and paste data into the worksheet area, and the SQL conversion describes the create table and insert statements for you.

 

Creating Tables

 

It is extremely simple to learn and understand the Quick SQL language used to create tables. For example, when you type words such as name, location, address, description the tool suggests that they should be varchar2 columns.Even the size of the column is predicted. For example:

  • description = varchar2(4000)
  • name = varchar2(255).

 

Syntax

 

 

Taking a closer look at the features available, I really liked how the following syntax is supported:

  • Shorthand suffix to define column attributes
    /indexed suffix to create indexes;
    /fk  [table name] to foreign key;
    vc20 to define a varchar2(20);
    /nn for a not null column;
  • Automatic addition of a primary key column called “ID” for each table. If this isn’t what you want, then you can add /pk to any other column.
  • Automatic detection of foreign keys.
  • Multiple levels of indentation, you can create parent, child, grandchild table structures simply by indenting.
  • Automatic indexing of foreign keys.
  • No need to include underscores in table or column names. i.e. If your column is named “first name” it will become first_name varchar2(255).
  • Columns ending in _YN will automatically have a check constraint generated that restrict the domain of acceptable values to Y and N.

 

You are also able to manage the Quick SQL settings for triggers, history tables, prefixes, audit columns, schema prefixes and more. There is even an option to have QuickSQL generate a PL/SQL table API package, all without writing any extra code.

 

 

APEX Enable

 

 

If you configure the settings to automatically create audit columns and switch APEX Enable option to Yes, the audit log trigger will be generated getting the user from the APEX session, not the database user.After you have designed your schema and are ready; you can copy and paste the generated code to SQL Developer or run it on the Oracle APEX SQL Scripts area. You can save your script within the Quick SQL Application or on your repository.You don’t have to worry about saving your syntax – at the end of the script, there is a comment with your Quick SQL code and the settings that were applied when the script was generated.

 

 

For example:— Generated by Quick SQL Thursday, August 17, 2017, 15:33:06
/*
departments /insert 4
name /pk
Location
Description
employees /insert 14name /nn vc50
email /lower
cost center num
date hired
Job
# settings = { API: true, auditCols: true, APEX: true }
*/

 

 

Summary

 

I have mentioned some great features, but of course, there are some improvements that could be made in the future versions of the application. For example, running the created script directly from Quick SQL or saving the settings by an individual user. Don’t expect to be able to use the Quick SQL tool to replace software such as Oracle SQL Data modeler, but what it will do is – save you time!

One of the great features of APEX is the possibility to import JavaScript plugins and css libraries into the application, just by referencing an external link or uploading a .js file.

There are several JavaScript plugins available online that can be used to easily add new functionality to you application. When you add a JavaScript library to a page or application, you usually do the following steps:

As an example, I will use a simple but very useful JavaScript plugin to create real-time masks (https://github.com/igorescobar/jQuery-Mask-Plugin)

1. Upload the files to the Static Application or Workspace files

Image-1.png

2. Load these files on application or page level (I will do it page level)

Image-2.png

3. Call the JavaScript function.

Image-3.png

Next, I will show how to implement this same functionality as an APEX plugin. The advantages of having APEX plugins comes since no JavaScript knowledge is required to used it, and they can be easily exported and imported across different applications.

  1. Go to Shared Components/Plug-ins and click on create, and then create from scratch.
  2. Choose a name and internal name. The internal name is used internally by APEX for identifying the plug-in, it is not displayed.
  3. Choose the item type, it is where this plugin will be used.

Now, you must define a PL/SQL anonymous block that contains a function for rendering, validating, execution and Ajax callbacks for the plug-in. This can also be stored in the database.

/*When you create a item plugin, you must create the following interface. */

PROCEDURE render_mask_field (

p_item   in            apex_plugin.t_item,

p_plugin in            apex_plugin.t_plugin,

p_param  in            apex_plugin.t_item_render_param,

p_result in out nocopy apex_plugin.t_item_render_result

)

IS

/* Here we define a plugin attribute used by the developer to set the item mask */

lv_format_mask         varchar2(100:= p_item.attribute_01;

 

/* We need to call the following function to allow APEX to map the submitted value to the actual page item in session state.

This function returns the mapping name for your page item. If the HTML input element has multiple values then set p_is_multi_value to TRUE.*/

lv_item_name           varchar2(1000) := apex_plugin.get_input_name_for_page_item(false);

 

Begin

/* This outputs the necessary HTML code to render a text field*/

sys.htp.p('<input type="text" class="text_field apex-item-text '||p_item.element_css_classes||'" name="'||lv_item_name||'" placeholder="'||p_item.placeholder||'" id="'||p_item.name||'" value="'||sys.htf.escape_sc(p_param.value)||'"size="'||p_item.element_width||'"/>');

 

/* Here we call the javascript to set the mask to the item $('#P1_TOTAL').mask('000.000.000.000.000,00'); */

apex_javascript.add_onload_code('$("#'||p_item.name||'").mask("'||lv_format_mask||'")');

 

End render_mask_field;

 

4. Now we set the name of render function

Image-4.png

5. Supported for: We must define where the plugin is supported, where it is available in the Builder, Page Items and/or Interactive Grid Columns. Also if the plug-in is displayed in the Builder as a supported component for desktop and/or mobile.

Image-5.png

6. Standard Attributes: For Item type plug-ins, identify the attributes that apply to this plug-in:

Image-6-1024x134.png

7. Now we must save, so APEX will identify the plugin type and add some plugin options, we must create the format mask custom attribute.

Image-7-1024x65.png

8. JavaScript Files: basically, what are we going to do next is similar of what we do to use javaScript on a page level, upload and load the JavaScript File.

Image-8-1024x311.png

Finally, we can save and use the plugin. Just create a page item and change the item type to the plugin that we just created.

 

If you want to download this plugin, please go to: https://github.com/ExplorerUK/Explorer-Mask-Field

Rodrigo Mesquita

Apex APEX_ERROR API

Posted by Rodrigo Mesquita Jan 27, 2017

    When we create an apex process to execute some pl/sql code, we have the Success Message and Error message fields to input a message and give to the user an alert at the end of the process execution informing the success or failure.

     Usually there are many possibilities of error on a extensive pl/sql code, and one simple generic error alert is not so productive. It's more easier to show specific messages telling the user in which process point something went wrong.


The Apex API called APEX_ERROR helps a lot. When this procedure is executed, the an apex error message alert shows. We can use this on validations and process.

 

A simple way to use it:

 

begin


If :P1_ENAME is null then

 

APEX_ERROR.ADD_ERROR (

    p_message  => 'The employee name can´t be null',

    p_display_location => apex_error.c_inline_in_notification );

 

Elsif :P1_HIREDATE > sysdate then

 

APEX_ERROR.ADD_ERROR (

    p_message  => 'The Hiredate must be less than or equal to today',

    p_display_location => apex_error.c_inline_in_notification );

 

End IF;

 

insert into Emp(ENAME,HIREDATE) values (:P1_ENAME,:P1_HIREDATE);


end;


Submit the page and the result is:

 

 




You can show the notification on the page item too. Adding the p_page_item_name parameter the page item is highlighted on the page. The error message is displayed inline in a notification.


begin


If :P1_ENAME is null then


APEX_ERROR.ADD_ERROR (

    p_message  => 'The employee name can´t be null',

    p_display_location => apex_error.c_inline_with_field_and_notif ,

    p_page_item_name => 'P1_ENAME');


Elsif :P1_HIREDATE > sysdate then


APEX_ERROR.ADD_ERROR (

    p_message  => 'The Hiredate must be less than or equal to today',

    p_display_location => apex_error.c_inline_with_field_and_notif ,

    p_page_item_name => 'P1_HIREDATE');

Else


insert into Emp(ENAME,HIREDATE) values (:P1_ENAME,:P1_HIREDATE);


End If;

end;

 

apex error

 

 

The complete APEX_ERROR API docs, can be found on this link

     Some people use modal pages to create modal windows, but in some cases it is recommended to use the modal region for it. This solution will take advantage of some of the features found in the APEX 5 Universal theme because it is easier to Develop and debug.

 

1. Create a new region and than set this template to inline dialog. On my test i named it to Dialog Region.

 

blog_1.png

 

2. On my example the modal will be open by a button click, so we need to create a button on another region.

3. Create a dynamic action which fires on button click and point to the created button.
4. Create a true action with the Execute JavaScript Code action and use the following for the code property. Don't forget to set the Fire On Page Load to No.

 

  this.affectedElements.dialog('open'); 

 

blog2.png

 

5. Now you have to create a button to close the modal. Create it on the modal region and do the same as above but now with the following code.

  this.affectedElements.dialog('close'); 

 

 

6. Refresh the page and test it.