-
I am trying to use this script located in $ORACLE_HOME/rdbms/admin but it gives errors
It compiles fine but when execute it says no data found
create or replace procedure deptree_fill (type char, schema char, name char) is
obj_id number;
begin
delete from deptree_temptab;
commit;
select object_id into obj_id from all_objects
where owner = upper(deptree_fill.schema)
and object_name = upper(deptree_fill.name)
and object_type = upper(deptree_fill.type);
insert into deptree_temptab
values(obj_id, 0, 0, 0);
insert into deptree_temptab
select object_id, referenced_object_id,
level, deptree_seq.nextval
from public_dependency
connect by prior object_id = referenced_object_id
start with referenced_object_id = deptree_fill.obj_id;
exception
when no_data_found then
raise_application_error(-20000, 'ORU-10013: ' ||
type || ' ' || schema || '.' || name || ' was not found.');
end;
/
I tried to debug it bu replacing
deptree_fill.schema
deptree_fill.name
deptree_fill.type
to
scott
dept
table
and it works by retruning the correct object_id but with those variables it does not
I am pretty sure I have passed the correct parameters to the procedure when executing it but no luck
anyone know why?
-
Works for me as is :)
-amar
-
you just do
exec ('table', 'scott','dept') right?
it always says data no found to me dunno why but if instead of variables I put literals it works....
-
yes, I just did the same as you!
-amar