Задание 1. С помощью триггеров необходимо реализовать следующее бизнес-правило: зарплата любого сотрудника не может быть меньше минимальной зарплаты установленной для его должности/категории. При повышении минимальной зарплаты для той или иной должности/категории в таблице Jobs необходимо просмотреть данные по всем сотрудникам этой должности в таблице Employees и повысить до необходимой величины зарплату всем тем сотрудникам, чья текущая зарплата оказалась ниже нового минимального оклада. Создайте триггер, связанный с таблицей Jobs, который будет использовать пакетную хранимую процедуру.

• В пакет emp_pkg добавьте хранимую процедуру set_salary.
  • set_salary должна обновлять зарплаты сотрудников.
  • Процедура должна принимать на вход 2 параметра:
    • ID должности
    • и новую минимальную величину зарплаты для этой должности.
  • Процедура должна обновлять зарплату всех сотрудников соответствующей должности так, чтобы она была не меньше новой минимальной.
• Создайте строчный триггер upd_minsalary_trg, связанный с таблицей Jobs, который будет вызывать процедуру emp_pkg.set_salary, когда минимальный уровень зарплаты для той или иной должности будет изменяться.
• Протестируйте работу триггера. Для этого просмотрите информацию о всех сотрудниках, работающих в должности 'IT_PROG', зафиксируйте их текущие зарплаты. Увеличьте минимальный оклад для этой должности на $1000. Что должно было произойти, и что произошло на самом деле?

При тестировании работы триггера, вы столкнетесь с проблемой «mutating table» — чтение изменяющейся таблицы. (В таблице Employees есть свой триггер check_salary, который будет читать таблицу Jobs). Эту ошибку надо исправить.

CREATE OR REPLACE PACKAGE emp_pkg AUTHID CURRENT_USER IS
  -- ... ... ...
  PROCEDURE set_salary (jobID Employees.Job_ID%TYPE, newSalary Employees.Salary%TYPE);
END emp_pkg;
/

Package created.

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

  -- ... ... ...

  PROCEDURE set_salary (jobID Employees.Job_ID%TYPE, newSalary Employees.Salary%TYPE) IS
  BEGIN
    UPDATE Employees
    SET Salary = GREATEST(Salary, newSalary)
    WHERE Job_ID = jobID;
  END;

  -- ... ... ...

END emp_pkg;
/

Package body created.

CREATE OR REPLACE TRIGGER upd_minsalary_trg
AFTER UPDATE OF Min_Salary ON Jobs
FOR EACH ROW
WHEN (OLD.Min_Salary != NEW.Min_Salary)
BEGIN
  emp_pkg.set_salary(:NEW.Job_ID, :NEW.Min_Salary);
END;
/

Trigger created.

SELECT Last_Name, First_Name, Salary, Job_ID
FROM Employees
WHERE Job_ID = 'IT_PROG';

UPDATE Jobs
SET Min_Salary = Min_Salary + 1000
WHERE Job_ID = 'IT_PROG';

[42000][4091] ORA-04091: таблица RUMINAT.JOBS изменяется, триггер/функция может не заметить это
ORA-06512: на  "RUMINAT.CHECK_SALARY", line 6
ORA-06512: на  "RUMINAT.CHECK_SALARY_TRG", line 2
ORA-04088: ошибка во время выполнения триггера 'RUMINAT.CHECK_SALARY_TRG'
ORA-06512: на  "RUMINAT.EMP_PKG", line 128
ORA-06512: на  "RUMINAT.UPD_MINSALARY_TRG", line 2
ORA-04088: ошибка во время выполнения триггера 'RUMINAT.UPD_MINSALARY_TRG'

Мы должны были обновить поле Salary у работников с Job_ID = 'IT_PROG', однако вместо этого получили ошибку.

Задание 2. Чтобы обойти проблему «mutating table» вам потребуется создать временную копию таблицы Jobs изменить процедуру check_salary так, чтобы она обращалась к этой временной таблице, вместо чтения оригинальной таблицы Jobs.

• Создайте новый пакет jobs_pkg со следующей спецификацией:

  PROCEDURE initialize;
  FUNCTION  get_minSalary (jobID VARCHAR2) RETURN NUMBER;
  FUNCTION  get_maxSalary (jobID VARCHAR2) RETURN NUMBER;
  PROCEDURE set_minSalary (jobID VARCHAR2, min_salary NUMBER);
  PROCEDURE set_maxSalary (jobID VARCHAR2, max_salary NUMBER);

• Создайте тело пакета jobs_pkg:
  • В теле пакета опишите тип индексированной таблицы jobs_tab_type, записи в этой таблице должны быть Jobs%ROWTYPE, а проиндексированы они должны быть с помощью строковых переменных типа Jobs.Job_ID%TYPE.
  • Создайте локальную переменную в пакете jobsTab типа jobs_tab_type.
  • В теле процедуры initialize в цикле заполните индексированную таблицу на основе таблицы Jobs. Значения из столбца Jobs.Job_ID используйте в качестве индексного значения.
  • Опишите функцию get_minSalary: функция по индексу находит в таблице запись и возвращает значение столбца Min_Salary.
  • Опишите функцию get_maxSalary: функция по индексу находит в таблице запись и возвращает значение столбца Max_Salary.
  • Опишите процедуры set_minSalary и set_maxSalary, которые будут обновлять соответствующие столбцы в индексированной таблице пакета jobsTab.
• Измените описание хранимой процедуры check_salary (которую вы создавали в упражнении 17.1) следующим образом:
  • Закомментируйте выборку минимального и максимального значения из таблицы Jobs.
  • Заполните локальные переменные minSal и maxSal с помощью функций jobs_pkg.get_minSalary и jobs_pkg.get_maxSalary соответственно.
• Создайте statement триггер BEFORE INSERT OR UPDATE для таблицы Jobs с именем init_jobspkg_trg. В теле триггера вызовите на исполнение хранимую процедуру jobs_pkg.initialize, чтобы заполнить локальную индексированную таблицу в пакете до того, как начнется модификация данных.
• Протестируйте работу нового кода:
  • Просмотрите информацию о программистах и их зарплатах.
  • Увеличьте минимально возможную зарплату для программистов ('IT_PROG') в таблице Jobs на $1000.
  • Просмотрите информацию о программистах заново. Найдите и перечислите тех сотрудников, зарплата которых изменилась.

CREATE OR REPLACE PROCEDURE
  check_salary (jobID Employees.Job_ID%TYPE, sal Employees.Salary%TYPE) IS
  minSal Jobs.Min_Salary%TYPE := jobs_pkg.get_minSalary(jobID);
  maxSal Jobs.Max_Salary%TYPE := jobs_pkg.get_maxSalary(jobID);
BEGIN
  -- SELECT Min_Salary, Max_Salary
  -- INTO   minSal,     maxSal
  -- FROM Jobs
  -- WHERE Job_ID = jobID;
 
  IF sal NOT BETWEEN minSal AND maxSal THEN
    RAISE_APPLICATION_ERROR(
      -20001,
        'Invalid salary '|| sal ||' for this job. Salaries must be between '
      || minSal ||' and '|| maxSal
    );
  END IF;
END check_salary;
/

Procedure created.

CREATE OR REPLACE PACKAGE jobs_pkg IS
  PROCEDURE initialize;
  FUNCTION  get_minSalary (jobID VARCHAR2) RETURN NUMBER;
  FUNCTION  get_maxSalary (jobID VARCHAR2) RETURN NUMBER;
  PROCEDURE set_minSalary (jobID VARCHAR2, min_salary NUMBER);
  PROCEDURE set_maxSalary (jobID VARCHAR2, max_salary NUMBER);
END jobs_pkg;
/

Package created.

CREATE OR REPLACE PACKAGE BODY jobs_pkg IS
  TYPE jobs_tab_type IS TABLE OF Jobs%ROWTYPE INDEX BY Jobs.Job_ID%TYPE;
  jobsTab jobs_tab_type;

  PROCEDURE initialize IS
  BEGIN
    FOR job IN (SELECT * FROM Jobs) LOOP
      jobsTab(job.Job_ID) := job;
    END LOOP;
  END;
  
  FUNCTION  get_minSalary (jobID VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN jobsTab(jobID).Min_Salary;
  END;
  
  FUNCTION  get_maxSalary (jobID VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN jobsTab(jobID).Max_Salary;
  END;
  
  PROCEDURE set_minSalary (jobID VARCHAR2, min_salary NUMBER) IS
  BEGIN
    jobsTab(jobID).Min_Salary := min_salary;
  END;
  
  PROCEDURE set_maxSalary (jobID VARCHAR2, max_salary NUMBER) IS
  BEGIN
    jobsTab(jobID).Max_Salary := max_salary;
  END;

END jobs_pkg;
/

Package body created.

CREATE OR REPLACE TRIGGER init_jobspkg_trg
BEFORE INSERT OR UPDATE ON Jobs
BEGIN
  jobs_pkg.initialize;
END;
/

Trigger created.

SELECT * FROM Jobs WHERE Job_ID = 'IT_PROG';

SELECT Last_Name, First_Name, Salary, Job_ID
FROM Employees
WHERE Job_ID = 'IT_PROG';

UPDATE Jobs
SET Min_Salary = Min_Salary + 1000
WHERE Job_ID = 'IT_PROG';

1 row updated.

SELECT * FROM Jobs WHERE Job_ID = 'IT_PROG';

SELECT Last_Name, First_Name, Salary, Job_ID
FROM Employees
WHERE Job_ID = 'IT_PROG';

Задание 3. Протестируйте работу триггеров при добавлении нового сотрудника:

• Прервите сеанс работы с БД, а затем подключитесь заново.
• С помощью процедуры emp_pkg.add_employee добавьте нового сотрудника со следующими параметрами
  • ('Steven', 'Morse', 'SMORSE', sal => 6500)
  Что произойдет?
• Для исправления ситуации создайте BEFORE INSERT OR UPDATE триггер employee_initJobs_trg на таблицу Employees, в теле которого вызывайте на исполнение процедуру jobs_pkg.initialize.
• Еще раз протестируйте работу триггеров и попытайтесь добавить 'Steven Morse'. Убедитесь, что информация о новом сотруднике успешно добавлена в таблицу.

EXECUTE emp_pkg.add_employee('Steven', 'Morse', 'SMORSE', sal => 6500);

[02000][1403] ORA-01403: данные не найдены
ORA-01403: данные не найдены
ORA-06512: на  "RUMINAT.JOBS_PKG", line 14
ORA-06512: на  "RUMINAT.CHECK_SALARY", line 3
ORA-06512: на  "RUMINAT.CHECK_SALARY_TRG", line 2
ORA-04088: ошибка во время выполнения триггера 'RUMINAT.CHECK_SALARY_TRG'
ORA-06512: на  "RUMINAT.EMP_PKG", line 34
ORA-06512: на  line 2

Мы получили ошибку, так как таблица jobs_pkg.jobsTab на данный момент не заполнена.

CREATE OR REPLACE TRIGGER employee_initJobs_trg
BEFORE INSERT OR UPDATE ON Employees
BEGIN
  jobs_pkg.initialize;
END;
/

Trigger created.

CREATE OR REPLACE TRIGGER upd_minsalary_trg
FOR UPDATE OF Min_Salary ON Jobs
COMPOUND TRIGGER

    TYPE minSalaryMap IS TABLE OF Jobs.min_salary%TYPE INDEX BY Jobs.job_id%TYPE;
    jobsTab minSalaryMap;
    jobID   Jobs.job_id%TYPE;

  AFTER EACH ROW IS
  BEGIN
    IF :OLD.Min_Salary != :NEW.Min_Salary THEN
      jobsTab(:NEW.job_id) := :NEW.min_salary;
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
      jobID := jobsTab.FIRST;
      WHILE jobID IS NOT NULL LOOP
        emp_pkg.set_salary(jobID, jobsTab(jobID));
        jobID := jobsTab.NEXT(jobID);
      END LOOP;
  END AFTER STATEMENT;
END upd_minsalary_trg;
/

Trigger created.

EXECUTE emp_pkg.add_employee('Steven', 'Morse', 'SMORSE', sal => 6500);

SELECT Employee_ID, Last_Name, First_Name, Email, Job_ID, Salary, Department_ID
FROM Employees WHERE Last_Name = 'Morse';

Задание 1. Запустите скрипт с именем lab_10_01.sql. Создайте и выполните запрос для осуществления выборки из словаря USER_PLSQL_OBJECT_SETTINGS следующей информации о параметрах инициализации, влияющих на компиляцию кода:

• Имя объекта.
• Тип объекта.
• Был ли объект 'compiled for debugging'.
• Режим компиляции объекта.
• Уровень оптимизации компиляции.

Зафиксируйте результат. Обратите внимание на информацию об объекте add_job_history.

Задание 2. Измените значение параметра plsql_code_type для текущей сессии и скомпилируйте объект add_job_history.

• Выполните команду ALTER SESSION для разрешения native compilation.
• Перекомпилируйте объект add_job_history.
• Выполните запрос из задачи 19.1 ещё раз. Зафиксируйте результат. Отметьте, что изменилось.
• Верните обратно прежний режим компиляции.

ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';
ALTER PROCEDURE add_job_history COMPILE;

Session altered.
Procedure altered.

SELECT Name, Type, PLSQL_Debug, PLSQL_Code_Type, PLSQL_Optimize_Level
FROM USER_PLSQL_OBJECT_SETTINGS;

ALTER SESSION SET PLSQL_CODE_TYPE = 'INTERPRETED';
ALTER PROCEDURE add_job_history COMPILE;

Session altered.
Procedure altered.

Задание 3. С помощью Tools -> Preference -> Database -> PL/SQL Compiler отключите все категории compiler warnings.

• Отредактируйте, просмотрите и выполните скрипт lab_10_04.sql для создания процедуры unreachable_code. Перекомпилируйте процедуру после создания.
• Удалось ли вам получить какие-то сообщения об ошибках или предупреждения на вкладке Compiler — Log?

ALTER SESSION SET PLSQL_WARNINGS = 'disable:all';

Session altered.

Скрипт lab_10_04.sql:

CREATE OR REPLACE PROCEDURE unreachable_code AS
  c_x CONSTANT BOOLEAN := TRUE;
BEGIN
  IF c_x THEN
    DBMS_OUTPUT.PUT_LINE('TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('FALSE');
  END IF;
END unreachable_code;
/

Procedure created.

Сообщений об ошибках или предупреждений не было.

Задание 4. С помощью Tools -> Preference -> PL/SQL Compiler Options включите отображение всех предупреждений компилятора.

• Перекомпилируйте процедуру unreachable_code.
• Что отобразилось на вкладке Compiler — Log? (Вы так же можете воспользоваться словарем USER_ERRORS).

ALTER SESSION SET PLSQL_WARNINGS = 'enable:all';

Session altered.

CREATE OR REPLACE PROCEDURE unreachable_code AS
  c_x CONSTANT BOOLEAN := TRUE;
BEGIN
  IF c_x THEN
    DBMS_OUTPUT.PUT_LINE('TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('FALSE');
  END IF;
END unreachable_code;
/

1:1:PLW-05018: в блоке UNREACHABLE_CODE опущена необязательная фраза AUTHID; используется значение по умолчанию DEFINER
7:5:PLW-06002: Недоступный код

Задание 5. Создайте скрипт с именем warning_msgs, в котором используется EXECUTE в сочетании с процедурами из пакетов DBMS_OUTPUT и DBMS_WARNING для идентификации категорий следующих сообщений: 5050, 6075, 7100.

EXECUTE DBMS_OUTPUT.PUT_LINE('Category of 5050-message is '|| DBMS_WARNING.GET_CATEGORY(5050));
EXECUTE DBMS_OUTPUT.PUT_LINE('Category of 6075-message is '|| DBMS_WARNING.GET_CATEGORY(6075));
EXECUTE DBMS_OUTPUT.PUT_LINE('Category of 7100-message is '|| DBMS_WARNING.GET_CATEGORY(7100));

Category of 5050-message is SEVERE
Category of 6075-message is INFORMATIONAL
Category of 7100-message is PERFORMANCE

Задание 1. Просмотрите и выполните скрипт lab_11_01.sql. В результате выполнения скрипта будет установлен флаг для отображения debugging и trace информации. Скрипт так же создаст пакет my_pkg и процедуру circle_area.

ALTER SESSION SET PLSQL_CCFLAGS = 'my_debug:FALSE, my_tracing:FALSE';
CREATE OR REPLACE PACKAGE my_pkg AS
  SUBTYPE my_real IS
    $IF DBMS_DB_VERSION.VERSION < 10 $THEN NUMBER; -- check database version
      $ELSE                                BINARY_DOUBLE;
    $END
  my_pi my_real; my_e my_real;
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg AS
BEGIN 
  $IF DBMS_DB_VERSION.VERSION < 10 $THEN
       my_pi := 3.14016408289008292431940027343666863227;
       my_e  := 2.71828182845904523536028747135266249775;
  $ELSE
       my_pi := 3.14016408289008292431940027343666863227d;
       my_e  := 2.71828182845904523536028747135266249775d;
  $END
END my_pkg;
/

CREATE OR REPLACE PROCEDURE circle_area(radius my_pkg.my_real) IS
  my_area my_pkg.my_real;
  my_datatype VARCHAR2(30);
BEGIN
  my_area := my_pkg.my_pi * radius;
  DBMS_OUTPUT.PUT_LINE('Radius: ' || TO_CHAR(radius) 
                       || ' Area: ' || TO_CHAR(my_area) );

  $IF $$my_debug $THEN -- if my_debug is TRUE, run some debugging code
    SELECT DATA_TYPE INTO my_datatype FROM USER_ARGUMENTS 
       WHERE OBJECT_NAME = 'CIRCLE_AREA' AND ARGUMENT_NAME = 'RADIUS';
     DBMS_OUTPUT.PUT_LINE('Datatype of the RADIUS argument is: ' || my_datatype);
  $END
END;
/

Session altered.
Package created.
Package body created.
Procedure created.

Задание 2. С помощью подпрограммы DBMS_PREPROCESSOR отобразите текст из файла lab_11_01.sql в том виде, который он примет после выполнения директив условной компиляции.

DECLARE
  PROCEDURE printSource(
     sourceType   VARCHAR2
   , sourceName   VARCHAR2
   , sourceSchema VARCHAR2 := USER
  ) IS
    lines DBMS_PREPROCESSOR.SOURCE_LINES_T;
  BEGIN
    lines := DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE(sourceType, sourceSchema, sourceName);
    FOR i IN 1..lines.COUNT LOOP
      DBMS_OUTPUT.PUT(lines(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END;
BEGIN
  printSource('package',       'my_pkg');
  printSource('package body',  'my_pkg');
  printSource('procedure',     'circle_area');
END;
/

PACKAGE my_pkg AS
  SUBTYPE my_real IS
    
                                     BINARY_DOUBLE;

  my_pi my_real;
my_e my_real;
END my_pkg;
PACKAGE BODY my_pkg AS
BEGIN
  



       my_pi := 3.14016408289008292431940027343666863227d;
       my_e  := 2.71828182845904523536028747135266249775d;
  
END my_pkg;
PROCEDURE circle_area(radius my_pkg.my_real) IS
  my_area my_pkg.my_real;
my_datatype VARCHAR2(30);
BEGIN
  my_area := my_pkg.my_pi * radius;
DBMS_OUTPUT.PUT_LINE('Radius: ' || TO_CHAR(radius)
                       || ' Area: ' || TO_CHAR(my_area) );





END;

Задание 3. Создайте PL/SQL скрипт, который использует DBMS_DB_VERSION константу. Скрипт определяет версию Oracle.

• Если версия меньше или равна 10.1, необходимо выдать сообщение:

  Unsupported database release

• В ином случае — показать сообщение:

  Release {используемые версия СУБД и номер релиза} is supported
  

BEGIN
  $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
    DBMS_OUTPUT.PUT_LINE('Unsupported database release');
  $ELSE
    DBMS_OUTPUT.PUT_LINE(
         'Release '
      || DBMS_DB_VERSION.VERSION ||'.'|| DBMS_DB_VERSION.RELEASE
      || ' is supported'
    );
  $END
END;
/

Release 18.0 is supported

Задание 4. Откройте скрипт lab_11_04.sql. В нем предполагается использование предустановленной процедуры create_wrapped для динамического создания и обработки (шифрования) пакета. Отредактируйте скрипт следующим образом:

• Добавьте в используемый анонимный блок исполняемую секцию, в которой:
  • Сгенерируйте текст для создания спецификации пакета с помощью процедуры generate_spec, установив значение параметра pkgname равным 'EMP_ACTIONS'. Сохраните его в соответствующей переменной.
  • Создайте и зашифруйте (wrap) спецификацию пакета.
  • Сгенерируйте текст для создания тела пакета с помощью процедуры generate_body. Сохраните его в соответствующей переменной.
  • Создайте и зашифруйте (wrap) тело пакета.
• Сохраните и выполните скрипт.
• Вызовите процедуру emp_actions.raise_salary с параметрами (120, 100). Проверьте результат;
• С помощью словаря USER_SOURCE проверьте, что исполняемый код скрыт.

Задание 1. Проанализируйте зависимости между объектами в следующем сценарии:

Хранимая процедура my_proc вызывает внутри себя на исполнение другую хранимую процедуру my_proc_pack, размещенную в пакете. Скажется ли изменение реализации и перекомпиляция процедуры my_proc_pack (без изменения ее объявления в спецификации пакета) на статус процедуры my_proc. Будет ли она помечена как INVALID?

CREATE OR REPLACE PACKAGE testPackage IS
  PROCEDURE my_proc_pack;
END testPackage;
/
CREATE OR REPLACE PACKAGE BODY testPackage IS
  PROCEDURE my_proc_pack IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('I say! I''m testPackage.my_proc_pack!');
  END;
END testPackage;
/

CREATE OR REPLACE
PROCEDURE my_proc IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello there, I''m my_proc!');
  DBMS_OUTPUT.PUT_LINE('I want to call the testPackage.my_proc_pack.');
  testPackage.my_proc_pack;
END;
/

Package created.
Package body created.
Procedure created.

SELECT Object_Name, Object_Type, Status
FROM USER_OBJECTS
WHERE LOWER(Object_Name) = 'my_proc';

Изменим код процедуры testPackage.my_proc_pack и перекомпилируем её.

CREATE OR REPLACE PACKAGE testPackage IS
  PROCEDURE my_proc_pack;
END testPackage;
/
CREATE OR REPLACE PACKAGE BODY testPackage IS
  PROCEDURE my_proc_pack IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('I say! I''m testPackage.my_proc_pack!');
    DBMS_OUTPUT.PUT_LINE('I''m a completely different procedure now!');
  END;
END testPackage;
/

Package created.
Package body created.

SELECT Object_Name, Object_Type, Status
FROM USER_OBJECTS
WHERE LOWER(Object_Name) = 'my_proc';

Как можно заметить, процедура my_proc не получила статус INVALID.

Задание 2. Создайте древовидную структуру, отражающую все зависимости между объектами на примере процедуры add_employee и функции valid_deptid.

• Просмотрите и выполните скрипт utldtree.sql из папки курса.
• Выполните процедуру deptree_fill для объекта add_employee.
• С помощью представления ideptree просмотрите результат.
• Выполните процедуру deptree_fill для объекта valid_deptid.
• С помощью представления ideptree просмотрите результат.
• Проанализируйте код процедуры deptree_fill. Какие системные ресурсы она опрашивает.

Скрипт utldtree.sql:

Rem 
Rem $Header: utldtree.sql,v 1.2 1992/10/26 16:24:44 RKOOI Stab $ 
Rem 
Rem  Copyright (c) 1991 by Oracle Corporation 
Rem    NAME
Rem      deptree.sql - Show objects recursively dependent on given object
Rem    DESCRIPTION
Rem      This procedure, view and temp table will allow you to see all
Rem      objects that are (recursively) dependent on the given object.
Rem      Note: you will only see objects for which you have permission.
Rem      Examples:
Rem        execute deptree_fill('procedure', 'scott', 'billing');
Rem        select * from deptree order by seq#;
Rem
Rem        execute deptree_fill('table', 'scott', 'emp');
Rem        select * from deptree order by seq#;
Rem
Rem        execute deptree_fill('package body', 'scott', 'accts_payable');
Rem        select * from deptree order by seq#;
Rem
Rem        A prettier way to display this information than
Rem		select * from deptree order by seq#;
Rem	   is
Rem             select * from ideptree;
Rem        This shows the dependency relationship via indenting.  Notice
Rem        that no order by clause is needed with ideptree.
Rem    RETURNS
Rem 
Rem    NOTES
Rem      Run this script once for each schema that needs this utility.
Rem      
Rem    MODIFIED   (MM/DD/YY)
Rem     rkooi      10/26/92 -  owner -> schema for SQL2 
Rem     glumpkin   10/20/92 -  Renamed from DEPTREE.SQL 
Rem     rkooi      09/02/92 -  change ORU errors 
Rem     rkooi      06/10/92 -  add rae errors 
Rem     rkooi      01/13/92 -  update for sys vs. regular user 
Rem     rkooi      01/10/92 -  fix ideptree 
Rem     rkooi      01/10/92 -  Better formatting, add ideptree view 
Rem     rkooi      12/02/91 -  deal with cursors 
Rem     rkooi      10/19/91 -  Creation 

drop sequence deptree_seq
/
create sequence deptree_seq cache 200 /* cache 200 to make sequence faster */
/
drop table deptree_temptab
/
create table deptree_temptab
(
  object_id            number,
  referenced_object_id number,
  nest_level           number,
  seq#                 number      
)
/
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;
/

drop view deptree
/

set echo on

REM This view will succeed if current user is sys.  This view shows 
REM which shared cursors depend on the given object.  If the current
REM user is not sys, then this view get an error either about lack
REM of privileges or about the non-existence of table x$kglxs.

set echo off
create view sys.deptree
  (nested_level, type, schema, name, seq#)
as
  select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#
  from deptree_temptab d, dba_objects o
  where d.object_id = o.object_id (+)
union all
  select d.nest_level+1, 'CURSOR', '', '"'||c.kglnaobj||'"', d.seq#+.5
  from deptree_temptab d, x$kgldp k, x$kglob g, obj$ o, user$ u, x$kglob c,
      x$kglxs a
    where d.object_id = o.obj#
    and   o.name = g.kglnaobj
    and   o.owner# = u.user#
    and   u.name = g.kglnaown
    and   g.kglhdadr = k.kglrfhdl
    and   k.kglhdadr = a.kglhdadr   /* make sure it is not a transitive */
    and   k.kgldepno = a.kglxsdep   /* reference, but a direct one */
    and   k.kglhdadr = c.kglhdadr
    and   c.kglhdnsp = 0 /* a cursor */
/

set echo on

REM This view will succeed if current user is not sys.  This view
REM does *not* show which shared cursors depend on the given object.
REM If the current user is sys then this view will get an error 
REM indicating that the view already exists (since prior view create
REM will have succeeded).

set echo off
create view deptree
  (nested_level, type, schema, name, seq#)
as
  select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#
  from deptree_temptab d, all_objects o
  where d.object_id = o.object_id (+)
/

drop view ideptree
/
create view ideptree (dependencies)
as
  select lpad(' ',3*(max(nested_level))) || max(nvl(type, '')
    || ' ' || schema || decode(type, NULL, '', '.') || name)
  from deptree
  group by seq# /* So user can omit sort-by when selecting from ideptree */
/

EXECUTE deptree_fill('procedure', 'ruminat', 'add_employee');

PL/SQL procedure successfully completed.

SELECT * FROM ideptree;

EXECUTE deptree_fill('function', 'ruminat', 'valid_deptid');

PL/SQL procedure successfully completed.

SELECT * FROM ideptree;

Процедура deptree_fill использует следующие системные словари: ALL_OBJECTS, PUBLIC_DEPENDENCY.

Задание 3. Динамически определите INVALID объекты:

1. Создайте копию таблицы Employees и назовите её Emps.

   CREATE TABLE Emps AS SELECT * FROM Employees;

   Table created.

2. Измените таблицу Employees. Добавьте в нее колонку TotSal с типом данных NUMBER(9, 2).

   ALTER TABLE Employees ADD (TotSal NUMBER(9, 2));

   Table altered.

3. Создайте и сохраните локально скрипт для отображения имени, типа и статуса всех INVALID объектов. Запустите его и посмотрите результат.

   CREATE OR REPLACE
   PROCEDURE displayInvalidObjects IS
     counter SIMPLE_INTEGER := 0;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('INVALID objects:');
     FOR queryRow IN (
       SELECT *
       FROM USER_OBJECTS
       WHERE Status = 'INVALID'
       ORDER BY Object_Type
     ) LOOP
       counter := counter + 1;
       DBMS_OUTPUT.PUT_LINE(
           ' - '|| queryRow.Object_Type
         ||': ' || queryRow.Object_Name ||' is '|| queryRow.Status
       );
     END LOOP;
     IF counter = 0 THEN
       DBMS_OUTPUT.PUT_LINE('none');      
     END IF;
   END;
   /
   
   BEGIN
     displayInvalidObjects;
   END;
   /

   Procedure created.
   
   INVALID objects:
   - PACKAGE: EMP_PKG is INVALID
   - PACKAGE BODY: EMP_PKG is INVALID
   - PACKAGE BODY: EMP_ACTIONS is INVALID
   - PROCEDURE: GET_EMPLOYEE is INVALID
   - TRIGGER: UPD_MINSALARY_TRG is INVALID
   - TRIGGER: DELETE_EMP_TRG is INVALID
   - TRIGGER: EMPLOYEE_INITJOBS_TRG is INVALID
   - VIEW: EMPLOYEES_VU is INVALID
   - VIEW: DISTR_VIEW is INVALID
   - VIEW: SALARY_VU is INVALID

4. В пакет compile_pkg добавьте процедуру recompile, которая перекомпилирует все INVALID объекты схемы (с использованием Native Dynamic SQL).

   CREATE OR REPLACE PACKAGE compile_pkg IS
     PROCEDURE make (objectName USER_SOURCE.Name%TYPE);
     PROCEDURE recompile;
   END compile_pkg;
   /

   Package created.

   CREATE OR REPLACE PACKAGE BODY compile_pkg IS
    
     -- ... ... ...
   
     PROCEDURE recompile IS
         objectType USER_SOURCE.Type%TYPE;
         statement VARCHAR2(256);
       BEGIN
         FOR queryRow IN (
           SELECT *
           FROM USER_OBJECTS
           WHERE Status = 'INVALID'
         ) LOOP
           objectType := REGEXP_SUBSTR(queryRow.Object_Type, '\w+', 1, 1);
           statement  := 'ALTER '|| objectType ||' '|| queryRow.Object_Name;
           IF objectType = 'PACKAGE' THEN
             EXECUTE IMMEDIATE statement ||' COMPILE SPECIFICATION';    
             EXECUTE IMMEDIATE statement ||' COMPILE BODY';    
           ELSE
             EXECUTE IMMEDIATE statement ||' COMPILE';    
           END IF;
         END LOOP;
       END;
   
   END compile_pkg;
   /

   Package body created.

5. Выполните процедуру compile_pkg.recompile.

   BEGIN
     compile_pkg.recompile;
   END;
   /

   PL/SQL procedure successfully completed.

6. Выполните заново скрипт из пункта 3. Убедитесь, что объектов со статусом INVALID в схеме не осталось.

   BEGIN
     displayInvalidObjects;
   END;
   /

   INVALID objects:
   none