Can I make two procedures that call each other out in Oracle?

Question

I have a database to store sales information. I am currently writing two procedures, one to insert new details about a sale, and another one to update an already existing detail. If the user wants to insert an already existing detail, I want my procedure to call the update procedure. Likewise, if the user wants to update a non-existing detail, I want to call the insert procedure. My code to insert a new detail is as follows:

CREATE OR REPLACE procedure EX1.insert_detail
    (id_sale IN INTEGER, id_product IN INTEGER, qty IN INTEGER)
IS
    id_detail INTEGER;
    mi_seq INTEGER;
    u_price FLOAT;
BEGIN
    BEGIN
        select detail.id_sales_order_detail into id_detail from EX1.sales_order_detail detail
            where detail.id_sales_order = id_sale and detail.id_prod = id_product;
    END;
    if id_detail is null then
        select seq_sales_order_detail.nextval into mi_seq from dual;
        select prod.list_price into u_price from EX1.product prod;

        insert into EX1.sales_order_detail values
            (mi_seq, id_sale, id_product, qty, u_price, EX1.calc_discount(qty, u_price));
        BEGIN
            EX1.update_costs(id_sale);
        END;
    else
        EX1.update_detail(id_sale, id_product, qty);
    end if;
END insert_detail;

To update a sales detail:

CREATE OR REPLACE procedure EX1.update_detail
    (id_sale IN INTEGER, id_product IN INTEGER, qty IN INTEGER)
IS
    id_detail INTEGER;
BEGIN
    BEGIN
        select detail.id_sales_order_detail into id_detail from EX1.sales_order_detail detail
            where detail.id_sales_order = id_sale and detail.id_prod = id_product;
    END;
    if id_detail is null then
        BEGIN
            EX1.INSERT_DETAIL(id_sale, id_product, qty);
        END;
    else
        UPDATE EX1.SALES_ORDER_DETAIL
            SET ORDER_QTY = ORDER_QTY + qty
        WHERE ID_SALES_ORDER = id_sale AND id_prod = id_product;
        BEGIN
            EX1.update_costs(id_sale);
        END;
    end if;
END update_detail;

I first compiled each procedure without calling the other one. Once I did this, I rewrote the insert procedure to call the update proc. I compiled it and everything was fine. The problem came when I tried to call the insert procedure in my update procedure.

I am getting the following error at the time of compiling: ex1.insert_detail is invalid. However, before compiling ex1.update_detail, all procedures are valid. Somehow, having each procedure calling the other one is making them both invalid.

Is there anyway I can accomplish this? Or should I just write all code in each procedure without calling the other one?

Any help or guidance in this issue will be greatly appreciated.

Answer 1

You don't really need two procedures. Just do one with a MERGE instead. Something like the following should be a good start;

CREATE OR REPLACE procedure EX1.upd_ins_sales_detail(p_id_sale IN INTEGER, p_id_product IN INTEGER, p_qty IN INTEGER)
IS

BEGIN

    MERGE INTO ex1.sales_order_detail sod
    USING (SELECT exist_sod.id_sales_order_detail, exist_sod.id_prod, prod.list_price
           FROM   ex1.sales_order_detail exist_sod
                  INNER JOIN
                  ex1.product prod ON (prod.prod_id = exist_sod.prod_id)
           WHERE  exist_sod.id_sales_order = p_id_sale
           AND    exist_sod.id_prod = p_id_product
          ) upd
    ON    (    sod.id_sales_order_detail = upd.id_sales_order_detail)
    WHEN MATCHED THEN UPDATE SET sod.qty = sod.qty + p_qty
    WHEN NOT MATCHED THEN INSERT (mi_seq, id_sale, id_product, qty, u_price, EX1.calc_discount(qty, u_price))
                          VALUES (seq_sales_order_detail.nextval,
                                  p_id_sale,
                                  p_id_product,
                                  upd.list_price,
                                  EX1.calc_discount(p_qty, upd.list_price)

    EX1.update_costs(id_sale);

    COMMIT;

EXCEPTION
WHEN ..... THEN
  put an exception as required here

END upd_ins_sales_detail; 

Answer 2

Use a package:

CREATE OR REPLACE PACKAGE TEST_CIRCULAR_REFERENCE
IS
  PROCEDURE A( value NUMBER );
  PROCEDURE B( value NUMBER );
END;
/

CREATE OR REPLACE PACKAGE BODY TEST_CIRCULAR_REFERENCE
IS
  PROCEDURE A( value NUMBER )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'A: ' || value );
    IF value <= 1 THEN
      NULL;
    ELSIF MOD( value, 2 ) = 0 THEN
      A( value / 2 );
    ELSE
      B( value );
    END IF;
  END;

  PROCEDURE B( value NUMBER )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'B: ' || value );
    IF value <= 1 THEN
      NULL;
    ELSIF MOD( value, 2 ) = 0 THEN
      A( value );
    ELSE
      B( 3 * value + 1 );
    END IF;
  END;
END;
/

So your package would be something like:

CREATE OR REPLACE PACKAGE EX1.DETAILS
IS
  FUNCTION get_detail_id ( id_product EX1.sales_order_detail.id_prod%TYPE )
    RETURN EX1.sales_order_detail.id_sales_order_detail%TYPE;

  PROCEDURE insert_detail(
    id_sale    IN EX1.sales_order_detail.id_sale%TYPE
    id_product IN EX1.sales_order_detail.id_prod%TYPE,
    qty        IN EX1.sales_order_detail.id_qty%TYPE
  );

  PROCEDURE update_detail(
    id_sale    IN EX1.sales_order_detail.id_sale%TYPE
    id_product IN EX1.sales_order_detail.id_prod%TYPE,
    qty        IN EX1.sales_order_detail.id_qty%TYPE
  );
END;
/

Answer 3

The problem is you have a cyclic dependency between the two procedures. Compiling an object on which a program depends invalidates that program: cyclic dependency means you're stuck in a perpetual loop of invalidation and recompilation.

One solution is to use a package. Objects depend on the package spec, so providing that doesn't change we can do anything with the package body.

 create or replace package pkg_sales as
     procedure insert_detail(id_sale IN INTEGER, id_product IN INTEGER, qty );
    procedure update_detail(id_sale IN INTEGER, id_product IN INTEGER, qty );
end pkg_sales;

Then your body can do the referencing like this:

CREATE OR REPLACE  package body pkg_sales as

    procedure insert_detail(id_sale IN INTEGER, id_product IN INTEGER, qty IN INTEGER)
    IS
        id_detail INTEGER;
        mi_seq INTEGER;
        u_price FLOAT;
    BEGIN
        BEGIN
            select detail.id_sales_order_detail into id_detail from EX1.sales_order_detail detail
                where detail.id_sales_order = id_sale and detail.id_prod = id_product;
        END;
        if id_detail is null then
            select seq_sales_order_detail.nextval into mi_seq from dual;
            select prod.list_price into u_price from EX1.product prod;

            insert into EX1.sales_order_detail values
                (mi_seq, id_sale, id_product, qty, u_price, EX1.calc_discount(qty, u_price));
            BEGIN
                EX1.update_costs(id_sale);
            END;
        else
            update_detail(id_sale, id_product, qty);
        end if;
    END insert_detail;

   procedure update_detail(id_sale IN INTEGER, id_product IN INTEGER, qty IN INTEGER)
    IS
        id_detail INTEGER;
    BEGIN
        BEGIN
            select detail.id_sales_order_detail into id_detail from EX1.sales_order_detail detail
                where detail.id_sales_order = id_sale and detail.id_prod = id_product;
        END;
        if id_detail is null then
            BEGIN
                INSERT_DETAIL(id_sale, id_product, qty);
            END;
        else
            UPDATE EX1.SALES_ORDER_DETAIL
                SET ORDER_QTY = ORDER_QTY + qty
            WHERE ID_SALES_ORDER = id_sale AND id_prod = id_product;
            BEGIN
                update_costs(id_sale);
            END;
        end if;
    END update_detail;
end pkg_sales;

Cyclic dependency is still bad though. It points to a design flaw. A better solution would be to have one procedure, procedure manage_detail(id_sale IN INTEGER, id_product IN INTEGER, qty IN INTEGER) which does the control logic, and decides whether to invoke an insert or update sub-routine accordingly.

Alternatively, for this situation there is the MERGE statement, which handles whether to insert or update (or indeed delete) in pure SQL. Find out more.

---

"am I better just writing the code to perform each procedure on its own"

Probably not. You'll end up duplicating some code. Modularity is a good thing, but it's even better to structure your logic to avoid cyclic dependency. Apart from anything else you risk infinite recursion.