Tabular Forms - Expert Oracle Application Express - page 169

DELETE FROM emp
WHERE empno = apex application.g f02 (i);
-- g f02 is the hidden column containing
-- the primary key of the EMP table (empno)
END LOOP;
END;
Listing 3-3 shows how the correct code should look. Because several procedures are now used, they
have been bundled together into a package. You can then simply call this package in your processes or
validations.
The package in Listing 3-3 implements a couple of small procedures in order to make the overall
processing easier. The goal is to avoid overloading the page and application with loose PL/SQL blocks.
Listing 3-3. Deleting Checked Rows—Procedure
CREATE OR REPLACE PACKAGE tab form emp pkg
AS
PROCEDURE disable foreign constraints;
PROCEDURE enable foreign constraints;
PROCEDURE restore tables;
PROCEDURE delete emp row (p message OUT VARCHAR2);
END tab form emp pkg;
/
CREATE OR REPLACE PACKAGE BODY tab form emp pkg
AS
PROCEDURE disable foreign constraints
IS
BEGIN
FOR c IN (SELECT constraint name, table name
FROM user constraints
WHERE table name IN ('EMP', 'DEPT')
AND constraint type = 'R'
ORDER BY table name)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '
|| c.table name
|| ' DISABLE CONSTRAINT '
|| c.constraint name;
END LOOP;
END disable foreign constraints;
PROCEDURE enable foreign constraints
IS
BEGIN
FOR c IN (SELECT constraint name, table name
FROM user constraints
WHERE table name IN ('EMP', 'DEPT')
AND constraint type = 'R'
ORDER BY table name)