CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
mlwacosmos
mlwacosmos

Reputation: 4561

How to list user_types used in a plsql script

Do you know if it exists a way to show all the user types used in a plsql script (maybe using sqldeveloper)

Ty

Upvotes: 0

Views: 58

Answers (1)

Alex Poole
Alex Poole

Reputation: 191295

You can query the user_dependencies view (or all_dependencies or dba_dependencies if you can see it, and want to cast your net wider) to see which objects depend on each other.

To see all packages, procedures, functions and triggers (i.e. stored PL/SQL) that refers to user-defined types you can do something like:

select type, name, referenced_type, referenced_name
from user_dependencies
where type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER')
and referenced_owner = user
and referenced_type = 'TYPE';

If you're interested in a particular 'script' (which has to be stored PL/SQL, not an anonymous block) then you can filter for that:

select referenced_name
from user_dependencies
where type = 'PROCEDURE' -- or whatever the object type actually is
and name = 'SOME_PROCEDURE' -- your actual object name
and referenced_owner = user
and referenced_type = 'TYPE';

In SQL Developer you can also find the procedure by drilling down into the connection in the left-hand panel and clicking on the procedure (or package, or whatever) name, which will open a new tab that shows the procedure code by default. Withing that tab there is a smaller set of tabs. Click 'Dependencies' and you'll see similar information to what that query gives you. If you're looking for dependencies from a package you may need to look at the body's dependencies as well as the specification's.

Upvotes: 2

Related Questions