Forum Stats

  • 3,769,311 Users
  • 2,252,947 Discussions
  • 7,874,984 Comments

Discussions

Calling procedures from a procedure

user545194
user545194 Member Posts: 577 Bronze Badge

Hi,

Oracle 19c SE2 (19.0.0.0.0) on Linux

A co-worker asked me to run 7 procedures by calling them from another procedure. Each procedure inserts similar data into the same table, but with different information. The parameters apply to all 7 procedures. This approach does not seem to be best practices.

Code example:

create or replace PROCEDURE call_02_07_proc (

  p_1 IN VARCHAR2,

  p_2 IN VARCHAR2,

  p_3    OUT VARCHAR2,

  p_4  OUT VARCHAR2

)

  

AS 


  -- Variables

  v_1 NUMBER := p_1;

  v_2 NUMBER := p_2;

  v_3 VARCHAR2(1);

  v_4 VARCHAR2(50);

   

 BEGIN


 -- Call procedures

  02_proc(v_1, v_2, v_3, v_4);

 03_proc(v_1, v_2, v_3, v_4);

 04_proc(v_1, v_2, v_3, v_4);

 05_proc(v_1, v_2, v_3, v_4);

 06_proc(v_1, v_2, v_3, v_4);

 07_proc(v_1, v_2, v_3, v_4);

   

END call_02_07_proc;

/

Wouldn't it be better to put the logic in a package using cursors, for example? That way the task could run as a unit.

Thanks!

Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,594 Red Diamond

    Refactoring code is always a consideration. Instrumentation should be considered as a mandatory requirement.

    Code needs to be structured. Name references need to be meaningful.

    PL/SQL is an implementation of the Ada language. Ada programming standards and style should be used - not braindead Hungarian notation and uppercase. See https://en.wikibooks.org/wiki/Ada_Style_Guide/Introduction

    For data (row) crunching, SQL should be maximised and PL/SQL minimised. And no, bulk processing in PL/SQL does NOT turbo boost processing.

    Get these fundamentals right, and your code will be robust, optimal, and easy to manage and maintain.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @user545194

    Each procedure inserts similar data into the same table, but with different information. The parameters apply to all 7 procedures. This approach does not seem to be best practices.

    What you said sure does make you wonder if the procedures should be combined, but it's not necessarily bad practice. It depends on exactly what the procedures do, and how different people you them. For example, you need to call all seven procedures, but someone else may need to call only one or two of them.

     -- Call procedures

      02_proc(v_1, v_2, v_3, v_4);

     03_proc(v_1, v_2, v_3, v_4);

    Oracle identifiers (such as table names and procedure names) normally can't start with a digit. 02_proc is a really bad procedure name; proc_02 or my_02_proc would be much better.

    Wouldn't it be better to put the logic in a package

    You should always think about putting procedures in a package, especially if they do similar things, or if they are used together. Again, it depends on what exactly the procedures are doing and how they are used (by all users, not just you).

  • user545194
    user545194 Member Posts: 577 Bronze Badge

    Thanks to all for your feedback!

    @Frank Kulash The code was abbreviated. The procedure names have text before the number; app_02_proc.

    Let's say we must implement the procedure call as is. Ok, so we are querying the same table, but with different information in each query and inserting the results into a base table.

    Googling didn't return useful examples. Maybe you have something similar on hand that you could share?

    Thanks!

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @user545194

    Let's say we must implement the procedure call as is. Ok, so we are querying the same table, but with different information in each query and inserting the results into a base table.

    Sorry, I don't know what you want. Can you post an example of two procedures that are doing similar inserts based on the same table (like your seven real procedures do, but much simpler)?

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,594 Red Diamond

    Googling didn't return useful examples. Maybe you have something similar on hand that you could share?

    I'll rather share software engineering fundamentals. The teach how to fish approach.

    The vast majority of s/w processing have 3 basic steps.

    Input. Processing. Output.

    Input needs to be ensured as valid for the processing step to be started. In PL/SQL this means checking parameter values passed as input, ensuring that it does not violate business rules and business logic, and so on.

    Processing it then initiated based on this input. In this step SQL should be used as far as possible, and not PL/SQL slow by slow row-by-row processing .

    The output is also typically created by SQL too in the processing step. E.g. process invoice data (select rows) and output (row inserts) customer monthly billing.

    With data processing, you want to do the absolute minimum to achieve the desired output. For example hit the input table once with a read (SQL select), and not hit it again and again - a single pass and not multiple passes.

    Software also needs to be structured and modularised.

    This means that your code that validates the input should be input-processing-output modules in themselves. For example the input for your billing process might be a date range of invoices to process. A module can be used to check the input date range, verify it as valid, and output a Boolean as check result.

    Now add instrumentation to it - structured modules that for example get start date & time of a process. That processes this (e.g. get current session CPU time) and output (insert row) process status data into a table.

    When the end of the billing process is reached, the relevant instrumentation module is called and processes the CPU time (determines spend CPU time) and outputs (update row) process status data in a table.

    This is software engineering in a nutshell - relevant in Cobol over 50 years ago, and still relevant in Java and C/C++ and PL/SQL, today. A good developer groks this in all its beauty and simplicity.