4 Replies Latest reply: Apr 18, 2013 11:41 PM by 1004062 RSS

    call stored procedure on a different db (dynamically)

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