Home > front end >  How to insert values to a table in APEX, using a PACKAGE?
How to insert values to a table in APEX, using a PACKAGE?

Time:12-07

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.

CodePudding user response:

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.

  • Related