check invalid database link for the DBA

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba


CREATE FUNCTION dba_status
  (owner VARCHAR2, db_link VARCHAR2)
  RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  status   VARCHAR2 (4000);
BEGIN
  EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
  EXECUTE IMMEDIATE
    'begin :1 := "' || owner 
    ||'".status(''' || db_link || '''); end;'
    USING OUT status;
  EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
  COMMIT;
  RETURN status;
END;
/


SELECT 
  owner, db_link, dba_status (owner, db_link) 
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)                                   
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

check invalid database link

If one database link is invalid, you cannot select through it


SQL> select * from dual@z;
select * from dual@z
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.


create function status(db_link varchar2) 
return varchar2 is 
  x number;
begin
  execute immediate 'select 1 from dual@"'||
        DB_LINK||
        '"' into x;
  return 'OK';
exception
  when others then return sqlerrm;
end;
/


select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK

dynamic database link

How do I select thru a database link, where the database link is not fixed?


SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from LSC_T@:db_link where x>0;  
select x from LSC_T@:db_link where x>0;  
                    *
ERROR at line 1:
ORA-01729: database link name expected

My solution using dynamic SQL
Remote databases DB02


create table lsc_t(x number);
insert into lsc_t(x) values (2);
commit;

Remote databases DB03


create table lsc_t(x number);
insert into lsc_t(x) values (3);
commit;

Local database DB01

create or replace type lsc_t_o as object(x number);
/
create or replace type lsc_t_c as table of lsc_t_o;
/
create or replace function lsc_f(str varchar2) 
return lsc_t_c is  
  rc lsc_t_c;  
begin  
  execute immediate 
    'select lsc_t_o(x) from '
    || dbms_assert.qualified_sql_name(str) bulk collect into rc;  
  return rc;  
end;  
/

Note the DBMS_ASSERT function. The DBMS_ASSERT just enhances the security by prevent SQL injection

Ok, let’s try

SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from table(lsc_f('LSC_T@'
  ||:db_link)) where x>0;  
         X
----------
         2
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;  
         X
----------
         3

Seems to work 🙂