DBMS_ASSERT is one of the oracle supplied pl/sql packages which contains useful functionality. Amongst others it supplies the function dbms_assert.sql_object_name. Although the function is very useful, it’s poorly documented and has several caveats you should be aware that are not mentioned.

This is what the documentation has to say:

This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.

When the SQL object exists it will simply return your input, otherwise it will fail with ORA-44002: Invalid object name.

What the documentation does not (explicitly) specify:

  • It will not purely check the existence of SQL object. Instead if will check the given object name exists AND is accessible to the current user. Since it allows to check for a qualified name you could easily check for the existence of the scott.emp table. However if your current user isn’t granted select privileges that table the functionwill return an error stating invalid object name. However the EMP table does exist, it’s merely not accessible to you. Depending on your expectation this could mean a hell of a difference.
  • The second caveat is that is does not allow you to specify a namespace to search for or an object type. When you are not aware of this might cause wrong interpretation of the result. Executing DBMS_ASSERT.SQL_OBJECT_NAME(’emp’) successfully does not mean the EMP table exists. It does only mean an object with the name EMP exists. Could very well be a index named EMP on an other table.

My naming convention for objects rules out that the same object name can exists even in different namespaces, if yours does not it’s vital to keep this information in mind. To freshen up your memory Oracle object names are divided into separate namespaces. The following schema objects share one namespace:

  • Tables
  • Views
  • Sequences
  • Private synonyms
  • Stand-alone procedures
  • Stand-alone stored functions
  • Packages
  • Materialized views
  • User-defined types

Each of the following schema objects has its own namespace:

  • Indexes
  • Constraints
  • Clusters
  • Database triggers
  • Private database links
  • Dimensions


Posted in Oracle Tagged with: ,

Leave a Reply