Apex SQL Command funny notification message

A short note just to mention a sort of bug in Apex.

Go to SQL Workshop and execute the following statement (supposing you don't currently have already a funny_pkg in your schema, otherwise you'll override it!):

Create or replace
Package Funny_Pkg as
Procedure Test (
  par_a  in varchar2,
  par_b  in number);
End Funny_Pkg;

Apex notifies you with the following message

Procedure created.

Now, let's change just a little bit the command, coalescing the first two lines of text:

Create or replace Package Funny_Pkg as
Procedure Test (
  par_a  in varchar2,
  par_b  in number);
End Funny_Pkg;

Now you get the following, correct, notification:

Package created.

There must some weird string processing going on behind the scenes if it picks different object types depending on the line position.

It's just a little whim, the object is fine, the package will be there even if it says it created a procedure.

But this will serve as an introduction to the next topic, an evergreen in the realm of Oracle's quirks.

On ApEx substitution strings embedded in plsql procedures and views

Often it happens that you need to embed static strings in a program or in a view.
Traditionally, this can be easily implemented using PL/SQL constants, either in the program unit or at the package definition level.
However this is not the only (or best...) possibility if your application has been developed with Oracle Application Express.

In Apex you have at least three more choices for storing static data:

1. Global substitution strings, a limited set of user-definable constants that you can find in the application definition properties page.

2. Shortcuts, identified as UPPERCASE double quoted strings like "SHORTCUT", that must have a corresponding entry in the shortcuts definition page, under the shared components page.
Shortcuts are typically used for storing redundant HTML fragments or JAVASCRIPT function calls. For reasons of personal taste, i tend to use shortcuts only in the HS type regions, but there are more places where you can actually reference them.

3. Application items, treated as global substitution strings.

There could be even more fancy options but let's stop here for the sake of this discussion.

So, i had a view where instead of casting the relative path of some icons as long static strings, i preferred to replace them with their global substitution counterparts, i.e. '&MYICON.' instead of '/home/images/app1235/myicon.gif'.

This approach allows you to quickly propagate file location changes throughout your application in case you need it at a later time or when there are differences in the relative path between your development box and the production environment, as in my case. In this way you change the value in one place, centrally, in the application properties, as i said earlier.

And now for the quirk: if you create (or replace) the view (or procedure definition) by issuing the CREATE OR REPLACE statement from the SQL Command Prompt page, prepare yourself to face a situation where all your substitution strings have magically *gone*. This applies to all recent Apex versions as far as i know including version 2.2.

Suppose now that you need to update one of such objects in a live environment, because you need to fix some kind of issue.
Suddenly all your icons are gone and your page looks horrible.
You were expecting to fix an issue and it turns out that you've created a bigger one.
And you didn't get any error or warning at all, the view compiled perfectly.

In this case go back to the SQL Command prompt history, select your view or procedure definition, copy it to the clipboard, then go to the Object Browser and pick the relevant object type from the list. Select the existing definition or create a new one if not, then paste the clipboard content into the code editor tab and compile, the object will be created correctly without trashing the substitution strings this time.

Happy compiling.