funciton/procedure calling each other

using packages:

define function in packages

create or replace PACKAGE FUNCTION_PAK AS FUNCTION GET_F_S_C( p_tname in varchar2,-- parent table name p_cname in varchar2,-- parent foreign key column name tname in varchar2, -- referenced table name cname in varchar2, -- target column name t_number in number, -- table index condition in varchar2, -- extra condition cursor_out default_pak.cursor_type) -- cursor point to result set to store data RETURN default_pak.cursor_type ; END FUNCTION_PAK;

create package body

in body :

write functin trunk

create or replace PACKAGE BODY FUNCTION_PAK AS FUNCTION GET_F_S_C( p_tname in varchar2,-- parent table name p_cname in varchar2,-- parent foreign key column name tname in varchar2, -- referenced table name cname in varchar2, -- target column name t_number in number, -- table index condition in varchar2, -- extra condition cursor_out default_pak.cursor_type) -- cursor point to result set to store data RETURN default_pak.cursor_type is cursor_to_return default_pak.cursor_type; cursor_temp default_pak.cursor_type; target_cursor default_pak.cursor_type; column_names VARCHAR2(4000); table_names VARCHAR2(4000); conditions VARCHAR2(4000); table_alias VARCHAR2(4000); p_table_alias VARCHAR2(4000); table_pk VARCHAR2(4000); target_table VARCHAR2(4000); --target_column_name VARCHAR2(4000); EXTRA_CONDITION VARCHAR2(4000); PK_COUNT NUMBER; target_list varchar2(4000); target_single varchar2(4000); i number; j number; BEGIN -- initial data -- get previous stored data LOOP FETCH cursor_out INTO column_names,table_names,conditions; EXIT WHEN cursor_out%NOTFOUND; END LOOP; CLOSE cursor_out; CONDITIONS:= RTRIM(CONDITIONS,' and '); -- COUNT PRIME select COUNT(ucc.column_name) into PK_COUNT from user_cons_columns ucc,user_constraints uc where uc.table_name = tname and uc.constraint_name = ucc.constraint_name and uc.constraint_type='P'; --table_pk:=GET_COLUMN_COMMENT_VALUE(P_TNAME,P_CNAME,'KEY'); SELECT GET_COLUMN_COMMENT_VALUE(P_TNAME,P_CNAME,'KEY') INTO TABLE_PK FROM DUAL; IF PK_COUNT=1 AND table_pk IS NULL -- SINGLE PRIME THEN -- get current table's pk select max(ucc.column_name) into table_pk from user_cons_columns ucc,user_constraints uc where uc.table_name = tname and uc.constraint_name = ucc.constraint_name and uc.constraint_type='P'; --ELSE -- MUTIPLE PRIME -- table_pk:=GET_COLUMN_COMMENT_VALUE(P_TNAME,P_CNAME,'KEY'); END IF; -- modified table_names table_alias := 't'||t_number; if (t_number =1) or (t_number>1) then table_names :=table_names||tname||' '||table_alias||','; end if; -- modified conditions if t_number>10 then if t_number>100 then p_table_alias := 't'||(floor(t_number/100)*10+1); else p_table_alias := 't'||(floor(t_number/100)+1); end if; CONDITIONS:= RTRIM(CONDITIONS,' and '); conditions := conditions||' and '||p_table_alias||'.'||p_cname||'='||table_alias||'.'||table_pk||'(+) and '; else conditions :=''; end if; -- IS TARTGET COLUMN FOREIGN if column_is_foreign(tname,cname)=0 then -- SET COLUMN NAME TO ADD column_names := column_names||table_alias||'.'||cname||','; --column_names := column_names||table_alias||'.'||cname||' "'||tname||'_'||cname||'",'; -- GET EXTRA CONDITION SELECT get_condition(tname,cname,condition) INTO EXTRA_CONDITION FROM DUAL; -- ADD EXTRA CONDITION TO CONDITION LIST IF EXTRA_CONDITION IS NOT NULL THEN EXTRA_CONDITION:= table_alias||'.'||EXTRA_CONDITION; if conditions is null then conditions:=' and '||EXTRA_CONDITION; else CONDITIONS:= RTRIM(CONDITIONS,' and '); conditions:=conditions||' and '||EXTRA_CONDITION; end if; ELSE CONDITIONS:= RTRIM(CONDITIONS,' and '); END IF; -- EXIT POINT -- RETURN RESULT open cursor_to_return for select column_names,table_names,conditions from dual; return cursor_to_return; else -- GET TARGET COLUMN target_list :=get_column_comment_value(tname,cname,'TARGET'); target_cursor := get_split_str_cursor(target_list,'|'); TARGET_TABLE := GET_REFERENCE_TABLE(TNAME,CNAME); open cursor_to_return for select column_names,table_names,conditions from dual; i :=0; LOOP FETCH target_cursor INTO target_single; EXIT WHEN target_cursor%NOTFOUND; i:=i+1; IF (i=1) THEN cursor_to_return:=get_f_s_c(tname,cname,target_table,target_single,t_number*10+1,condition,cursor_to_return); ELSE cursor_to_return:=get_f_s_c_m(tname,cname,target_table,target_single,t_number*10+i,condition,cursor_to_return); END IF; END LOOP; CLOSE target_cursor; return cursor_to_return; end if; END GET_F_S_C; END FUNCTION_PAK;