APEX

Hi all,

I've been struggling for days to find a solution for entering data into a table at the time of closing a modal dialog.

Use Case: I want to add a sysdate to a dataset in a table upon closing dataset creation in a modal dialog.

I have an interactive report (IR) based on a table containing, among others

• ID (PK)
• some columns
• and a created_on column (default sysdate), plain and simple

Based on that, there's a modal dialog page (MD).

When I click on create in the IR, MD opens where a row can be added to the table. The IR afterwards refreshes the IR region (all out of the box, really).

Problem

Now what I want is that sysdate should be inserted into created_on when the MD is closed by hitting the create button in the MD. I've tried to simply add sysdate into the form, which is relatively easy, but the point is that it's the sysdate at the beginning of entering data, not the sysdate of the point in time of actually creating the dataset.

Also, putting sysdate as a default value in the table doesn't do anything. If I create a row in the table in the object browser, the sysdate remains empty.

I've searched for days now for a way to perform an SQL statement upon closure of the MD (by hitting create button). But in the MD page, PL/SQL doesn't accept an insert statement (only retrieval of data from the table to the form).

Basically, it's the questions:

1. How can data be inserted into a(ny) table from a modal dialog button click that also closes the dialog?
2. Can it even be done by means of a procedure?

Temporary Solutions

1. I succeeded in putting a process into the MD before the default process Process form with the PL/SQL content :MD_CREATED_ON := SYSDATE;, however, I don't like the approach of handling it with MD values instead of using an SQL statement that makes sure the data is inserted into the table directly, and also, I don't know if the process execution is upon clicking the create button in the MD.
2. As for the second question (procedure), I already tried this procedure code:

CREATE OR REPLACE PROCEDURE set_created_on (
    p_tablename IN VARCHAR2,
    p_id_column IN VARCHAR2,
    p_id IN NUMBER
) IS
    sql_statement VARCHAR2(1000);
    l_table_count NUMBER;
    l_column_count NUMBER;
BEGIN
    -- Validierung: Tabelle muss existieren
    SELECT COUNT(*)
    INTO l_table_count
    FROM user_tables
    WHERE table_name = UPPER(p_tablename);

    IF l_table_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Ungültiger Tabellenname.');
    END IF;

    -- Validierung: Spalte muss existieren
    SELECT COUNT(*)
    INTO l_column_count
    FROM user_tab_columns
    WHERE table_name = UPPER(p_tablename)
      AND column_name = UPPER(p_id_column);

    IF l_column_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Ungültiger Spaltenname.');
    END IF;

    -- Dynamisches SQL für das Update-Statement
    sql_statement := 'UPDATE ' || p_tablename ||
                     ' SET created_on = :1 ' ||
                     ' WHERE ' || p_id_column || ' = :2';

    -- Ausführung des dynamischen SQL
    EXECUTE IMMEDIATE sql_statement USING SYSDATE, p_id;

    -- Änderungen speichern
    COMMIT;
END;
/
;

The calling of the procedure (set_created_on(p_tablename => 'SDV', p_id_column => 'ID', p_id => :MD_ID);) never worked, because I always got an ORA-06550: wrong number of args / arg types error which I really couldn't solve.

Can anyone address and comment or even solve the 2 questions?