--Stored procedure for generating DML Script in apex
CREATE OR REPLACE PROCEDURE GEN_APEX_DML_SCRIPT (
p_table_name IN VARCHAR2,
p_page_item_pk IN VARCHAR2 DEFAULT NULL, -- e.g. 'P17_ID'
P_PAGE IN VARCHAR2 -- e.g. 'P17_'
)
IS
v_table_name VARCHAR2(100);
v_columns VARCHAR2(4000);
v_bind_columns VARCHAR2(4000);
v_update_columns VARCHAR2(4000);
v_pk_column VARCHAR2(100);
v_pk_page_item VARCHAR2(100);
BEGIN
v_table_name := UPPER(p_table_name);
-- Try to detect primary key automatically
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;
-- If user passed a page item pk name, use it, else build default
IF p_page_item_pk IS NOT NULL THEN
v_pk_page_item := p_page_item_pk;
ELSE
v_pk_page_item := 'Pxx_' || v_pk_column;
END IF;
-- Build column lists
SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),
LISTAGG(':' ||P_PAGE|| column_name, ', ') WITHIN GROUP (ORDER BY column_id),
LISTAGG(column_name || ' = :' ||P_PAGE|| column_name, ', ')
INTO v_columns, v_bind_columns, v_update_columns
FROM user_tab_cols
WHERE table_name = v_table_name
ORDER BY COLUMN_ID;
-- Output APEX DML script
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Generated DML for table: ' || v_table_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE(' IF :' || v_pk_page_item || ' IS NULL 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(' ELSE');
DBMS_OUTPUT.PUT_LINE(' UPDATE ' || v_table_name);
DBMS_OUTPUT.PUT_LINE(' SET ' || v_update_columns);
IF v_pk_column IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(' WHERE ' || v_pk_column || ' = :' || v_pk_page_item || ';');
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_DML_SCRIPT;
/
SET SERVEROUTPUT ON;
BEGIN
GEN_APEX_DML_SCRIPT(:TABLE_NAME, :PAGE_ITEM_PK, 'P17_');
END;
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 ' || ':' || :PAGE_NO_PK || ' IS NULL
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 (':' || :PAGE_NO_PK || Y.COLUMN_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE (');');
END LOOP;
DBMS_OUTPUT.PUT_LINE (
'
ELSIF ' || ':' || :PAGE_NO_PK || ' IS NOT NULL
THEN
UPDATE ' || :P_TABLE_NAME || ' SET ');
FOR Z IN C5
LOOP
DBMS_OUTPUT.PUT_LINE (
Z.COLUMN_NAME
|| ' = :'
|| :PAGE_NO_PK
|| Z.COLUMN_NAME
|| ',');
END LOOP;
DBMS_OUTPUT.PUT_LINE (
'WHERE ' || :PAGE_NO_PK || ' = ' || ':' || :PAGE_NO_PK || ';');
DBMS_OUTPUT.PUT_LINE ('END IF;
END;');
END LOOP;
END;
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন