PLSQL - Create DBLink inside function

Question

I would like to create database link inside of script, and want to receive all table names from the linked database. If I am correct, I need to create database link in order to use, but Oracle does not allow me to create such thing neither inside of my_fn or DECLARE section. Any suggestion?

DECLARE

TYPE tp_col_array IS TABLE OF varchar2(1000);
FUNCTION my_fn( 
    p_in_dblink_name IN VARCHAR2,       
    p_in_schema_name IN VARCHAR2)
    RETURN varchar2 AS
        vr_coll_table tp_col_array;
        vr_coll_owner tp_col_array;

    BEGIN            
        create database link "database1"
        connect to my_name
        identified by "my_password"
        using 'database1';    

        SELECT owner, table_name
        bulk collect into vr_coll_owner, vr_coll_table
        FROM all_tables@database1
        WHERE OWNER NOT IN ('SYS'); 

    RETURN TO_CHAR(vr_coll_owner(1));      //just for temporary
END my_fn;

BEGIN

    DBMS_OUTPUT.PUT_LINE(my_fn('link1','schema1'));

END;

EDIT I also tried the following, but no luck :(

Execute immediate q'[create database link "database1"
        connect to my_name
        identified by "my_password"
        using 'database1']';

Define "no luck". Are you getting an error? If so, what error? If you create a database link dynamically in a PL/SQL block, every reference to that database link would also need to use dynamic SQL otherwise your block won't compile. Your `SELECT` statement would need to use `EXECUTE IMMEDIATE` as well. Stepping back, creating database links at runtime is generally a poor practice-- I'd seriously question why you're going down that path. — Justin Cave, Oct 22 '14 at 20:41
@JustinCave Using dynamic SQL for the select statement works great! Thank you! Why didn't you post this as an answer, so that I can mark it as correct answer. — Adrian, Oct 23 '14 at 21:29

score 0 · Accepted Answer · answered Nov 24 '14 at 21:33

If you create a database link dynamically in a PL/SQL block, every reference to that database link would also need to use dynamic SQL otherwise your block won't compile. Your SELECT statement would need to use EXECUTE IMMEDIATE as well. Stepping back, creating database links at runtime is generally a poor practice-- I'd seriously question why you're going down that path.

According to Justin Cave's comment

Bjarte Brandt · Answer 2 · 2014-10-22T20:47:32.400

Make sure the definer-schema is granted the "create database link" privilege.

This one is working:

me@XE> execute execute immediate 'create database link superlink connect to a identified by b using ''TNSALIAS''';

PL/SQL procedure successfully completed.

me@XE> @mylinks

DB_LINK         USERNAME        PASSWORD        HOST                      CREATED
--------------- --------------- --------------- ------------------------- --------------------
SUPERLINK       A                               TNSALIAS                  22.10.2014 22:42:19

PLSQL - Create DBLink inside function

2 Answers2