This content has been marked as final. Show 3 replies
974884 wrote:No scripts please. Use PL/SQL properly. Stored in the database.
We have a need to write a script to validate the DB links in each database.
I am stuck with, how to validate the private DB links.It can only be validated when executing code as the schema that owns the database link.
a) using dynamic SQL with that database link as part of the SQL
b) parse and execute that SQL cursor as the schema that owns the database link
The first part is fairly easy. Iterate through DBA_DB_LINKS and build a dynamic SQL statement - something like "+select * from dual@some_db_link+".
The next part is parsing this as a cursor as the db link schema owner. This needs to be done using DBMS_SYS_SQL package. It is not officially documented as far as I know, but it works exactly as DBMS_SQL, with the addition of being able to specify the schema to parse the cursor as. (this is basically how Apex works - being able to run Apex applications as a specific schema).