top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How can I view the dependencies of a PL/SQL block?

+1 vote
How can I view the dependencies of a PL/SQL block?
posted Nov 19, 2014 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
Best answer

Basically there are two types of dependencies a pl/sql block may have. Dependency on another pl/sql block called as procedural dependency. Dependency on a database object called as object dependency. For viewing the procedural dependencies we have the following group of views.


DBA_DEPENDENCIES: Shows both the dependencies. This views primarily has these following columns OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_LINK_NAME

Another view can be used to find the object dependencies called as DEPTREE and IDEPTREE for indirect dependencies.

answer Nov 24, 2014 by Arun Gowda
Similar Questions
0 votes

Question: Write a PL/SQL block which will populate the RESULTS table as described below. Consider performance implications (specifically where would you commit the queries) as we are dealing with millions of records.

The PL/SQL will:
• Insert into the RESULTS table each customer id, and the number of unique products purchased by that customer.
• Update the recently purchased column of the customer table to 'Y' (yes if they have purchased a product in the last 12 months) or 'N' (if they have not purchased a product in the last 12 months).

Listed below are the tables & definitions:

Table: CUSTOMER Columns: customer_id NUMBER, customer_name VARCHAR2(100), recently_purchased VARCHAR2(1) -- 'Y' or 'N'
Table: CUST_PRODUCTS columns: product_id NUMBER, customer_id NUMBER, date_purchased DATE
Table: RESULTS columns: customer_id NUMBER, product_count NUMBER