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.
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