Oracle Scratchpad

January 21, 2014


Filed under: Oracle — Jonathan Lewis @ 9:42 pm BST Jan 21,2014

Here’s a little detail I could do without in my database:

       owner, object_type, object_name
        object_name like '_'
order by
        object_name, object_type

--------------- ------------------- --------------------
APEX_030200     PROCEDURE           F
PUBLIC          SYNONYM             F
APEX_030200     PROCEDURE           G
APEX_030200     PROCEDURE           P
PUBLIC          SYNONYM             P
APEX_030200     FUNCTION            V
PUBLIC          SYNONYM             V
APEX_030200     PROCEDURE           Z
PUBLIC          SYNONYM             Z

9 rows selected.

Public names like P and F for procedures or functions are just not on (unless I create them myself).


  1. I’ve often wondered about this… It’s used all over the place in APEX for externalizing variables ( aka global variables) and other such session settings and info.
    I’d say there would be a better convention to use than just a single letter, but then again I’m not “educated” so I must have no opinion…

    Comment by Nuno Pinto do Souto — January 21, 2014 @ 11:00 pm BST Jan 21,2014 | Reply

  2. I can’t speak to the history behind the names (APEX has a fairly long history now), but I suspect that they were chosen to help minimize re-typing–as in the v() function, which is used very frequently–or to help prevent URLs from becoming overly long and unmanageable (if memory serves, URLs at least originally were supposed to have 256-character length limits; I don’t know if there’s a limit any more).

    Comment by David Gale — January 22, 2014 @ 12:17 am BST Jan 22,2014 | Reply

  3. I’d be very annoyed if everywhere I called v() I had to use some longer identifier. It’s used very very frequently, and I wouldn’t want my code cluttered with a longer name for something so basic. Also, F, P and Z are used for Apex URLs so using a single letter instead of more might very well save some bandwidth maybe? Not sure what G is for though.

    Comment by Jeffrey Kemp — January 22, 2014 @ 4:56 am BST Jan 22,2014 | Reply

    • true, but its not much to ask that when you (say) add a schema to be used under apex, that private synonyms could be created under there.

      public syns are a pain :-)

      Comment by connormcdonald — January 22, 2014 @ 5:29 am BST Jan 22,2014 | Reply

    • The irony there, of course, is that we see complaints on the OTN forum from time to time (there’s one going on at present) from people who want Oracle to allow CamelCase names up to 128 characters long because 32 isn’t enough and they don’t want to use underscores. (And, of course, they want Oracle to work out that CamelCase is the same as camelcase and CAMELCASE).

      Comment by Jonathan Lewis — January 22, 2014 @ 8:05 am BST Jan 22,2014 | Reply

  4. It just occurred to me that I should have seen X and Y in this list as well, as public synonyms for objects under the MDSYS schema.

    But I didn’t have spatial installed on this copy of Oracle, and a quick check on a version which did have MDSYS installed didn’t show X and Y – although the ogc_x and ogc_y to which they used to refer do still exist but now with matching (public) synonyms.

    Comment by Jonathan Lewis — January 22, 2014 @ 8:14 am BST Jan 22,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by