Categories
dba

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

Categories
dba

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

Categories
sql

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 🙂