A work-around for the Illegal restricted procedure error message

Post built is restricted with post-change:
Use the below procedure (PR_POST_TIMER) instead of post to work-around the Illegal restricted procedure error message
POST-CHANGE:
BEGIN
PR_POST_TIMER
END;

PROCEDURE PR_POST_TIMER IS
T_POST TIMER;
BEGIN
T_POST := CREATE_TIMER('T_POST',1);
END;

In the WHEN-TIMER-EXPIRED trigger write the following:
DECLARE T_TIMER TIMER;
BEGIN
T_TIMER := FIND_TIMER('T_POST');
IF NOT ID_NULL(T_TIMER) THEN
/* stop timer firing again */
delete_timer('T_POST');
POST;
END IF;
END;

NB: The WHEN-TIMER-EXPIRED trigger should be at the FORM level

You can use the same work-around to call most of the illegal restricted procedure error message,for example you can now call go_block in WHEN-VALIDATE-ITEM

Happy coding

How to get the root parent in a self relationship

This is a generic function usefull for retriving the parent in a self relationship:

CREATE OR REPLACE FUNCTION GET_ROOT (P_TABLE_NAME VARCHAR2,P_CHILD_COLUMN VARCHAR2,P_PARENT_COLUMN VARCHAR2,P_ID NUMBER) RETURN NUMBER IS
V_P_ID NUMBER := 0;
V_ID NUMBER := 0;
V_PARENT_ID NUMBER;
V_SQL VARCHAR2(3000);
BEGIN
BEGIN
V_SQL := 'SELECT 'P_PARENT_COLUMN
' FROM 'P_TABLE_NAME
' WHERE 'P_CHILD_COLUMN' = 'P_ID;
EXECUTE IMMEDIATE V_SQL
INTO V_P_ID;
V_SQL := NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_P_ID := -1;
WHEN OTHERS THEN
V_P_ID := -1;
END;
IF V_P_ID <> - 1 THEN
WHILE V_P_ID IS NOT NULL LOOP
BEGIN
V_SQL := 'SELECT 'P_CHILD_COLUMN','P_PARENT_COLUMN
' FROM 'P_TABLE_NAME
' WHERE 'P_CHILD_COLUMN' = ' V_P_ID;
EXECUTE IMMEDIATE V_SQL
INTO V_ID,V_P_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
END LOOP;
V_PARENT_ID := V_ID;
ELSE
V_PARENT_ID := P_ID;
END IF;
IF P_ID = V_PARENT_ID THEN
RETURN NULL;
ELSE
RETURN (V_PARENT_ID);
END IF;
END;
/


The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.

Let's try this function SCOTT.EMP Table:

SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/80 800 20
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30
7521 WARD SALESMAN 7698 22/02/81 1250 500 30
7566 JONES MANAGER 7839 02/04/81 2975 20
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/81 2850 30
7782 CLARK MANAGER 7839 09/06/81 2450 10
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7839 KING PRESIDENT 17/11/81 5000 10
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30
7876 ADAMS CLERK 7788 23/05/87 1100 20
7900 JAMES CLERK 7698 03/12/81 950 30
7902 FORD ANALYST 7566 03/12/81 3000 20
7934 MILLER CLERK 7782 23/01/82 1300 10

14 rows selected.

Now let's try to get the root parent of the EMPNO = 7369

select GET_ROOT ('EMP' ,'EMPNO','MGR',7369) AS GET_ROOT_PARENT
from Dual;

GET_ROOT_PARENT
---------------
7839

select GET_ROOT ('EMP' ,'EMPNO','MGR',7839) AS GET_ROOT_PARENT
from Dual;

GET_ROOT_PARENT
---------------

As you can see the MGR of EMPNO = 7839 was NULL in the SELECT * from EMP.

Performing Top-N Queries in Oracle Using the ROWNUM Pseudocolumn

There is a useful function in SQL Server called TOP N With Ties. It is like TOP N (which is like the Oracle ROWNUM function) but if you are ordering by a non unique column, it retrieves all common rows associated with the last row in the set.
The code below will achieve this functionality,this function return a date if you want to use it with other type for example,just change the return of the function from date to number:

CREATE OR REPLACE FUNCTION TOP_DATE(P_TABLE_NAME VARCHAR2,P_COLUMN_NAME VARCHAR2,P_RANGE NUMBER ,P_DISTINCT VARCHAR2 DEFAULT 'DISTINCT',P_ORDER_BY VARCHAR2 DEFAULT 'DESC',P_WHERE VARCHAR2 DEFAULT NULL) RETURN DATE IS
V_DATE DATE;
V_DISTINCT VARCHAR2(10);
V_SQL VARCHAR2(3000);
V_WHERE VARCHAR2(1000);
BEGIN
IF P_RANGE <= 0 THEN V_DATE := NULL; DBMS_OUTPUT.PUT_LINE('Range must be greater than 0'); ELSIF NVL(LTRIM(RTRIM(UPPER(P_DISTINCT))),'DISTINCT') <> 'DISTINCT' THEN
V_DATE := NULL;
DBMS_OUTPUT.PUT_LINE('P_DISTINCT must be null or = DISTINCT');
ELSIF LTRIM(RTRIM(UPPER(P_ORDER_BY))) NOT IN ('ASC','DESC') THEN
V_DATE := NULL;
DBMS_OUTPUT.PUT_LINE('P_ORDER_BY must be ASC or DESC');
ELSE
BEGIN
IF P_DISTINCT IS NULL OR P_DISTINCT = '' OR P_DISTINCT = ' ' THEN
V_DISTINCT := ' ';
ELSE
V_DISTINCT := 'DISTINCT';
END IF;
IF P_WHERE IS NULL THEN
V_WHERE := ' ';
ELSE
V_WHERE := P_WHERE;
END IF;
V_SQL := 'select 'P_COLUMN_NAME' from (select rownum range,'P_COLUMN_NAME
' from (select 'V_DISTINCT' 'P_COLUMN_NAME' from 'P_TABLE_NAME' 'V_WHERE
' order by 'P_COLUMN_NAME' 'P_ORDER_BY'))'
' where range = 'P_RANGE;

EXECUTE IMMEDIATE V_SQL
INTO V_DATE;

EXCEPTION WHEN OTHERS THEN V_DATE := NULL;
END;
END IF;
RETURN V_DATE;
END;
/