What is Oracle APEX Quick SQL?

If you’ve browsed Oracle APEX’s features you must have seen the Quick SQL in the SQL Workshop. But have you ever used it? Do you know the shortcut? If not, have a look at this post to understand better this amazing tool.

 

What is Quick SQL?

According to the Oracle Documentation

Quickly develop a script for simple tables and views.

Quick SQL provides a quick way to generate the SQL required to create a relational data model from an indented text document.

Quick SQL is designed to reduce the time and effort required to create SQL tables, triggers, and index structures. This tool is not designed to be a replacement for data modeling. Instead, Quick SQL is simply a quick way to develop a script for simple tables and views. Once you have generate the SQL, you can then tweak and expand it.

From this quote, you can see that it is not design to replace traditional SQL Development, it is just a tool to help speed up the development of simple routines that sometimes take longer than they should.

Where can I test it?

If you have Oracle APEX,  you can open it in SQL Workshop -> Utilities -> Quick SQL

If you don’t have it, then you should go get it, it’s free 🙂 . But if you really in a hurry, then try https://apex.oracle.com/quicksql

Basic Syntax

As you’ll see, quick SQL is easy to use, you just add some text, indentation and shorthands for stuff you want. Take a look at the example below

On the left side is what I’ve wrote and on the right side is the actual script being generated. Can you see from this simple example how much writing quick sql did for me? I’ve only wrote the table name, indented to write the columns with some shorthands for things like data types and not null. And when I wrote “date_of_birth” i didn’t have to write anything. And the Primary key came as a bonus.

By pressing Enter or “Generate SQL” it keeps updating the right side, so I knew what was happening all the time.

Let’s add a bit more

Ok, that may have been a bit too easy, let’s add

  • check constraints
  • a child table
  • audit columns (auditing in the trigger)

This simple addition made it difficult to fit the right side content, so I’m adding it bellow

 Where can I learn more?

The good news is that Oracle provides a lot of content to study.

The bad news is that, as far as i know, only Oracle has content about it

Quick SQL Documentation: https://docs.oracle.com/en/database/oracle/application-express/20.2//aeutl/using-quick-sql.html#GUID-21EE36C2-F814-48C0-90EA-7D464E9014FD

Shorthands and Datatype: I don’t have a link for that, I have a path. with Quick SQL opened, click on help and you’ll get all the datatypes, table and column directives that you can use (check image below)

One final tip

Take a look at the sample files, you won’t believe how much you’ll learn from them.

That’s it, good studies and good luck

The power of Dynamic Actions in APEX

In this post I would like to talk about a few configuration items in the Dynamic Actions that could make reduce your need to code basic interactions even further.

Selection Type: jQuery Selector and Javascript Expression

  • Do you have multiple elements that need the same action?
  • Are your page’s elements not accessible by the items, buttons, regions or columns selection type?
  • Does your page region have plain HTML and you want it to use Dynamic Actions?

If you said yes, then I highly suggest you take a look below:

jQuery Selector:

The easiest of the 2. You just need to add the selector that will capture the element(s) you need.

You don’t know how to use a selector? Here are a few tips

  • If your apex item has a static ID with the value element-x , you can write #element-x (yes, hashtag include)
  • If you want create a global action that will do or check something, you can write just body

Selecting elements is a powerful tool and to understand them better, I recommend reading more about it in https://learn.jquery.com/using-jquery-core/selecting-elements/.

JavaScript Expression:

Alright, let’s say that your search criteria is so complex that selectors won’t do it. Or maybe you have to validate something in the elements beforehand. If that is the case you can fallback to the JavaScript Expression which allow you to grab DOM elements, apply logic and return processed items to be selected.


Event Scope

The event scope is an awesome tool that allow us to do dynamic binding, which, to the best of my knowledge remove the need for adding event directly into the HTML code because it continues to work even after Pagination, Refresh adding new items, etc…

I am 99% sure that this comes from the jQuery ‘on’ event with delegate. Anyways, here are some tips

  • By selecting it, the Static Container (jQuery Selector) will become available. Please make sure it is an HTML element that DOES NOT get updates with AJAX. Usually you are safe with regions (give them a static id) and in the last case the <main> or <body> tags. But always try the region first
  • IMPORTANT: In the ‘when’ section, if you are using a jQuery Selector as well, make it not including the container itself.

Client-side vs Server-side Conditions

I’ve seen this with many clients, they are not the same!!!! The only thing they have in common is the name.

Client-side Condition: Is the equivalent of adding an IF clause to you Dynamic Action this allows you to better leverage the low code side of APEX. When adding a condition, you can redirect to the TRUE or FALSE actions.

Server-side Condition: The server-side condition does the same thing to DAs that it does for other components, if true the server will render/send it to the user’s machine, otherwise it won’t. There no true and false and the actions won’t work because the Dynamic Action doesn’t exist to be bound to the element.

Conclusion

As you can see, with APEX you probably don’t need to add external JavaScript or bind events directly on the HTML tag which helps to keep your low code also clean from mundane taks.

Oracle APEX List of Values and Popup Lov

Most people don’t know, but Oracle APEX List of Values (LoV) are very flexible components with that allows us to perform a series of actions using a centralized and reusable data source.

In this post, I’ll be briefly talking about some of the amazing functionalities this tool provides us.

Multi column view

After creating the LoV you can scroll down to Additional Columns and add all columns available in your original select. This columns will become available Popup LoV when the user clicks on it to find an item.

The nice thing about this feature is that you can toggle if the column is visible, searchable and even add a format mask.

And don’t worry, if you use the same LoV for Select Lists items, it will still get its configuration from the Display / Return Value fields.

Subscription

If you have one or just a few Apps in you workspace, you probably never did a subscription. But this is a great way to have a “Master List App” that all other apps listen to get updates.

The interesting thing to note is that the subscription needs to be refreshed by the Publisher or Subscriber, it doesn’t happen automatically. For this reason it allows you test it before letting the code propagate to other applications.

JavaScript Initialization

For those who know JavaScript, you can modify the initialization object and return it with a new configuration.

Although Oracle APEX has dropped jQuery UI, it still uses the same OPTIONS object that you can learn more about in here https://api.jqueryui.com/dialog/

 

Additional Outputs

Going back to my first tip when I mentioned multiple columns, in addition to that, you can set the value of multiple page items by using said columns. The interface here is a bit too compact, but from the example you can see the pattern. Column name : page item, another column : another page item.

 

Conclusion

As you can see, the List of Value and Popup LoV are 2 powerful features that allow us to quickly develop without gaving to worry too much about standard list components (and some not so standard as well). I could probably write much more on the subject, but that will probably stay for another post 😉