How to remotely execute a procedure when db link is a variable in PL/SQL?
Two databases are linked.
IN DB2, have a procedure p1
WOULD LIKE TO EXECUTE p1 in DB1. If we put the known db link name specifically, that is OK. For example,
in DB1, in pl/sql block
p1@DB1.DOMAIN;
It runs well.
But if we make a variable,
v_dblink := 'DB1.DOMAIN';
p1@v_dblink;
It complains.
Sorry to ask the variable quetions one more time. It really stops me.
Gurus, any advice??
IN DB2, have a procedure p1
WOULD LIKE TO EXECUTE p1 in DB1. If we put the known db link name specifically, that is OK. For example,
in DB1, in pl/sql block
p1@DB1.DOMAIN;
It runs well.
But if we make a variable,
v_dblink := 'DB1.DOMAIN';
p1@v_dblink;
It complains.
Sorry to ask the variable quetions one more time. It really stops me.
Gurus, any advice??
jaiminpsoni
I am not sure about the requirement, but is it possible to create synonym for the procedure on the remote DB and then execute the procedure on remote DB using synonym?
ASKER
Have to connect different DB links in different envs running the same code, so this is why I need a variable there.
ASKER
For example, I get the instance name from v%instace and depend on what it is about to form the db link.
ASKER
I was already back one step. I tried to make the following work
if dm = 'A' then
p1@DB1.DMA;
elsif dm = 'B' then
p1@DB1.DMB;
elsif dm = 'C' then
....
end if;
if dm = 'A' then
p1@DB1.DMA;
elsif dm = 'B' then
p1@DB1.DMB;
elsif dm = 'C' then
....
end if;
ASKER
I got the error:
91/4 PL/SQL: Statement ignored
91/4 PLS-00201: identifier
'p1@DB1.DMB' must be declared
If i was in dm = "A' env
91/4 PL/SQL: Statement ignored
91/4 PLS-00201: identifier
'p1@DB1.DMB' must be declared
If i was in dm = "A' env
I don't follow that latest posts but the original question about variables: you need to use execute immediate with dymanic sql.
The latest error might be due to the compiler not being able to see db1.dmb from the database you are in.
The latest error might be due to the compiler not being able to see db1.dmb from the database you are in.
ASKER
slightwv:
2 questions for you:
1) in pl/sql block (v_sqlttext is a procedure in remote database, NOT DML or DDL), is
execute immediate v_sqltext;
still valid. It seems not. How to deal with it.
2) If 1) is hard to achieve, I have to use the way to separate the env.
if dm = 'A' then
p1@DB1.DMA;
elsif dm = 'B' then
p1@DB1.DMB;
elsif dm = 'C' then
....
end if;
If compiler could not see the "p1@DB1.DMB; ", how to let the compiler see it???
Greatly appreciate your help.
2 questions for you:
1) in pl/sql block (v_sqlttext is a procedure in remote database, NOT DML or DDL), is
execute immediate v_sqltext;
still valid. It seems not. How to deal with it.
2) If 1) is hard to achieve, I have to use the way to separate the env.
if dm = 'A' then
p1@DB1.DMA;
elsif dm = 'B' then
p1@DB1.DMB;
elsif dm = 'C' then
....
end if;
If compiler could not see the "p1@DB1.DMB; ", how to let the compiler see it???
Greatly appreciate your help.
>>But if we make a variable,
This is where I'm confused. Are you using variables or not?
It appears that you might have shifted the question.
This is where I'm confused. Are you using variables or not?
It appears that you might have shifted the question.
ASKER
Yes. Use a variable for a procedure located in a remote database.
ASKER
Execute it in a pl/sql in local DB. How to do it??
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jaiminpsoni: thanks a lot. It works.
ASKER
Very good.