Pass the unix variable to the ddl command inside a pl/sql block

oracleplsqlsqlplus

I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from the current unix session, I want to connect to one sql database and then to create the db link to the another database using the input values. When I tried the below script, this is creating the db link successfully, but the values of VAR1 & VAR2 are not taken as 'xxx' and 'yyy', instead these are read as it is, i.e. the username is 'VAR1' and host name is 'VAR2'. How do I make sure that the values of these variables are passed instead of these? Thanks in advance.

VAR1="xxx"
VAR2="yyy"
sqlplus aaa/bbb @try.sql $VAR1 $VAR2

–Then below is my SQL (try.sql)

SET SERVEROUTPUT ON
DECLARE
VAR1 varchar2(100):='&&1';
VAR2 varchar2(100):='&&2';
BEGIN
execute immediate 'CREATE DATABASE LINK  try_lnk CONNECT TO VAR1 IDENTIFIED BY VAR1 USING ''VAR2''';
END;
/
exit

Best Answer

Instead of the string literal VAR1 and VAR2, use the variables in your EXECUTE IMMEDIATE statement:

execute immediate 'CREATE DATABASE LINK  try_lnk CONNECT TO ' || VAR1 || ' IDENTIFIED BY ' || VAR1 || ' USING ''' || VAR2 || '''';