Generating APEX form/grid PL/SQL handlers (processing procedures)

M

Many times I create an interactive grid not just on a simple table, but on a more complex view with transformed rows/columns (pivot/unpivot) with the need to store data in multiple tables on submit. I face similar difficulties when handling complex forms. Then I have to create a custom procedure to handle the grid/form submit and then pass columns from grid (or items from form) to this procedure call. That is a lot of tedious work to write these handlers over and over again.

Check this GEN package, it will generate all of this code for you and with nice formatting. You can easily customize this to fit your needs and style.

Generating the procedure

Imagine you have this table and you have more complex view based on this table.

CREATE TABLE plan_certifications (
    cert_id             VARCHAR2(20)    NOT NULL,
    cert_name           VARCHAR2(100)   NOT NULL,
    path_id             VARCHAR2(30),
    --
    exam_page_link      VARCHAR2(256),
    credly_link         VARCHAR2(256),
    study_link          VARCHAR2(256),
    study_hours         VARCHAR2(10),
    --
    questions           NUMBER(4),
    minutes             NUMBER(4),
    pass_ratio          NUMBER(4),
    price               NUMBER(8),
    --
    CONSTRAINT pk_plan_certifications
        PRIMARY KEY (cert_id),
    --
    CONSTRAINT fk_plan_certifications_path
        FOREIGN KEY (path_id)
        REFERENCES plan_paths (path_id)
);

When you save your grid or form you want to store submitted data back to this table. So you need that custom PL/SQL processing handler. From now on, you can run this:

DECLARE
    in_table_name           user_tables.table_name%TYPE := 'PLAN_CERTIFICATIONS';
BEGIN
    gen.create_handler (
        in_table_name       => in_table_name,
        in_target_table     => NULL,
        in_proc_prefix      => 'save_'
    );
END;
/

And it will generate this code for you:

    PROCEDURE save_plan_certifications (
        in_action               CHAR,
        in_cert_id              plan_certifications.cert_id%TYPE,
        in_cert_name            plan_certifications.cert_name%TYPE,
        in_path_id              plan_certifications.path_id%TYPE             := NULL,
        in_exam_page_link       plan_certifications.exam_page_link%TYPE      := NULL,
        in_questions            plan_certifications.questions%TYPE           := NULL,
        in_minutes              plan_certifications.minutes%TYPE             := NULL,
        in_pass_ratio           plan_certifications.pass_ratio%TYPE          := NULL,
        in_price                plan_certifications.price%TYPE               := NULL,
        in_credly_link          plan_certifications.credly_link%TYPE         := NULL,
        in_study_link           plan_certifications.study_link%TYPE          := NULL,
        in_study_hours          plan_certifications.study_hours%TYPE         := NULL
    ) AS
        rec                     plan_certifications%ROWTYPE;
    BEGIN
        tree.log_module();
        --
        rec.cert_id             := in_cert_id;
        rec.cert_name           := in_cert_name;
        rec.path_id             := in_path_id;
        rec.exam_page_link      := in_exam_page_link;
        rec.questions           := in_questions;
        rec.minutes             := in_minutes;
        rec.pass_ratio          := in_pass_ratio;
        rec.price               := in_price;
        rec.credly_link         := in_credly_link;
        rec.study_link          := in_study_link;
        rec.study_hours         := in_study_hours;
        --
        DELETE FROM plan_certifications t
        WHERE t.cert_id         = rec.cert_id;
        --
        BEGIN
            INSERT INTO plan_certifications
            VALUES rec;
        EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            UPDATE plan_certifications t
            SET ROW = rec
            WHERE t.cert_id         = rec.cert_id;
        END;
        --
        tree.update_timer();
    EXCEPTION
    WHEN tree.app_exception THEN
        RAISE;
    WHEN OTHERS THEN
        tree.raise_error();
    END;

The in_target_table is very useful if you want to generate different inputs to the procedure (based on in_table_name object, typically view) but the DML itself is done on in_target_table object (typically target table or multiple tables).

You can and should customize this code, for example remove the tree package references I use for code instrumenting, logging and exception handling. Maybe you dont need the delete statement. Maybe you need this as a function returning assigned id from a sequence back to the grid. Maybe you dont need the action argument (for passing :APEX$ROW_STATUS)... This is up to you.

Generating the call

Run this script to generate caller for this procedure (you can pass package name too or even a table/view name):

DECLARE
    in_object_name          user_objects.object_name%TYPE := 'SAVE_PLAN_CERTIFICATIONS';
BEGIN
    gen.handler_call (
        in_procedure_name       => in_object_name
    );
END;
/

And check the generated code:

save_plan_certifications (
    in_action               => :APEX$ROW_STATUS
    in_cert_id              => :CERT_ID,
    in_cert_name            => :CERT_NAME,
    in_path_id              => :PATH_ID,
    in_exam_page_link       => :EXAM_PAGE_LINK,
    in_questions            => :QUESTIONS,
    in_minutes              => :MINUTES,
    in_pass_ratio           => :PASS_RATIO,
    in_price                => :PRICE,
    in_credly_link          => :CREDLY_LINK,
    in_study_link           => :STUDY_LINK,
    in_study_hours          => :STUDY_HOURS
);

If you pass page_id, you can generate call for processing form.

DECLARE
    in_object_name          user_objects.object_name%TYPE := 'SAVE_PLAN_CERTIFICATIONS';
BEGIN
    gen.handler_call (
        in_procedure_name       => in_object_name,
        in_app_id               => NULL,
        in_page_id              => 100          -- NULL for grid, page_id for forms
    );
END;
/

And check the generated code:

save_plan_certifications (
    in_action               => :APEX$ROW_STATUS
    in_cert_id              => :P100_CERT_ID,
    in_cert_name            => :P100_CERT_NAME,
    in_path_id              => :P100_PATH_ID,
    in_exam_page_link       => :P100_EXAM_PAGE_LINK,
    in_questions            => :P100_QUESTIONS,
    in_minutes              => :P100_MINUTES,
    in_pass_ratio           => :P100_PASS_RATIO,
    in_price                => :P100_PRICE,
    in_credly_link          => :P100_CREDLY_LINK,
    in_study_link           => :P100_STUDY_LINK,
    in_study_hours          => :P100_STUDY_HOURS
);

How to generate views you can check in View generator article.

Your life as APEX developer should be now a way easier. You are welcome.