SQL & PL/SQL

Okay so I have begun the long process of finding out what procedures and functions are in each one of my packages under a specified schema, I pass out a ref cursor from a function that list these

I also have a function that find the source code of a specified procedure within the package.

Basically what I am trying to do is build upon a tree walk hierarchy (I have one for the packages reliance on other packages).

I was hoping to pass the procedures name into a function - this would then go off and get the source code, and search it for the list of procedures and functions brought back by the first function.

However this is proving more difficult that first thought - I would then use this third procedure / function in a loop and find the reliance as i do in the package tree walk.

cur_package_objects -

SELECT package, LOWER(TRIM(sub_object)name)) AS sub_object_name, sub_object_type
FROM (SELECT CASE WHEN sub_name_a IS NOT NULL THEN sub_name_a
                  WHEN sub_name_a IS NULL AND sub_name_b IS NOT NULL THEN sub_name_b
                  WHEN sub_name_a IS NULL AND sub_name_b IS NULL AND sub_name_c IS NOT NULL THEN sub_name_c
             ,name as package
             ,sub_object_type
      FROM (SELECT DISTINCT name
                   ,CASE WHEN SUBSTR(TRIM(' ' FROM text),INSTR(TRIM(' ' FROM text), ' ',1,1)+1, INSTR(SUBSTR(TRIM(' ' FROM text),INSTR(TRIM(' ' FROM text),'(',1,1)-1) IS NOT NULL
                         THEN SUBSTR(TRIM(' ' FROM text),INSTR(TRIM(' ' FROM text), ' ',1,1)+1, INSTR(SUBSTR(TRIM(' ' FROM text),INSTR(TRIM(' ' FROM text),'(',1,1)-1) END AS sub_name_a
                   -- repeat for 'RETURN'
                   ,CASE WHEN text LIKE '%FUNCTION%' THEN 'FUNCTION'
                         WHEN text LIKE '%PROCEDURE%' THEN 'PROCEDURE' END AS sub_object_type
             FROM all_source
             WHERE (text LIKE '%FUNCTION %'
                    OR text LIKE '%PROCEDURE %')
             AND text NOT LIKE '--%'
             AND type = 'PACKAGE'
             AND OWNER = p_schema ) ) ;

(use two other cursors to get the start and end line for this)

cur_source -

SELECT text
FROM all_source
WHERE type = 'PACKAGE BODY'
AND owner = UPPER(p_schema)
AND name = UPPER(p_object)
AND line BETWEEN l_start AND l_end;

both of these functions work fine - however when begining to actually process the records is where i hit a bump

- i wanted to pass them into another function that would then only tell me what from the cur_package_objects are actually in the source code i am looking at

, then i was to loop through this list to find source and then what objects are in this source etc ; i think i may need to insert them into a table instead of using a refcursor

but i was hopeful some may be able to assist me with this