Is there an equivalent to the OBJECT_ID function from T-SQL in Oracle?

Question

The OBJECT_ID function in SQL Server is very useful for resolving an object name that is not fully qualified using the name resolution rules that apply for calling that function/procedure. So for instance, if I call the SP name foobar, T-SQL will first look within the current user schema, then within the dbo schema. If they both have a foobar SP, the one in the user schema will be called. The OBJECT_ID function resolves to an object id in the same way.

For Oracle though, things are more complicated as there is the concept of a package name as well. So for instance doe.foobar could refer to the package doe that has a procedure foobar OR it could refer to user schema doe with procedure foobar. There are rules for this that Oracle uses (which include synonyms as well), but I can't seem to find a function that will let me either resolve the name how Oracle's compiler would or get the object id that matches what Oracle's compiler would find.

So after all of that my question is: Is there some way of doing this without trying to recreate the name resolution rules that Oracle uses internally?

Littlefoot · Answer 1 · 2021-08-05T19:05:58.140

I probably didn't understand the question, but - here's what Oracle does. I was kind of lazy creating a new user whose name is dbo so I used scott instead, but I guess it doesn't matter much.

Currently connected as scott

SQL> show user
USER is "SCOTT"

Creating a package named scott (obviously, owned by scott, which means it is in scott schema) and a function foobar in it (which returns where I am):

SQL> create or replace package scott as
  2    function foobar return varchar2;
  3  end;
  4  /

Package created.

SQL> create or replace package body scott as
  2    function foobar return varchar2 is
  3    begin
  4      return 'package function';
  5    end;
  6  end;
  7  /

Package body created.

Creating a standalone function named foobar (again, owned by scott):

SQL> create or replace function foobar return varchar2 is
  2  begin
  3    return 'standalone function';
  4  end;
  5  /

Function created.

OK; so, what's being returned when we call them gradually (foobar > scott.foobar > scott.scott.foobar):

SQL> select foobar from dual;

FOOBAR
--------------------------------------------------------------------------------
standalone function

SQL> select scott.foobar from dual;

FOOBAR
--------------------------------------------------------------------------------
package function

SQL> select scott.scott.foobar from dual;

FOOBAR
--------------------------------------------------------------------------------
package function

SQL>

Therefore, there's no ambiguity; rules are rules. The only ambiguous thing (from my, human point of view) was produced by me. What the heck forced me to create a package named scott?

As of object ID: is this what you're looking for?

SQL> select object_name, object_type, object_id
  2  from user_objects
  3  where object_name in ('SCOTT', 'FOOBAR');

OBJECT_NAME  OBJECT_TYPE          OBJECT_ID
------------ ------------------- ----------
FOOBAR       FUNCTION                 25973
SCOTT        PACKAGE                  25971
SCOTT        PACKAGE BODY             25972

SQL>

[EDIT]

After reading your comment, perhaps this answers your question (at least, a little bit):

SQL> describe scott.foobar
FUNCTION scott.foobar RETURNS VARCHAR2

SQL>

So, that's a function which doesn't accept any parameters and returns a string.

Not really what I'm getting at. Maybe I wasn't clear about the part I'm having difficult with: If I don't know what's there and someone asks me to run `scott.foobar` and I want to get details about `scott.foobar` (for instance the parameters to it), how do I find the object id of the `scott.foobar` that would be called? It could be a different depending on who I'm logged in as for instance. For SQL Server the `OBJECT_ID` function gives me the object id of the one that would be called based on my current scope. How do I get that for Oracle? — flxkid, Aug 05 '21 at 17:25
Maybe it's DESCRIBE that helps ... see an example I added at the bottom of the answer. — Littlefoot, Aug 05 '21 at 19:06
DESCRIBE is a SQL*Plus command, it's not callable from sql or plsql, so that won't work. — flxkid, Aug 05 '21 at 23:50
I'm afraid I don't have any other ideas at the moment, sorry. — Littlefoot, Aug 06 '21 at 08:49

score 0 · Answer 2 · answered Aug 12 '21 at 04:17

Couldn't find a built in way of doing this, so we ended up with one of three queries depending on if we have .., . or . The only complex one is . which is the ambiguous case. We ended up with this query which follows the name resolution documented by Oracle:

SELECT OBJECT_ID, IN_OUT, DATA_TYPE, DATA_LEVEL, ARGUMENT_NAME, POSITION, SEQUENCE, 1 AS PRIORITY FROM USER_ARGUMENTS 
    WHERE PACKAGE_NAME = :pkg AND OBJECT_NAME = :name 
UNION ALL 
SELECT OBJECT_ID, IN_OUT, DATA_TYPE, DATA_LEVEL, ARGUMENT_NAME, POSITION, SEQUENCE, 2 AS PRIORITY FROM ALL_ARGUMENTS 
    WHERE OWNER = :pkg AND PACKAGE_NAME IS NULL AND OBJECT_NAME = :name 
ORDER BY PRIORITY, SEQUENCE

If you couldn't tell, the arguments are what I was really after. This isn't the ideal solution since Oracle could change their name resolution rules and we now have to keep track of that and duplicate what they're doing.

Is there an equivalent to the OBJECT_ID function from T-SQL in Oracle?

2 Answers2