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.