DECLARE
	CURSOR cur_package IS
-- -- 	SELECT package, UPPER(dependency) dependency, pack_ref FROM TEST_TAB;
	SELECT package, UPPER(dependency) dependency, pack_ref
	FROM
	(
	SELECT 1 AS package, 'BEFORE' AS dependency, 5 AS pack_ref FROM DUAL
	UNION
	SELECT 5 AS package, 'AFTER' AS dependency, 1 AS pack_ref FROM DUAL
	UNION
	SELECT 1 AS package, 'BEFORE' AS dependency, 3 AS pack_ref FROM DUAL
	UNION
	SELECT 3 AS package, 'AFTER' AS dependency, 1 AS pack_ref FROM DUAL
	UNION
	SELECT 3 AS package, 'BEFORE' AS dependency, 6 AS pack_ref FROM DUAL
	UNION
	SELECT 7 AS package, 'AFTER' AS dependency, 3 AS pack_ref  FROM DUAL
	UNION
	SELECT 6 AS package, 'AFTER' AS dependency, 3 AS pack_ref  FROM DUAL
	);
	
	TYPE tab_package IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;

	tab_a tab_package;
	tab_b tab_package;
		
	package_found 	BOOLEAN := FALSE;
	pack_ref_found 	BOOLEAN := FALSE;

	package_pos		PLS_INTEGER := 0;
	pack_ref_pos	PLS_INTEGER := 0;
	j				PLS_INTEGER := 0;

	pack_excp		EXCEPTION;
			
BEGIN

	FOR rec_package IN cur_package 
	LOOP
		
	dbms_output.put_line('values : ' || rec_package.package || ' - ' || rec_package.dependency || ' - ' || rec_package.pack_ref);
	/* IF the array list is NULL then insert the package and package reference at suitable position */
	IF tab_a.FIRST IS NULL THEN
	
			j := 1;

			IF rec_package.dependency = 'BEFORE' THEN
				tab_a(j) 	:= rec_package.package;
				tab_a(j+1) 	:= rec_package.pack_ref;
			ELSIF rec_package.dependency = 'AFTER' THEN
				tab_a(j) 	:= rec_package.pack_ref;
				tab_a(j+1) 	:= rec_package.package;
			END IF;
			
  			dbms_output.put_line('NULL ' || tab_a(j) || '  ' || tab_a(j+1));

	ELSE
-- -- 			dbms_output.put_line('First :' || tab_a.FIRST);
-- -- 			dbms_output.put_line('Last  :' || tab_a.LAST);
	
		/* Check for package value in table */
		package_found := FALSE;
		package_pos   := 0;
		FOR	i IN tab_a.FIRST..tab_a.COUNT	 
		LOOP

			IF rec_package.package = tab_a(i) THEN
				package_found := TRUE;
				package_pos   := i;
				EXIT;
			END IF;	
		
		END LOOP;
		IF package_found THEN
	    dbms_output.put_line('found values package - ' || package_pos);
	    END IF;
	    
		/* Check for package reference value in table */
		pack_ref_found := FALSE;
		pack_ref_pos   := 0;
		FOR	i IN tab_a.FIRST..tab_a.COUNT 
		LOOP
			IF rec_package.pack_ref = tab_a(i) THEN
				pack_ref_found := TRUE;
				pack_ref_pos   := i;
				EXIT;
			END IF;	
		
		END LOOP;
		IF pack_ref_found THEN
	    dbms_output.put_line('found values pack ref  - ' || pack_ref_pos);
		END IF;
		
		/* If both are found then validate for cyclic loops and inconsistent depth */
		IF (package_found = TRUE AND pack_ref_found = TRUE) THEN
			DBMS_OUTPUT.PUT_LINE('BOTH FOUND ');
			
			IF (rec_package.dependency = 'BEFORE' AND package_pos > pack_ref_pos) THEN
				RAISE pack_excp;
			ELSIF (rec_package.dependency = 'AFTER' AND package_pos < pack_ref_pos) THEN
				RAISE pack_excp;
			END IF;				
			
		/* If both are NOT found then add in the array list */
		ELSIF (package_found = FALSE AND pack_ref_found = FALSE) THEN
			DBMS_OUTPUT.PUT_LINE('BOTH False ' || tab_a.COUNT); 
			j := tab_a.COUNT + 1;
			DBMS_OUTPUT.PUT_LINE('J ' || j); 

			IF rec_package.dependency = 'BEFORE' THEN
				tab_a(j) 	:= rec_package.package;
				tab_a(j+1) 	:= rec_package.pack_ref;
			ELSIF rec_package.dependency = 'AFTER' THEN
				tab_a(j) 	:= rec_package.pack_ref;
				tab_a(j+1) 	:= rec_package.package;
			END IF;
				
		/* IF package is FOUND and package reference NOT FOUND then only ADD package reference at suitable position */			
		ELSIF (package_found = TRUE AND pack_ref_found = FALSE) THEN
-- -- 		DBMS_OUTPUT.PUT_LINE('Got into TF');
			j := 1;
			
			IF rec_package.dependency = 'BEFORE' THEN
				
				FOR	i IN tab_a.FIRST..package_pos
				LOOP
					tab_b(j) := tab_a(i);
					j := j + 1;
				END LOOP;
				
				DBMS_OUTPUT.PUT_LINE('J TF ' || j); 		
				tab_b(j) := rec_package.pack_ref;
				
				FOR	i IN package_pos+1..tab_a.COUNT
				LOOP
					j := j + 1;
					tab_b(j) := tab_a(i);
				END LOOP;
				
			ELSIF rec_package.dependency = 'AFTER' THEN
			
				FOR	i IN tab_a.FIRST..package_pos - 1
				LOOP
					tab_b(j) := tab_a(i);
					j := j + 1;
				END LOOP;
						
				tab_b(j) := rec_package.pack_ref;
				
				FOR	i IN package_pos..tab_a.COUNT
				LOOP
					j := j + 1;
					tab_b(j) := tab_a(i);
				END LOOP;
				
			END IF;
			
			tab_a := tab_b;
			tab_b.delete;
			
		/* IF package is NOT FOUND and package reference FOUND then only ADD package at suitable position */			
		ELSIF (package_found = FALSE AND pack_ref_found = TRUE) THEN

			j := 1;
			IF rec_package.dependency = 'BEFORE' THEN

				FOR	i IN tab_a.FIRST..pack_ref_pos - 1
				LOOP
					tab_b(j) := tab_a(i);
					j := j + 1;
				END LOOP;
						
				tab_b(j) := rec_package.package;
				j := j + 1;
				
				FOR	i IN pack_ref_pos..tab_a.COUNT
				LOOP
					tab_b(j) := tab_a(i);
					j := j + 1;
				END LOOP;
				
			ELSIF rec_package.dependency = 'AFTER' THEN
				
				FOR	i IN tab_a.FIRST..pack_ref_pos
				LOOP
					tab_b(j) := tab_a(i);
					j := j + 1;
				END LOOP;
						
				tab_b(j) := rec_package.package;
				j := j + 1;
				
				FOR	i IN pack_ref_pos +1 ..tab_a.COUNT
				LOOP
					tab_b(j) := tab_a(i);
					j := j + 1;
				END LOOP;
				
			END IF;
		
			tab_a := tab_b;
			tab_b.delete;
		
		
		END IF;	
			
	END IF;
			
	END LOOP;

	dbms_output.put_line('XFirst :' || tab_a.FIRST);
	dbms_output.put_line('XLast :'  || tab_a.COUNT);
	FOR i IN 1..tab_a.COUNT 
	LOOP
	dbms_output.put_line('VALUE :' || tab_a(i));
	END LOOP;

EXCEPTION
	WHEN pack_excp THEN
		DBMS_OUTPUT.PUT_LINE('Error in depth :');
-- -- 		po_sql_errm := 'Error in cyclic depth';
-- -- 		po_sql_errcode := '-1'; 
	WHEN OTHERS THEN	
		DBMS_OUTPUT.PUT_LINE('Other Error :' || SQLERRM);
-- -- 		po_sql_errm := SQLERRM;
-- -- 		po_sql_errcode := SQLCODE; 
END;
/