Apex - PLSQL - PLSQL Code structure in APEX

Jasper Tanglib

Hi,

In Oracle 20.2, I have a code or Process in Oracle Apex and it has 52 unique conditions so it is kind of lengthy. It is about extracting contents of a file, there are 51 specific file names to consider plus the else condition, read its data, insert into a temporary table, and then inserting into a final big table.

There are two ways I know on how I can implement my code.

First is I can have all the code in 1 script or procedure in my database and then I will use Apex Process to call that procedure:

Example:

In database:

create or replace my_procedure as

begin

--52 conditions here;

end;

In Apex PLSQL process:

my_procedure();

Second way is, I have the option to reduce the code by creating a separate procedure for each 52 conditions.

Example:

In database:

create or replace first_condition_procedure as

begin

-- unique process here;

end;

create or replace second_condition_procedure as

begin

--unique process here;

end;

--and 50 more...

In Apex PLSQL process:

BEGIN

IF THEN

first_condition_procedure();

ELSIF THEN

second_condition_procedure();

--and 50 more...

END IF

END;

I am trying to consider the performance or impact it may have on the application.

Is there any advantage or disadvantage among the 2 different structures or will they be pretty much the same?

Any idea is appreciated!

-Jazz

jariola

You need measure performance.

But, I would try model logic/conditions to table where you can query it from procedures. Then you can build also APEX form to maintain your logic/conditions if those changes.

fac586

There isn't likely to be any significant performance difference between the two approaches.

Structurally the first option is the best approach from an APEX perspective. Implementing all of the code in a database program unit follows the software engineering principles of separation of concerns and loose coupling. Doing so reduces inter-component dependencies, which will allow the file extraction program to be cleanly unit tested and maintained separately to the APEX application(s).

However, instead of creating one single monolithic procedure, or multiple separate procedures, apply encapsulation and group the required methods into one or more PL/SQL packages. It's not possible to be definitive given such limited information, but the best approach would probably be to combine the two options: build a package where each of the conditional processes is implemented as a separate private procedure, with a single public procedure providing the callable entry point for APEX and other applications.

If you state the requirements in more detail and tell us more about the current/proposed solution then we might be able to provide further suggestions on how to improve things—including ways to reduce or even eliminate the need for separate implementations of 51 distinct operations.

Jasper Tanglib

https://community.oracle.com/tech/developers/discussion/comment/16793348#Comment_16793348

Thank you for this insight @fac586!

There is no proposed solution so I pretty much have the freedom to do things my way. Even though there would not be any significant differences, I'd like to consider the coding convention or proper structure of implementing the code for future use or self development.

Currently, I have 2 separate processes, one for uploading CSV files and this uses the 2nd structure mentioned above, and the other process is for uploading ZIP files and this uses the 1st structure. I am thinking of changing one of the structures among the 2 just to be formal. With that said, would you still recommend the 1st approach?