How do I manage two versions of a stored procedure at the same time? Here is the situation: There are some stored procedures (3 - 5 no's) already in production. We made some changes for a fix. It also involved changes to the code that is using these stored procedures.
I am looking for a strategy that can help us achieve this:
Let the old code in production use the old version of the stored procedures. So when I deploy the newer version of the stored procedures, and the old code still running in production (no down time) it should use the old version of the stored procedure so the application does not break.
Once I deploy the new code, it should start using the newer version of the stored procedures.
This is typical use case for edition-based redefinition. You initially create an edition and a service that the clients use for connecting, to that speficic edition:

SQL&gt; alter user bp enable editions for procedure;

User altered.

SQL&gt; conn bp/bp
Connected.

SQL&gt; create edition e1;

Edition created.

SQL&gt; alter session set edition=e1;

Session altered.

SQL&gt; create or replace procedure p1 as
  2  begin
  3    dbms_output.put_line('Edition e1.');
  4  end;
  5  /

Procedure created.

SQL&gt; exec dbms_service.create_service('myservice', 'myservice', edition=&gt;'e1');

PL/SQL procedure successfully completed.

SQL&gt; exec dbms_service.start_service('myservice');

PL/SQL procedure successfully completed.


This is what you get if you execute the procedure:

SQL&gt; conn bp/bp@localhost:1521/myservice
Connected.
SQL&gt; set serveroutput on
SQL&gt; exec p1
Edition e1.

PL/SQL procedure successfully completed.


This session remains alive and continues its work. Now you want to deploy a new version of the procedure. In another session:

SQL&gt; conn bp/bp@localhost:1521/myservice
Connected.
SQL&gt; create edition e2;

Edition created.

SQL&gt; alter session set edition=e2;

Session altered.

SQL&gt; create or replace procedure p1 as
  2  begin
  3    dbms_output.put_line('Edition e2.');
  4  end;
  5  /

Procedure created.


Then modify the service to use the new edition:

SQL&gt; exec dbms_service.modify_service('myservice', edition=&gt;'e2', modify_edition=&gt;true);

PL/SQL procedure successfully completed.


New sessions connecting through this service will use the new version of the package:

SQL&gt; conn bp/bp@localhost:1521/myservice
Connected.
SQL&gt; set serveroutput on
SQL&gt; exec p1
Edition e2.

PL/SQL procedure successfully completed.


While back in the original session:

SQL&gt; exec p1
Edition e1.

PL/SQL procedure successfully completed.


If the old session ends then simply reconnects, using the same service it used for connecting earlier, it will start using the new procedure as well:

SQL&gt; conn bp/bp@localhost:1521/myservice
Connected.
SQL&gt; set serveroutput on
SQL&gt; exec p1
Edition e2.

PL/SQL procedure successfully completed.


More about this: Using Edition-Based Redefinition