Skip navigation

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