How to insert values to a table in APEX, using a PACKAGE?

Question

Right now I'm inserting values to a table, with insert or update values, like this:

IF     :P903_PYMT_MTH_ID IS NOT NULL

THEN
INSERT INTO "TABLE_NAME" (
                                    PYMT_MTH_ID,
                                    PYMT_MTH_TYPE,
                                    PYMT_MTH_NM,
                                    CRT_DT_TM,
                                    MOD_DT_TM,
                                    USR_NM,
                                    LAST_TXN_DT            
                                      )                                      
VALUES     (
                :P903_PYMT_MTH_ID,
                :P903_PYMT_MTH_TYPE,
                :P903_PYMT_MTH_NM,
                sysdate,
                sysdate,    
                :P903_USR_NM,
                :P903_LAST_TXN_DT
           );
           
           COMMIT;

But now, in the office they need it to be done with a recent created package:

    PROCEDURE P_NEW(PN_PYMT_MTH_ID        NUMBER,
                 PV_PYMT_MTH_TP        VARCHAR2,
                 PV_PYMT_MTH_NM        VARCHAR2,
                 PV_USR_NM             VARCHAR2,
                 PN_RESULTADO          OUT NUMBER) AS
    VN_EXISTE NUMBER := 0;
 BEGIN

   PN_RESULTADO := 0;

   SELECT COUNT(1) INTO VN_EXISTE
      FROM "TABLE_NAME"
     WHERE PYMT_MTH_ID = PN_PYMT_MTH_ID;

How can I link every input in the original apex page with the package? I must say that it's my first time using a package, so I'm totally new in that field. Thanks in advance.

Note, you will get a number of benefits by moving your code into packages, including: performance, maintainability, testing, potential re-use. — Scott, Dec 06 '21 at 23:41

Koen Lostrie · Accepted Answer · 2021-12-06T19:21:13.823

To use a package on a form in apex, just replace the code in your form where you manually do the insert with a call to the package & procedure. Replace the "MYPKG" below with the actual package name.

DECLARE
  l_out_arg NUMBER;
BEGIN
  MYPKG.P_NEW(PN_PYMT_MTH_ID => :P903_PYMT_MTH_ID,
                 PV_PYMT_MTH_TP => :P903_PYMT_MTH_TYPE,
                 PV_PYMT_MTH_NM => :P903_PYMT_MTH_NM,
                 PV_USR_NM => :P903_USR_NM,
                 PN_RESULTADO => l_out_arg);
END;

The variable l_out_arg can be replaced with a page item so you could use it in a success message of your process or in another component (other process, branch) executed after this page process.

As a side note, since APEX 5.1 packages on tables can be generated automatically. It might be worth having a look at them. One very useful technique shown in the generated packages is the lost update detection using the MD5 hash. That is a feature that is easily missed when applications are written using packages.

Thank you so much, it worked this way, but when I click create, the row is created but it goes blank to the table, like I was inserting NULL values. [Screenshot](https://ibb.co/Xj5hn2j) — José Mora, Dec 06 '21 at 20:16
I solved it, it was a little error from my sequence in APEX, thank you so much for your help. Have a nice day! — José Mora, Dec 06 '21 at 20:47

How to insert values to a table in APEX, using a PACKAGE?

1 Answers1