create or replace view catproc1 (arg_name,obj_num,proc_name,arg_pos,arg_seq,arg_type,
arg_inout,arg_len)
as
select sys.argument$.ARGUMENT,sys.argument$.OBJ#,sys.argument$.procedure$,
	sys.argument$.POSITION#,sys.argument$.SEQUENCE#,sys.argument$.TYPE#,
	sys.argument$.IN_OUT,sys.argument$.LENGTH
from sys.argument$ where sys.argument$.LEVEL# = 0;



create or replace view catproc2 (arg_name,obj_num,proc_name,arg_pos,arg_seq,
		arg_type,arg_len) as
select sys.argument$.ARGUMENT,sys.argument$.OBJ#,sys.argument$.procedure$,
	sys.argument$.POSITION#,sys.argument$.SEQUENCE#,sys.argument$.TYPE#,
	sys.argument$.LENGTH from
sys.argument$ where sys.argument$.LEVEL# = 1;



create or replace view catproca(arg_name,proc_name,obj_num,arg_pos,arg_typea,
		arg_typeb,arg_lena,arg_lenb,arg_inout) as
select distinct catproc1.arg_name,catproc1.proc_name, catproc1.obj_num, 
		catproc1.arg_pos, catproc1.arg_type, catproc2.arg_type,
		catproc1.arg_len,catproc2.arg_len,catproc1.arg_inout
from catproc1,catproc2 
where
catproc1.obj_num=catproc2.obj_num and catproc1.proc_name=catproc2.proc_name;

create or replace view catprocb(arg_name,proc_name,obj_num,arg_pos,arg_typea,
			arg_typeb,arg_lena,arg_lenb,arg_inout) as
select distinct catproc1.arg_name,catproc1.proc_name, catproc1.obj_num, 
		catproc1.arg_pos, catproc1.arg_type, catproc2.arg_type,
		catproc1.arg_len,catproc2.arg_len,catproc1.arg_inout
from catproc1,catproc2 
where
catproc1.obj_num=catproc2.obj_num and catproc1.proc_name=catproc2.proc_name and
		(catproc2.arg_seq - catproc1.arg_seq) = 1;

create or replace view catprocc (proc_name, obj_num, arg_name, arg_pos, arg_typea, 
		arg_typeb, arg_lena, arg_lenb, arg_inout) as
select 	proc_name, obj_num, arg_name, arg_pos, arg_typea, arg_typeb, 
		arg_lena, arg_lenb, decode(ARG_INOUT,NULL,0,ARG_INOUT)
from catproca where arg_typea <> 251
	union
select	proc_name, obj_num, arg_name, arg_pos, arg_typea, 
	arg_typeb, arg_lena, arg_lenb, decode(ARG_INOUT,NULL,0,ARG_INOUT) 
		 from catprocb ;


create or replace view catora (obj_num,owner,pack_name,type) as
select object_id,owner,object_name,object_type from all_objects 
where object_type='PACKAGE' or object_type='PROCEDURE';


create or replace view catpack(proc_name,obj_num) as
select distinct sys.argument$.procedure$,sys.argument$.OBJ# from sys.argument$;

drop PUBLIC SYNONYM CATPROC1;
drop PUBLIC SYNONYM CATPROC2;
drop PUBLIC SYNONYM CATPROCA;
drop PUBLIC SYNONYM CATPROCB;
drop PUBLIC SYNONYM CATPROCC;
drop PUBLIC SYNONYM CATORA;
drop PUBLIC SYNONYM CATPACK;


CREATE PUBLIC SYNONYM CATPROC1 FOR CATPROC1;
CREATE PUBLIC SYNONYM CATPROC2 FOR CATPROC2;
CREATE PUBLIC SYNONYM CATPROCA FOR CATPROCA;
CREATE PUBLIC SYNONYM CATPROCB FOR CATPROCB;
CREATE PUBLIC SYNONYM CATPROCC FOR CATPROCC;
CREATE PUBLIC SYNONYM CATORA FOR CATORA;
CREATE PUBLIC SYNONYM CATPACK FOR CATPACK;

grant select on CATPROC1 to PUBLIC with grant option;
grant select on CATPROC2 to PUBLIC with grant option;
grant select on CATPROCA to PUBLIC with grant option;
grant select on CATPROCB to PUBLIC with grant option;
grant select on CATPROCC to PUBLIC with grant option;
grant select on CATORA to PUBLIC with grant option;
grant select on CATPACK to PUBLIC with grant option;

