Search code examples
oracleplsqlplsql-package

Managing version of pl/sql procedures/functions


I am searching for best practices for supporting multiple versions of procedures/functions. For example, I have a procedure that generates complicated json output with oracle apex packages, which is used by application to draw some front-end.

And now there is a need to return different output structures keeping the same entrypoint.

I see couple options:

  • include version parameter and route between code versions with if statements - that will make code messy
  • include version parameter and create multiple versions of procedure inside package - code duplications with known consequences
  • include version parameter and create multiple versions packages - even more duplications

I've checked some Oracle mechanisms, the only thing I found was Edition-based redefinition (EBR), but that don't suits me.


Solution

  • I'm not sure of the level of segregation required, and there is some additional security complexity to deal with, but in principle a possible approach could be to create an Oracle schema per version:

    eg schemas v1,v2,v3,....vN

    and store the package version in its associated schema, thus making use of the schema as the logical version namespace.

    Then, to reference for example version 2 of myproc in mypackage, the calling app would request:

    v2.mypackage.myproc

    To request version N the calling app would request:

    vN.mypackage.myproc

    thus simply changing the requested schema prefix in the call to derive the mypackage.myproc version required

    Common components could be stored in say a schema called common and shared to all v1..N schemas to prevent code duplication.

    ...but without knowing much more detail I unfortunately can't be sure whether this approach is feasible for your use case.