Monday, December 09, 2024

The eternal question: is it a bug or a feature? The case of DBMS_ASSERT.SQL_OBJECT_NAME

Have you ever tried to execute DBMS_ASSERT.SQL_OBJECT_NAME passing a packaged function or procedure?
select dbms_assert.sql_object_name('<SCHEMA>.<PACKAGE>.<whatever>') from dual;

If <SCHEMA>.<PACKAGE> exists, you can write whatever you like and the query will return the input string as it is, that is a valid result.
It will only fail if the object <SCHEMA>.<PACKAGE> does not exist or the user calling the function does not hold sufficient privileges on that object.

ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 472
ORA-06512: at "SYS.DBMS_ASSERT", line 467
44002. 0000 -  "invalid object name"
*Document: Yes
*Cause:    The input parameter string was not a qualified
           SQL identifier of an existing SQL object.
*Action:   Make sure the string matches an existing SQL object that
           is accessible by the current schema.

So, the question is: why DBMS_ASSERT.SQL_OBJECT_NAME accepts fully qualified packaged procedure/function names but it does not check if they actually exist?

I mean, if this function would also verify that the packaged procedure or function exists, would it cause any trouble?

And in that case, why not adding an optional parameter whose default value makes the function work as usual but allows for an extended verification of the packaged procedures/functions?

So, in the end, is this a bug or a feature that I don't fully appreciate?

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles