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']';

Answer 1

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