0

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.

John Doe
  • 57
  • 8
  • I would use a version parameter, and try to modularise the procedure as much as possible, simplifying it down so that the different versions are small coordination methods that call out to other procedures, perhaps passing different parameters to cause different effects. I hope this makes sense. – Jeffrey Kemp Mar 26 '22 at 11:50

2 Answers2

2

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;
Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • This is the obvious way to implement it ofc as I stated this as second option in my list. The problem is that function boo is rather complex and only about 30% will differ between versions, so I have to keep in mind that there is multiple instances which I have to support at the same time. That's not a big deal but I just tried to find a better way of handling this. – John Doe Mar 25 '22 at 07:36
1

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.

Pancho
  • 2,043
  • 24
  • 39
  • Thank you. I think this solution would not be suitable for my particular case because I want to keep the same entry point for API. Otherwise the users of this API would have to rebuild the app for each version. – John Doe Mar 25 '22 at 07:40
  • @JohnDoe ah, was hoping that your external app could prepend and make the call. It is of course possible to have a single entry point schema with which the application engages eg. APP and which then manages the internals APP.getMyStuff(versionNo => :versionNo) to which you pass the version no required and it manages the internals eg. app.getMyStuff(2) => v2.mypackage.myproc but then you will be back to your case / if / dynamic sql options – Pancho Mar 25 '22 at 10:12
  • Well I hoped for some silver bullet. Anyway, thanks for your reply. – John Doe Mar 25 '22 at 11:59