Forum Stats

  • 3,757,165 Users
  • 2,251,204 Discussions


Apex - PLSQL - PLSQL Code structure in APEX

Jasper Tanglib
Jasper Tanglib Member Posts: 67 Green Ribbon


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:


In database:

create or replace my_procedure as


--52 conditions here;


In Apex PLSQL process:


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


In database:

create or replace first_condition_procedure as


-- unique process here;


create or replace second_condition_procedure as


--unique process here;


--and 50 more...

In Apex PLSQL process:






--and 50 more...



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!



  • jariola
    jariola Member Posts: 10,391 Silver Crown

    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.

    Jasper Tanglib
  • fac586
    fac586 Senior Technical Architect Member Posts: 19,913 Red Diamond
    edited Apr 26, 2021 8:52AM

    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 Tanglibjariola
  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    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?