Insert script in apex detail data block

--Storeed Procedure for generating  APEX_GRID_DML 

CREATE OR REPLACE PROCEDURE GEN_APEX_GRID_DML (p_table_name IN VARCHAR2)

IS

    v_table_name     VARCHAR2(100);

    v_columns        VARCHAR2(4000);

    v_bind_columns   VARCHAR2(4000);

    v_update_cols    VARCHAR2(4000);

    v_pk_column      VARCHAR2(100);

BEGIN

    v_table_name := UPPER(p_table_name);

    -- Detect Primary Key Column

    BEGIN

        SELECT acc.column_name

          INTO v_pk_column

          FROM user_constraints uc

               JOIN user_cons_columns acc

                  ON uc.constraint_name = acc.constraint_name

         WHERE uc.table_name = v_table_name

           AND uc.constraint_type = 'P'

           AND ROWNUM = 1;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            v_pk_column := NULL;

    END;

    -- Build column lists

    SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),

           LISTAGG(':' || column_name, ', ') WITHIN GROUP (ORDER BY column_id),

           LISTAGG(column_name || ' = :' || column_name, ', ')

      INTO v_columns, v_bind_columns, v_update_cols

      FROM user_tab_cols

     WHERE table_name = v_table_name;

    -- Print generated block

    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE('-- APEX GRID DML BLOCK FOR TABLE: ' || v_table_name);

    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE('BEGIN');

    DBMS_OUTPUT.PUT_LINE('   IF :APEX$ROW_STATUS = ''C'' THEN');

    DBMS_OUTPUT.PUT_LINE('      INSERT INTO ' || v_table_name || ' (' || v_columns || ')');

    DBMS_OUTPUT.PUT_LINE('      VALUES (' || v_bind_columns || ');');

    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE('   ELSIF :APEX$ROW_STATUS = ''U'' THEN');

    DBMS_OUTPUT.PUT_LINE('      UPDATE ' || v_table_name);

    DBMS_OUTPUT.PUT_LINE('         SET ' || v_update_cols);

    IF v_pk_column IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE('       WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

    ELSE

        DBMS_OUTPUT.PUT_LINE('       -- No primary key found. Add WHERE clause manually.');

    END IF;

    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE('   ELSIF :APEX$ROW_STATUS = ''D'' THEN');

    DBMS_OUTPUT.PUT_LINE('      DELETE FROM ' || v_table_name);

    IF v_pk_column IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE('       WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

    ELSE

        DBMS_OUTPUT.PUT_LINE('       -- No primary key found. Add WHERE clause manually.');

    END IF;

    DBMS_OUTPUT.PUT_LINE('   END IF;');

    DBMS_OUTPUT.PUT_LINE('END;');

    DBMS_OUTPUT.PUT_LINE('/');

    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END GEN_APEX_GRID_DML;

/

SET SERVEROUTPUT ON;

BEGIN

   GEN_APEX_GRID_DML(:TABLE_NAME);

END;

/

--Latest version

DECLARE

    v_table_name     VARCHAR2(100) := UPPER(:P_TABLE_NAME);

    v_columns        VARCHAR2(4000);

    v_bind_columns   VARCHAR2(4000);

    v_update_cols    VARCHAR2(4000);

    v_pk_column      VARCHAR2(100);

BEGIN

    -- Detect Primary Key Column

    BEGIN

        SELECT acc.column_name

          INTO v_pk_column

          FROM user_constraints uc

               JOIN user_cons_columns acc

                  ON uc.constraint_name = acc.constraint_name

         WHERE uc.table_name = v_table_name

           AND uc.constraint_type = 'P'

           AND ROWNUM = 1;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            v_pk_column := NULL;

    END;

    -- Build column lists for insert/update

    SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),

           LISTAGG(':' || column_name, ', ') WITHIN GROUP (ORDER BY column_id),

           LISTAGG(column_name || ' = :' || column_name, ', ')

      INTO v_columns, v_bind_columns, v_update_cols

      FROM user_tab_cols

     WHERE table_name = v_table_name;

    -- Print output block

    DBMS_OUTPUT.PUT_LINE('BEGIN');

    DBMS_OUTPUT.PUT_LINE('   IF :APEX$ROW_STATUS = ''C'' THEN');

    DBMS_OUTPUT.PUT_LINE('      INSERT INTO ' || v_table_name || ' (' || v_columns || ')');

    DBMS_OUTPUT.PUT_LINE('      VALUES (' || v_bind_columns || ');');

    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE('   ELSIF :APEX$ROW_STATUS = ''U'' THEN');

    DBMS_OUTPUT.PUT_LINE('      UPDATE ' || v_table_name);

    DBMS_OUTPUT.PUT_LINE('         SET ' || v_update_cols);

    IF v_pk_column IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE('       WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

    ELSE

        DBMS_OUTPUT.PUT_LINE('       -- ⚠️ No primary key found. Please add WHERE clause manually.');

    END IF;

    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE('   ELSIF :APEX$ROW_STATUS = ''D'' THEN');

    DBMS_OUTPUT.PUT_LINE('      DELETE FROM ' || v_table_name);

    IF v_pk_column IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE('       WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

    ELSE

        DBMS_OUTPUT.PUT_LINE('       -- ⚠️ No primary key found. Please add WHERE clause manually.');

    END IF;

    DBMS_OUTPUT.PUT_LINE('   END IF;');

    DBMS_OUTPUT.PUT_LINE('END;');

END;

/

 

-- Old Code

DECLARE
   CURSOR c1
   IS
      SELECT :P_TABLE_NAME P_TABLE FROM DUAL;
   CURSOR C2
   IS
      SELECT COLUMN_NAME || ',' COLUMN_NAME
        FROM USER_TAB_COLS
       WHERE TABLE_NAME = :P_TABLE_NAME

        ORDER BY COLUMN_ID;
   CURSOR c3
   IS
      SELECT 'INSERT INTO (' || :P_TABLE_NAME P_TABLE FROM DUAL;
   CURSOR C4
   IS
      SELECT COLUMN_NAME || ',' COLUMN_NAME
        FROM USER_TAB_COLS
       WHERE TABLE_NAME = :P_TABLE_NAME

        ORDER BY COLUMN_ID;
   CURSOR C5
   IS
      SELECT COLUMN_NAME
        FROM USER_TAB_COLS
       WHERE TABLE_NAME = :P_TABLE_NAME

        ORDER BY COLUMN_ID;
BEGIN
   FOR I IN C1
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         'BEGIN
      IF :APEX$ROW_STATUS = ''C''
   THEN
    INSERT INTO ' || I.P_TABLE || '(');
      FOR X IN C2
      LOOP
         DBMS_OUTPUT.PUT_LINE (X.COLUMN_NAME);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE (')');
      FOR J IN C3
      LOOP
         DBMS_OUTPUT.PUT_LINE ('VALUES(');
         FOR Y IN C4
         LOOP
            DBMS_OUTPUT.PUT_LINE (':' || Y.COLUMN_NAME);
         END LOOP;
         DBMS_OUTPUT.PUT_LINE (');');
      END LOOP;
      DBMS_OUTPUT.PUT_LINE (
         ' 
   ELSIF :APEX$ROW_STATUS = ''U''
   THEN
      UPDATE ' || :P_TABLE_NAME || ' SET ');
      FOR Z IN C5
      LOOP
         DBMS_OUTPUT.PUT_LINE (
            Z.COLUMN_NAME || ' = :' || Z.COLUMN_NAME || ',');
      END LOOP;
      DBMS_OUTPUT.PUT_LINE ('WHERE COLUMN_NAME = :COLUMN_NAME;');
      DBMS_OUTPUT.PUT_LINE (
            'ELSIF :APEX$ROW_STATUS = ''D''
   THEN
      DELETE FROM '
         || :P_TABLE_NAME
         || '
            WHERE COLUMN_NAME = :COLUMN_NAME;
            END IF;
            END;');
   END LOOP;
END;