This content has been marked as final. Show 4 replies
1001059 wrote:I am not sure I would classify as "correct", but what I did was effective.
I would like to call a sp on a different db. I know how I can hard code the db name in this case @dev.
This works just fine but I would like to be able to pass the dbname into the call to the sp. ie when I move to UAT and prod
Bind variables dont work in this case. What is the correct way of achieving this?
Thanks in advance,
my sql always used the literal "@REMOTE" & prior to using the sql I would estable a private DBLINK
such that "REMOTE" was directed to the desired database.
Better to have same but meaningful names in DEV, UAT and PROD environments..
You will not chnage table names or package names in UAT and PROD, right?
Then why should you change in case of DB links?
using a dblink sounds very sensible. As soon as I get priviledges to create db links I will implement the solution.
Thanks for the prompt response. much appreciated!
I am dynamically forming the string (l_string), with in this i even include BEGIN and END, after that just say execute immediate, it will execute the block (in your case i.e., SP)
set serveroutput on declare dbname varchar2(10):='@UAT'; l_string varchar2(4000); begin l_string:='begin id.packagename.procname:'||dbname||'(); end;'; dbms_output.put_line('l_string value is ..'||l_string); --execute immediate l_string; end; /
Kashyap Varma N