Managing version of pl/sql procedures/functions-CodePudding

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.

CodePudding user response：

Given your criteria " there is a need to return different output structures keeping the same entrypoint", if they all must be available at the same time, I believe the best choice is to have a version parameter. If these will reside in different schemas or databases, you could use conditional compilation to compile only the appropriate version.

Here is my example using overloaded functions with a version parameter.

CREATE OR REPLACE PACKAGE multiversion
    AUTHID DEFINER
AS
    FUNCTION boo (p_val IN VARCHAR2)
        RETURN VARCHAR2;

    FUNCTION boo (p_char IN VARCHAR2)
        RETURN VARCHAR2;


    FUNCTION boo (p_char2 IN VARCHAR2)
        RETURN VARCHAR2;

    FUNCTION boo (p_value IN VARCHAR2, p_version IN VARCHAR2)
        RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY multiversion
AS
    FUNCTION boo (p_val IN VARCHAR2)
        RETURN VARCHAR2
    AS
    BEGIN
        RETURN 'p_val: ' || p_val;
    END boo;

    FUNCTION boo (p_char IN VARCHAR2)
        RETURN VARCHAR2
    AS
    BEGIN
        RETURN 'p_char: ' || p_char;
    END boo;

    FUNCTION boo (p_char2 IN VARCHAR2)
        RETURN VARCHAR2
    AS
    BEGIN
        RETURN 'p_char2: ' || p_char2 || ' ' || p_char2;
    END boo;

    FUNCTION boo (p_value IN VARCHAR2, p_version IN VARCHAR2)
        RETURN VARCHAR2
    AS
        l_ret   LONG;
    BEGIN
        l_ret   :=
            CASE p_version
                WHEN 'a' THEN boo (p_val => p_value)
                WHEN 'b' THEN boo (p_char => p_value)
                WHEN 'c' THEN boo (p_char2 => p_value)
            END;

        IF l_ret IS NULL
        THEN
            raise_application_error (
                -20000
              , COALESCE (p_version, '<<null>>') || ' is not a known version');
        END IF;

        RETURN l_ret;
    END boo;
END;

SELECT multiversion.boo (p_value => 'this is a test', p_version => 'c')
  FROM DUAL;

CodePudding user response：

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.