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??
jl66Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jaiminpsoniConnect With a Mentor Commented:
You can achieve 2 using following....

proc := 'testproc;';
execute immediate('begin '||proc||' end;');

Have you tried that?

I guess this should work for 1 as well.

following may help you...

http://www.dulcian.com/Articles/Execute_Immediate.htm

0
 
jaiminpsoniCommented:
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?

0
 
jl66Author Commented:
Have to connect different DB links in different envs running the same code, so this is why I need a variable there.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jl66Author Commented:
For example, I get the instance name from v%instace and depend on what it is about to form the db link.
0
 
jl66Author Commented:
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;

 
0
 
jl66Author Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
jl66Author Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
jl66Author Commented:
Yes. Use a variable for a procedure located in a remote database.
0
 
jl66Author Commented:
Execute it in a pl/sql in local DB. How to do it??
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Use execute immediate when using variables and dynamic sql.

Where are we crossing wires?
0
 
jl66Author Commented:
jaiminpsoni: thanks a lot. It works.
0
 
jl66Author Commented:
Very good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.