I have several scripts that are hard wired when creating a database link and all works well. The time has come though to take them to the next level and expand the user community. To do that the CREATE DATABASE LINK statement needs to be dynamically determine the host connect string at runtime. Reading the connect string is not the issue though.

Simply stated the 1st statement (hard coded) below works fine. The 2nd statement does not nor does the 3rd statement. Any ideas?

CREATE DATABASE LINK TempLink USING 'host_name.abc_xyz.com:1521/srvc_name.abc_xyx.com'; -- works fine

CREATE DATABASE LINK TempLink USING :Bind_Variable_1; -- fails, tried variable with and without quotes

EXECUTE IMMEDIATE :Bind_Variable_2; -- fails, variable = 1st statement

SQL> exec execute immediate 'create table :x (c int)' using 'MY_TABLE';
BEGIN execute immediate 'create table :x (c int)' using 'MY_TABLE'; END;

*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 1

SQL> variable x varchar2(100)
SQL> exec :x := 'MYTAB'

PL/SQL procedure successfully completed.

SQL> exec execute immediate 'create table '||:x||' (c int)';

PL/SQL procedure successfully completed.