What is Oracle equivalent of OBJECT

CREATE OR REPLACE PROCEDURE OMEGA."IPO_GET"
(cur OUT SYS_REFCURSOR)IS
BEGIN
    OPEN cur FOR
      SELECT i.* , 
             CASE 
             WHEN OBJECT_ID(
                    DBMS_ASSERT.ENQUOTE_LITERAL(i.ipo_code||'_exchange_data'),
                    'U'
                  ) IS NOT NULL
             THEN 1
             ELSE 0
             END CASE
      FROM   IPO_MASTER i
      order by start_date DESC;
END;

I am getting the error:

PL/SQL: ORA-00904: "OBJECT_ID": invalid identifier

CodePudding user response：

I might be far from what you actually expect, but in Oracle there is a table all_objects, where all the objects are stored and this query:

SELECT object_id
  FROM all_objects
 WHERE owner       = <your SCHEMA>
   AND object_name = <your OBJECT>

will return unique id of the given object if it exists.

CodePudding user response：

Oracle doesn't have a function ORACLE_ID like the one in SQL Server. But you could use the data dictionary to solve your problem.

CREATE OR REPLACE PROCEDURE OMEGA."IPO_GET"
(cur OUT SYS_REFCURSOR)IS
BEGIN
    OPEN cur FOR
      SELECT i.* , 
             nvl2(ut.table_name, 1, 0) as haz_exch_data_table
      FROM   IPO_MASTER i
      left outer join user_tables ut
        on ut.table_name = i.ipo_code||'_EXCHANGE_DATA'
      order by start_date DESC;
END;

If there is an exchange data table prefixed by a value of i.ipo_code then haz_exch_data_table will be one else it will be 0.