Sunday, November 8, 2020

New version of Quick PL/SQL, a code generator for PL/SQL

I've released a new version of Quick PL/SQL, the code generator for PL/SQL based on the same concept as QuickSQL in APEX, but for PL/SQL code. The initial version was released in August 2018.

The online version is available (as before) here: tinyurl.com/quickplsql2

The source code is available on my GitHub.

This release (version 1.2) has a few improvements. The first is syntax highlighting of the generated code (right hand side of the page). This relies on the CodeMirror library which is bundled with APEX. Unfortunately it only supports SQL, not PL/SQL, so not all code is properly highlighted, but it's better than nothing.




The second improvement is a "Quick CRUD" button which opens a dialog that allows you to specify a table name (and optionally the name of the primary key column and the package name):


Based on this it creates the markup required for a complete "CRUD package" in the Input section, which saves you from remembering the markup syntax, and saves you from typing a lot of boilerplate markup as well.

Then, as before, just click on "Generate" to have the final PL/SQL generated for you.




Enjoy! :-)



Sunday, December 8, 2019

Translation enhancements in APEX 18 and 19

The Oracle Application Express (APEX) documentation summarizes the translation process thus:

"To translate an application developed in App Builder, you must map the primary and target language, seed and export text to a translation file, translate the text, apply the translation file, and publish the translated application."

When you go to Shared Components and then click "Translate Application", this process is illustrated by a list:

So the "default" approach is to download the XLIFF file and use a third-party tool to do the translation by editing that file.

However, it is also possible to edit the translations directly from within APEX. Click on "Translation Repository" under Translation Utilities to access this editor.



A few simple, but very useful enhancements have been made to the Translation Repository pages in the last couple of APEX versions.

 In APEX 19, there is now a new column available called "Translated" which indicates whether the original string has been translated or not. Using this column it is easy to filter out the strings already translated.




The popup edit window for the translation has a checkbox, added in APEX 18, that allows you to update all existing occurrences of the source string with a new translation. This is useful if you have, for example, 100 buttons with the label "Save Changes" because you only need to do the translation once.



Also in APEX 18, a "Grid Edit" page was added so you could edit the translations using a tabular form. In APEX 19, this tabular form was replaced with an interactive grid.



This interactive grid also includes the "Translated" column that can be used to filter out the translations which are already done.

Note that sorting by the "Translate From" or "Translate To" columns in the interactive grid is not possible (if you try you'll get an "ORA-00932: inconsistent datatypes: expected - got NCLOB" error). But the list of available columns include "Translate From (Partial)" and "Translate To (Partial)" which extracts the first part of the string as a regular varchar2 (usually the strings to translate are not that long so you'll see the full string in the "Partial" column). If you need to sort the strings (I find it useful to sort alphabetically by the source string in order to find identical strings and use copy/paste to update the translation), use these "Partial" columns to set up the sorting.


Thanks to Joel Kallmann for taking the time to add these small but very useful enhancements into APEX based on my feedback.


Sunday, June 16, 2019

APEX custom Theme Style performance

When you create an application in Oracle Application Express (APEX) using the Universal Theme, the application comes with a few default "Theme Styles" which are essentially various stylesheets that you can switch between to go from the default blue (called "Vita") to, for example, the dark grey style (called "Vita - Slate").

You can use the Theme Roller in Oracle Application Express (APEX) to customize the colors and other settings, and by saving these you create your own Theme Styles. In this video, Shakeeb from the Oracle APEX team demonstrates how to use the Theme Roller, and he also briefly mentions that the generated stylesheet for custom theme styles can be placed into the filesystem for better performance.



I recorded a video myself to discuss the details of how custom Theme Styles affect the performance of your APEX application, and how you can move the Theme Style generated stylesheets to a web server to maximize performance and minimize bandwidth consumption:




Another issue that I did not mention in the video is that when you pick an icon for your application, APEX creates an app-icon.svg file (for the logo itself) and app-icon.css (that references the svg file), and places both those files in your Static Application Files.

Now since these files (and all other Static Application Files) are stored in the database, they require a database hit to retrieve, instead of fetching them from (and caching them on) the web server filesystem.



So if you want to maximize performance, I suggest that you remove the reference to the logo CSS file from your application (and delete the files from the Static Application Files).


If you really want that logo icon, you could of course place the .svg and .css files on the web server and reference them from there instead.


Thursday, April 25, 2019

Thoth Gateway version 1.4.3 now available

I've updated the Thoth Gateway (a PL/SQL web gateway similar to mod_plsql and ORDS, but written in .NET that allows you to run APEX and PL/SQL Web Toolkit applications using Microsoft IIS web server).



The latest release is version 1.4.3. It fixes a few minor issues, but more importantly it is compiled against the latest ODP.NET Managed Driver.

Among other things, this version of the Managed Driver supports Oracle Advanced Security Option (ASO) encryption; without this certain operations such as APEX file uploads would cause "OracleInternal.Network.NetworkException: ORA-12537: Network Session: End of file" errors against an Oracle 18c database.

When upgrading to Thoth Gateway v1.4.3 or later, please make sure you also copy the Oracle.ManagedDataAccess.dll file in addition to the PLSQLGatewayModule.dll file to make sure you have the correct library.

Enjoy!



Wednesday, January 30, 2019

APEX Plugin: Execute PL/SQL code and return content to page (updated)

Oracle APEX has a built-in Dynamic Action called "Execute PL/SQL Code" that executes a block of PL/SQL code on the server via an Ajax call (ie does not do a regular submit and reload of the whole page).

But what if you want to do something on the server AND also return some content back to the client? Back in 2012 I released an APEX Dynamic Action plugin called "Execute PL/SQL Code and Return Content" that allows you to do just that. Read the original blog post for more information.



Since then, the APEX framework has evolved and the old Ajax workhorse function called "htmldb_Get" has been deprecated and replaced with functions in the "apex.server" namespace. Hence I have updated my plugin to use the modern API.

You can find the updated version (1.1) of the plugin on my GitHub page for APEX plugins. Note that the new plugin has been exported from APEX 18.1 and therefore requires at least that version to import and use.

Enjoy! :-)


Thursday, August 9, 2018

Quick PL/SQL, a code generator for PL/SQL based on simple markup

Maybe you've heard about "Quick SQL", a utility (previously a separate application, now part of APEX 18.1 itself) that generates SQL scripts based on a simple markup language. Quick SQL is a real time-saver and allows you to go from idea to prototype to working application in an instant.

Inspired by Quick SQL, I've created a similar utility, called "Quick PL/SQL", that does the same thing, except it generates PL/SQL code (packages with functions and procedures, with standard comment blocks, formatting, etc.) based on a simple markup.

I've recorded a couple of videos to show the tool in action.

Here is an introduction which explains the basic input syntax and the output options:



And here is another video which explains how to create standard CRUD-style (Create, Read, Update, Delete) APIs for your tables:



Try it out for yourself here: https://apex.oracle.com/pls/apex/f?p=QUICKPLSQL:HOME&c=MULEDEV or via this shortcut: https://tinyurl.com/quickplsql2

Of course, I wouldn't mind if this functionality becomes available as part of the QuickSQL utility that is built into APEX! :-)


Monday, March 19, 2018

Thoughts about the APEX_UTIL package

The APEX_UTIL package in Oracle Application Express is a mixed bag of "miscellaneous" stuff.

From the documentation: "You can use the APEX_UTIL package to get and set session state, get files, check authorizations for users, reset different states for users, get and purge cache information and also to get and set preferences for users."


It's clear that, over time, a lot of stuff has been placed here that really belongs elsewhere, or in its own package. By splitting up this package, it should be easier for the developer to find the functions and procedures he is looking for.

Here are my thoughts on how the APEX_UTIL package could be refactored. I'm not saying remove all this from the package in the next APEX release, but move the code to new/other packages and just leave wrappers in the APEX_UTIL package that call the new package, so that old code continues to work. Mark these as deprecated in the docs, and maybe remove after a few more releases.

Move to a new APEX_CACHE package:

  CACHE_GET_DATE_OF_PAGE_CACHE Function
  CACHE_GET_DATE_OF_REGION_CACHE Function
  CACHE_PURGE_BY_APPLICATION Procedure
  CACHE_PURGE_BY_PAGE Procedure
  CACHE_PURGE_STALE Procedure
  CLEAR_APP_CACHE Procedure
  CLEAR_PAGE_CACHE Procedure
  CLEAR_USER_CACHE Procedure
  PURGE_REGIONS_BY_APP Procedure
  PURGE_REGIONS_BY_NAME Procedure
  PURGE_REGIONS_BY_PAGE Procedure
 
Move to a new APEX_USER package:

  CHANGE_CURRENT_USER_PW Procedure
  CHANGE_PASSWORD_ON_FIRST_USE Function
  CREATE_USER Procedure
  CREATE_USER_GROUP Procedure
  CURRENT_USER_IN_GROUP Function
  DELETE_USER_GROUP Procedure Signature 1
  DELETE_USER_GROUP Procedure Signature 2
  EDIT_USER Procedure
  END_USER_ACCOUNT_DAYS_LEFT Function
  EXPIRE_END_USER_ACCOUNT Procedure
  EXPIRE_WORKSPACE_ACCOUNT Procedure
  EXPORT_USERS Procedure
  FETCH_USER Procedure Signature 1
  FETCH_USER Procedure Signature 2
  FETCH_USER Procedure Signature 3
  GET_FIRST_NAME Function
  GET_LAST_NAME Function
  GET_USER_ID Function
  GET_USER_ROLES Function
  GET_USERNAME Function
  GET_EMAIL Function
  SET_USERNAME Procedure
  SET_EMAIL Procedure
  SET_FIRST_NAME Procedure
  SET_LAST_NAME Procedure
  LOCK_ACCOUNT Procedure
  PASSWORD_FIRST_USE_OCCURRED Function
  UNEXPIRE_END_USER_ACCOUNT Procedure
  UNEXPIRE_WORKSPACE_ACCOUNT Procedure
  UNLOCK_ACCOUNT Procedure
  WORKSPACE_ACCOUNT_DAYS_LEFT Function
  STRONG_PASSWORD_CHECK Procedure
  STRONG_PASSWORD_VALIDATION Function
  GET_ACCOUNT_LOCKED_STATUS Function
  REMOVE_USER Procedure
  RESET_AUTHORIZATIONS Procedure [DEPRECATED]
  PUBLIC_CHECK_AUTHORIZATION Function [DEPRECATED]
  RESET_PASSWORD Procedure
  RESET_PW Procedure
  GET_AUTHENTICATION_RESULT Function
  SET_AUTHENTICATION_RESULT Procedure
  SET_CUSTOM_AUTH_STATUS Procedure
  GET_GROUPS_USER_BELONGS_TO Function
  GET_GROUP_ID Function
  GET_GROUP_NAME Function
  SET_GROUP_GROUP_GRANTS Procedure
  SET_GROUP_USER_GRANTS Procedure
  IS_LOGIN_PASSWORD_VALID Function
  IS_USERNAME_UNIQUE Function
  GET_ATTRIBUTE Function
  SET_ATTRIBUTE Procedure
 
Move to a new APEX_PRINT package:

  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 1
  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 2
  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 3
  DOWNLOAD_PRINT_DOCUMENT Procedure Signature 4
  GET_PRINT_DOCUMENT Function Signature 1
  GET_PRINT_DOCUMENT Function Signature 2
  GET_PRINT_DOCUMENT Function Signature 3
  GET_PRINT_DOCUMENT Function Signature 4
 
Move to APEX_IR package (this has already been done):

  IR_CLEAR Procedure [DEPRECATED]
  IR_DELETE_REPORT Procedure [DEPRECATED]
  IR_DELETE_SUBSCRIPTION Procedure [DEPRECATED]
  IR_FILTER Procedure [DEPRECATED]
  IR_RESET Procedure [DEPRECATED]

Move to APEX_SESSION package:

  GET_SESSION_LANG Function
  GET_SESSION_STATE Function
  GET_SESSION_TERRITORY Function
  GET_SESSION_TIME_ZONE Function
  SET_SESSION_HIGH_CONTRAST_OFF Procedure
  SET_SESSION_HIGH_CONTRAST_ON Procedure
  SET_SESSION_LANG Procedure
  SET_SESSION_LIFETIME_SECONDS Procedure
  SET_SESSION_MAX_IDLE_SECONDS Procedure
  SET_SESSION_SCREEN_READER_OFF Procedure
  SET_SESSION_SCREEN_READER_ON Procedure
  SET_SESSION_STATE Procedure
  SET_SESSION_TERRITORY Procedure
  SET_SESSION_TIME_ZONE Procedure
  GET_EDITION Function
  SET_EDITION Procedure
  SET_SECURITY_GROUP_ID Procedure
  FETCH_APP_ITEM Function
  GET_NUMERIC_SESSION_STATE Function
  GET_CURRENT_USER_ID Function
  IS_HIGH_CONTRAST_SESSION Function
  IS_HIGH_CONTRAST_SESSION_YN Function
  IS_SCREEN_READER_SESSION Function
  IS_SCREEN_READER_SESSION_YN Function
  SET_CURRENT_THEME_STYLE Procedure [DEPRECATED]
  GET_DEFAULT_SCHEMA Function
  SET_WORKSPACE_Procedure

Move to APEX_EXPORT package:

  GET_SUPPORTING_OBJECT_SCRIPT Function
  GET_SUPPORTING_OBJECT_SCRIPT Procedure
 
Move to APEX_PAGE package: 

  GET_HIGH_CONTRAST_MODE_TOGGLE Function
  GET_SCREEN_READER_MODE_TOGGLE Function
  SHOW_HIGH_CONTRAST_MODE_TOGGLE Procedure
  SHOW_SCREEN_READER_MODE_TOGGLE Procedure

Leave in APEX_UTIL (for now):

  CLOSE_OPEN_DB_LINKS Procedure
  COUNT_CLICK Procedure
  CUSTOM_CALENDAR Procedure
  INCREMENT_CALENDAR Procedure
  SUBMIT_FEEDBACK Procedure
  SUBMIT_FEEDBACK_FOLLOWUP Procedure
  GET_FEEDBACK_FOLLOW_UP Function
  STRING_TO_TABLE Function [DEPRECATED]
  TABLE_TO_STRING Function [DEPRECATED]
  SAVEKEY_NUM Function
  SAVEKEY_VC2 Function
  KEYVAL_NUM Function
  KEYVAL_VC2 Function
  HOST_URL Function
  URL_ENCODE Function
  REDIRECT_URL Procedure
  PREPARE_URL Function
  GET_HASH Function
  GET_SINCE Function
  GET_PREFERENCE Function
  SET_PREFERENCE Procedure
  REMOVE_PREFERENCE Procedure
  REMOVE_SORT_PREFERENCES Procedure
  GET_BUILD_OPTION_STATUS Function Signature 1
  GET_BUILD_OPTION_STATUS Function Signature 2
  SET_BUILD_OPTION_STATUS Procedure
  GET_BLOB_FILE_SRC Function
  GET_FILE Procedure
  GET_FILE_ID Function
  HTML_PCT_GRAPH_MASK Function
  FIND_SECURITY_GROUP_ID Function
  FIND_WORKSPACE Function
  GET_APPLICATION_STATUS Function
  GET_GLOBAL_NOTIFICATION Function
  SET_GLOBAL_NOTIFICATION Procedure
  SET_APP_BUILD_STATUS Procedure
  SET_APPLICATION_STATUS Procedure

 

These are just my suggestions, but I do hope the APEX developer team will do some kind of refactoring (even if not exactly as suggested above) in future releases of APEX.

If you would like to see it done differently, please leave feedback in the comment field below! :-)