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"
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;
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;
create or replace function test_f
begin dt := sysdate;
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;
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 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$$';
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)