PL/SQL - Call new instance of package procedure

Question

I have package that uses global variables. From one of the procedures i need to call "new instance" od another procedure (of the same package). I need this second procedure to use it's own version of global variables and when it's done i need first procedure to use variables as they were before. I did a quick and dirty fix, at the beginning of second procedure i create backup variables and put global variables values in it and then setting global variables to NULL and at the end i put backup values back to global variables. But i hope there is better solution than this one, a way to start new instance of that procedure/package

EDIT

Detailed scenario is like this: Package has multiple procedures and functions that use variables defined at package level. I call firs procedure of the package and that procedure calls everything that is needed for current action. In some cases from second procedure i need to start new task (action) that has to be executed. In that case i would like to start again first procedure in package, but for it to use its own global variables (not to mess with original ones) because original procedure is not yet finished and still needs it's variables Some of package global variables are custom type (table of custom_type to be exact) so it's creating additional problem for me.

Hope it's clear now what problem i have and what i'm trying to do So it's something like this

Package 1
 global_var 1
 global_var 2
 global_var 3

 Procedure 1
     some code
     call to function 1
     some code
     call to procedure 2
     and so on...
 Procedure 2
     some code
     call function 3
     some code
     call to procedure 1 (start new process)
     some code
     call procedure 3
     and so on....
 Procedure 3
 Procedure 4
 Function 1
 Function 2
 Function 3
 Function 4

And variables are like this

type type1 is record (x NUMBER, y VARCHAR2(1024), z NUMBER);
type type2 is table of type1;

global_var_1 NUMBER;
global_var_2 type2;
...

Types have instances, packages don't. Maybe you could place values in an array or global temporary table with some key to distinguish between sets of values, or else put the logic in an object type. — William Robertson
There is no conceptually different way to emulate object instances by package. You can use global associative array of records to access instance states by instance id and wrap it with getters and setters for example. — Konstantin Sorokin
Perhaps if you explain more in details the scenario you are dealing with, someone in this forum may be able to suggest a simpler (and feasible) alternative. — FDavidov
Whatever you do you do it wrong, man. If procedures need different values at a time - split them into different packages. If business logic requires the same procedures with different values, then it's better to revise a logic within procedures — Yuriy Tsarkov
What do you mean with "split them into different packages"? The second call is using all the same procedures as the first one. How can i split them? — BeRightBack

FDavidov FDavidov · Accepted Answer · 2016-12-10T08:47:00

After reading your entire post (with the additional description), I came to the conclusion that your problem starts with a wrongly conceived view of the required process. In fact (and lending words from other programming languages) you need your package to spawn new threads with their own data context.

I see two possible solutions (in Oracle):

Write your package in JAVA (here I'm assuming that this option does allow the creation of threads, but never checked it),
Do not use GLOBAL VARIABLES.

For the first option I have no much to say except that it may be worth for you to investigate further.

As for the second option, you can (quite easily in fact):

Define a type a package level that will contain all the variables that you are currently using at global level and:
Add to all your functions in the package a parameter of the new type (I would put it as the last one) with default value NULL. Wherever needed, the definition of the new parameter can be set as IN OUT (for the case that one or more of the fields within the structure is updated within a function and the update needs to "bubble-up" to the invoking code).

Now, let's analyze the two scenarios (first invocation of FIRST procedure and second invocation from within the package):

FIRST INVOCATION (from outside the package): The new parameter will be empty (NULL) and the procedure will assign whatever values are needed to cascade throughout additional invocations of other functions/procedures;
SECOND INVOCATION (from within the package): can include a non-NULL value for the new parameter (depending on your needs); the code will then be able to prepare a new set of values (for a new variable of the type described above) which will then be cascaded through the additional invocations of other functions and procedures.

This method achieves exactly what you need while (as far as I can imagine) the required code changes would be minimal.

Hope this addresses the question you posted and the proposed approach serves you well.

PL/SQL - Call new instance of package procedure

1 Answers