This discussion is archived
4 Replies Latest reply: Apr 18, 2013 9:41 PM by 1004062 RSS

call stored procedure on a different db (dynamically)

1004062 Newbie
Currently Being Moderated
Hi All,

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.

tablespace.packagename.procname@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

declare
dbname varchar2(10):='@UAT';
begin
id.packagename.procname:dbname();
end;

Bind variables dont work in this case. What is the correct way of achieving this?

Thanks in advance,

Luke (newbie)
  • 1. Re: call stored procedure on a different db (dynamically)
    sb92075 Guru
    Currently Being Moderated
    1001059 wrote:
    Hi All,

    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.

    tablespace.packagename.procname@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

    declare
    dbname varchar2(10):='@UAT';
    begin
    id.packagename.procname:dbname();
    end;

    Bind variables dont work in this case. What is the correct way of achieving this?

    Thanks in advance,

    Luke (newbie)
    I am not sure I would classify as "correct", but what I did was effective.

    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.
  • 2. Re: call stored procedure on a different db (dynamically)
    jeneesh Guru
    Currently Being Moderated
    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?
  • 3. Re: call stored procedure on a different db (dynamically)
    1004062 Newbie
    Currently Being Moderated
    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!

    Luke
  • 4. Re: call stored procedure on a different db (dynamically)
    nkvkashyap Explorer
    Currently Being Moderated
    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;
    /
    Regards,
    Kashyap Varma N

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points