oracle - PLSQL - Create DBLink inside function -

oracle - PLSQL - Create DBLink inside function -

i create database link within of script, , want receive table names linked database. if correct, need create database link in order use, oracle not allow me create such thing neither within of my_fn or declare section. suggestion?

declare type tp_col_array table of varchar2(1000); function my_fn( p_in_dblink_name in varchar2, p_in_schema_name in varchar2) homecoming varchar2 vr_coll_table tp_col_array; vr_coll_owner tp_col_array; begin create database link "database1" connect my_name identified "my_password" using 'database1'; select owner, table_name mass collect vr_coll_owner, vr_coll_table all_tables@database1 owner not in ('sys'); homecoming to_char(vr_coll_owner(1)); //just temporary end my_fn; begin dbms_output.put_line(my_fn('link1','schema1')); end;

edit tried following, no luck :(

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

if create database link dynamically in pl/sql block, every reference database link need utilize dynamic sql otherwise block won't compile. select statement need utilize execute immediate well. stepping back, creating database links @ runtime poor practice-- i'd question why you're going downwards path.

according justin cave's comment

oracle plsql dblink database-link