utldtree.sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: utldtree.sql

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  2. #2
    Join Date
    Mar 2001
    Posts
    314
    Works for me as is

    -amar

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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....

  4. #4
    Join Date
    Mar 2001
    Posts
    314
    yes, I just did the same as you!

    -amar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width