Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 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 || '''';
share|improve this answer
    
Thanks a lot....it worked.... –  SSS1 Jul 14 at 6:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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