How to get Oracle Database dependents objects

Hi folks, (português mais abaixo)

After some time I decided to continue posting at this blog even having a channel on youtube. My channel was created to share some stuff like tips, experiences, tutorials about Databases (specially Oracle), Linux and new techologies all in portuguese language.
If you are interested in this stuff, please go to https://www.youtube.com/channel/UCDncbo0hpBTEYMjZNZwsfmw and subscribe. 

Today, I'm glad to share with you a query to get objects dependencies in Oracle Databases. It's very useful when you have to change some object and want to see if some others objects have to be updated too to avoid a broken application. Here I'm using a hierarchical query to get object depedency by level: 

SELECT LPAD('-',level) || owner || '.' || name ||'('|| type || ')' DEPENDENCY
FROM dba_dependencies 
CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name 
AND prior type = referenced_type 
START WITH referenced_owner = '&owner'
AND referenced_name = '&object'
AND owner IS NOT NULL;

You have to provide OWNER and OBJECT you are going to change. The depedencies will be tree-like displayed as below:

SYS@TEST > /   
Enter value for owner: HR
Enter value for object: JOB_HISTORY

-HR.ADD_JOB_HISTORY(PROCEDURE)
 -HR.UPDATE_JOB_HISTORY(TRIGGER)

2 rows selected.

Output above means UPDATE_JOB_HISTORY trigger depends on ADD_JOB_HISTORY procedure that depends on JOB_HISTORY table (provided object). Let's check:

SYS@TEST > select text from dba_source where name='UPDATE_JOB_HISTORY';

TRIGGER update_job_history
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;

7 rows selected.

SYS@TEST > select text from dba_source where name='ADD_JOB_HISTORY';

PROCEDURE add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

13 rows selected.

I hope this helps you, as DBA or DEVELOPER, to avoid broke things up when changing objects.

Til next post.
Eduardo Valentim
-------------------------------------------------- Português --------------------------------------------------
Fala pessoal,

Depois de algum tempo decidi continuar postando nesse blog mesmo tendo um canal no youtube. Meu canal foi criado para compartilhar algumas coisas como dicas, experiências, tutoriais sobre bancos de dados (principalmente Oracle), Linux e novas tecnologias tudo em português. Se estiver interessado nesse tipo de coisa, por favor acesse https://www.youtube.com/channel/UCDncbo0hpBTEYMjZNZwsfmw e assine o canal. 

Hoje estou feliz de trazer pra você uma consulta para obter as dependências de objetos no banco de dados Oracle. É muito útil quando você tem que alterar algum objeto e quer ver se outros objetos também precisam ser atualizados para evitar que a aplicação "quebre". Aqui estou usando uma consulta hierárquica para obter o nível de dependência dos objetos: 

SELECT LPAD('-',level) || owner || '.' || name ||'('|| type || ')' DEPENDENCY
FROM dba_dependencies 
CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name 
AND prior type = referenced_type 
START WITH referenced_owner = '&owner'
AND referenced_name = '&object'
AND owner IS NOT NULL;

Você precisa fornecer o OWNER e o OBJECT que você modificará. As dependências aparecerão no estilo árvore conforme abaixo:

SYS@TEST > /   
Enter value for owner: HR
Enter value for object: JOB_HISTORY

-HR.ADD_JOB_HISTORY(PROCEDURE)
 -HR.UPDATE_JOB_HISTORY(TRIGGER)

2 rows selected.

A saída acima significa que a trigger UPDATE_JOB_HISTORY depende da procedure ADD_JOB_HISTORY que depende da tabela JOB_HISTORY (objeto informado). Vamos verificar:

SYS@TEST > select text from dba_source where name='UPDATE_JOB_HISTORY';

TRIGGER update_job_history
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;

7 rows selected.

SYS@TEST > select text from dba_source where name='ADD_JOB_HISTORY';

PROCEDURE add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

13 rows selected.

Espero que ajude você, como DBA ou DESENVOLVEDOR, a evitar quebrar as coisas quando estiver alterando objetos!

Até o próximo post.
Eduardo Valentim.