Skip to Main Content
  • Questions
  • DBMS_ASSERT returning the ORA-44002: invalid object name exception for existing database links and directories

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nick.

Asked: September 29, 2020 - 3:13 pm UTC

Answered by: Connor McDonald - Last updated: October 19, 2020 - 2:54 am UTC

Category: Database Development - Version: 19.7.0.0.0

Viewed 100+ times

You Asked

Hi,

In my procedure I'm trying to check whether a database link and a directory exist. If they don't I wanted to display a nice message about them needing to be created etc.

I thought of using dbms_assert.sql_object_name, this seems to do the trick for tables, views, functions but not for database links or directories.

Here is my test case (not my actual procedure, but I have the same issue)
The table, view, function return the name / don't error when running the dbms_assert part.
The database link, directory returns "ORA-44002: invalid object name"

-- table
create table test_tbl (x number);
select table_name from user_tables where table_name = 'TEST_TBL';
select sys.dbms_assert.sql_object_name('TEST_TBL') from dual;



-- view
create view test_vw as select * from test_tbl;
select view_name from user_views where view_name = 'TEST_VW';
select sys.dbms_assert.sql_object_name('TEST_VW') from dual;


-- function
create or replace function test_f 
return date
is
dt date;
begin dt := sysdate;
return dt;
end;

select object_name from user_objects where object_name = 'TEST_F';
select sys.dbms_assert.sql_object_name('TEST_F') from dual
;

-- database link
create database link test_link connect to user123 identified by user123 using 'dwh';
select db_link from user_db_links where db_link = 'TEST_LINK';
select sysdate from dual@test_link;
select sys.dbms_assert.sql_object_name('test_link') from dual;
select sys.dbms_assert.sql_object_name('TEST_LINK') from dual;



-- directory
create directory test_dir as '/apps1/oradata/big_dump';
select directory_name from all_directories where directory_name = 'TEST_DIR';
select sys.dbms_assert.sql_object_name('test_dir') from dual;
select sys.dbms_assert.sql_object_name('TEST_DIR') from dual;


Thanks
Nick

and we said...

Thanks for your patience.

I asked around internally and this is expected behaviour due to the definition in the docs

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

where the term "SQL object" means an item that could be referred to **standalone** (which is the aspect from which we would typically want validate an assertion).

For example, a LOB is an object but you can refer to it in isolation

SQL> select object_name
  2  from user_objects
  3  where object_name = 'SYS_LOB0000109294C00002$$';

OBJECT_NAME
----------------------------------------
SYS_LOB0000109294C00002$$

SQL> select sys.dbms_assert.sql_object_name('SYS_LOB0000109294C00002$$') from dual;
select sys.dbms_assert.sql_object_name('SYS_LOB0000109294C00002$$') from dual
       *
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 472
ORA-06512: at "SYS.DBMS_ASSERT", line 467


(But I agree, the docs could be better at explaining this, and I've logged a bug to get that improved)

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.