1

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

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.