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

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