Different examples for training to exam Oracle Database 19c: Program with PL/SQL 1Z0-149. Part 3.

CHAPTER 17. "Creating triggers"
CHAPTER 18. "Creating Compound, DDL, and event Database Triggers"
CHAPTER 19. "PLSQL Compiler"
CHAPTER 20. "Managing PLSQL code"
CHAPTER 21. "Dependencies"
CHAPTER 22. "Another features"


CHAPTER 17. "Creating triggers"
Task 17.1. About triggers.
    1. What is trigger? What is maximum size of trigger?
    2. For which objects trigger can be defined?
    3. List the trigger event types.
    4. For which business scenarios we can use triggers?
    5. List trigger types.
    6. Where in dictionary we can find info about triggers?
    7. For which schema object we can`t create trigger?
Solution:
    --1
    Trigger is a PL/SQL block that is stored in the database and automatically 
    executed in response to a specified event. 32k is maximum size of trigger.
    --2
    Table, view, schema or database (for all users).
    --3
    DML: DELETEINSERTUPDATE [OF column].
    DDL: CREATEALTERDROP.
    Database operations: SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN.
    --4
    Security: insert only in working hours.
    Auditing: log all the transactions for specific tables.
    Data integrity: complex integrity rules which not standard.
    Referential integrity: non standard referential.
    Table replication: synchronize a table.
    Computing derived data automatically.
    Event logging.
    --5
    - Simple DML triggers: BEFOREAFTER, INSTEAD OF;
    - Compound triggers;
    - Non-DML triggers: DDL event triggers; database event triggers.
    --6
    SELECT * FROM USER_TRIGGERS
    --7
    SYS

Task 17.2. DML triggers. Part 1.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5)     DEFAULT 0, 
                           COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
    Then do next:
    1. List and explain two types of DML-trigger.
    2. For TAB1 demonstrate two types of trigger when try to update (before):
    - any columns;
    - only column COL2.
    Which triggers will be fired for next blocks:
    --A
    BEGIN
        UPDATE TAB1 SET COL1 = 0 WHERE COL2 = 'two';
    END;
    --B
    BEGIN
        UPDATE TAB1 SET COL2 = 't' WHERE COL1 = 99;
    END;
Solution:
    --1
    Statement-Level trigger:
    - is the default for trigger;
    - fires once for the triggering event;
    - fires once even if no rows are affected.
    Row-Level trigger:
    - use the FOR EACH ROW clause when creating a trigger;
    - fires once for each row affected by the triggering event;
    - not fired if the triggering event not affected to any rows.
    --2
    --Statement-Level triggers
    --Will be fired for A AND B
    CREATE OR REPLACE TRIGGER TS_BU
        BEFORE UPDATE
        ON TAB1
    BEGIN
        DBMS_OUTPUT.PUT_LINE('S_level');
    END;
    --Will be fired for B
    CREATE OR REPLACE TRIGGER TS_COL2_BU
        BEFORE UPDATE OF COL2
        ON TAB1
    BEGIN
        DBMS_OUTPUT.PUT_LINE('S-Level_COL2');
    END;
    --Row-Level triggers
    --Will be fired for A
    CREATE OR REPLACE TRIGGER TR_BU
        BEFORE UPDATE
        ON TAB1
        FOR EACH ROW
    BEGIN
        DBMS_OUTPUT.PUT_LINE('R-Level');
    END;
    --Not will be fired
    CREATE OR REPLACE TRIGGER TR_COL2_BU
        BEFORE UPDATE OF COL2
        ON TAB1
        FOR EACH ROW
    BEGIN
        DBMS_OUTPUT.PUT_LINE('R-Level_COL2');
    END;
    
Task 17.3. DML triggers. Part 2.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5)     DEFAULT 0, 
                           COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
    Then do next:
    1. Write trigger for TAB1 that will be display '1' after any operation: DELETE,
    UPDATEINSERT. But if day from sysdate <= 29, then raise errors:
    - if insert row then raise error with message 'Err_I';
    - if update only COL2 then raise error with message 'Err_U';
    - if delete row then raise error with message 'Err_D';
    2. What priority for trigger on table and constraints for table?
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG_ADIU
        AFTER DELETE OR 
              UPDATE OF COL2 OR
              INSERT
        ON TAB1
    BEGIN
        IF EXTRACT(DAY FROM SYSDATE) <= 29 THEN
            IF INSERTING THEN
                RAISE_APPLICATION_ERROR(-20001, 'Err_I');
            ELSIF UPDATING THEN
                RAISE_APPLICATION_ERROR(-20001, 'Err_U');
            ELSIF DELETING THEN
                RAISE_APPLICATION_ERROR(-20001, 'Err_D');                
            END IF;
        ELSE
            DBMS_OUTPUT.PUT_LINE('1');
        END IF;
    END;
    --2
    Trigger have priority over constraint. Trigger executes first.
    
Task 17.4. DML triggers. New and old values.
    Do next operations:
        DROP TABLE TAB1;
        DROP TABLE TLOG;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        CREATE TABLE TLOG(OLD_C1 NUMBER,
                          OLD_C2 VARCHAR2(100),
                          NEW_C1 NUMBER,
                          NEW_C2 VARCHAR2(100),
                          OPER   VARCHAR2(100));
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
    Then do next:
    1. Create trigger, that after insert, delete, update rows in TAB1, will load 
    old and new values and name of operation into TLOG. Indicate optional
    construction in create trigger.
    2. Create trigger that will be fired only if we insert into TAB1.COL1 value 4,
    TAB1.COL2 value 'four' and trigger must raise error.
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG
        AFTER INSERT OR
              DELETE OR
              UPDATE
        ON TAB1
        REFERENCING NEW AS NEW OLD AS OLD --optinal
        FOR EACH ROW
    BEGIN
        IF INSERTING THEN
            INSERT INTO TLOG(NEW_C1, NEW_C2, OPER) 
                VALUES(:NEW.COL1, :NEW.COL2, 'INSERT');
        ELSIF UPDATING THEN
            INSERT INTO TLOG(OLD_C1, OLD_C2, NEW_C1, NEW_C2, OPER)
                VALUES (:OLD.COL1, :OLD.COL2, :NEW.COL1, :NEW.COL2, 'UPDATE');
        ELSIF DELETING THEN
            INSERT INTO TLOG(OLD_C1, OLD_C2, OPER)
                VALUES(:OLD.COL1, :OLD.COL2, 'DELETE');
        END IF;
    END;
    --2
    CREATE OR REPLACE TRIGGER TRG
        BEFORE INSERT
        ON TAB1
        FOR EACH ROW
        WHEN (NEW.COL1 = 4 AND NEW.COL2 = 'four') --only for row-level triggers
    BEGIN
        RAISE_APPLICATION_ERROR(-20001, 'Error');
    END;
    
Task 17.5. DML triggers. Generating value.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
        DROP SEQUENCE SEQ;
        CREATE SEQUENCE SEQ
            START WITH 1
                MINVALUE 1
                    MAXVALUE 100
                        INCREMENT BY 1;
    Then do next:
    1. Create trigger for TAB1 if we insert new row and COL1 = NULL, then use 
    sequence value. For NEW and OLD use aliases N and O.
    Then insert row: COL1 = NULL, COL2 = 'v'.
    When we can`t use OLD and NEW pseudorecords?
Solution:
    --1
    --NEW trigger variables CAN BE CHANGED in BEFORE row insert or update triggers.
    --OLD values can ONLY BE READ and not changed.
    CREATE OR REPLACE TRIGGER TRG 
        BEFORE INSERT 
        ON TAB1
        REFERENCES NEW AS N OLD AS O
        FOR EACH ROW
    BEGIN
        IF :N.COL1 IS NULL THEN
            :N.COL1 := SEQ.NEXTVAL--:O.COL1 := SEQ.NEXTVAL; will fail
        END IF;
    END;
    INSERT INTO TAB1 VALUES(NULL, 'v');
    COMMIT;
    
Task 17.6. DML triggers. Firing sequence. ALTER.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
        CREATE OR REPLACE TRIGGER TRG
            BEFORE UPDATE
            ON TAB1
        BEGIN
            DBMS_OUTPUT.PUT_LINE('S_level');
        END;
    Then do next:
    1. List trigger firing sequence.
    2. How to compile trigger?
    3. How to disable all triggers on a table?
    4. How to enable all triggers on a table?
    5. How to disable and enable only trigger TRG?
    6. How to drop trigger? When we must drop trigger?
Solution:
    --1
    BEFORE statement trigger
    BEFORE row trigger
    AFTER row trigger
    AFTER statement trigger
    --2
    ALTER TRIGGER TRG COMPILE;
    --3
    ALTER TABLE TAB1 DISABLE ALL TRIGGERS;
    --4
    ALTER TABLE TAB1 ENABLE ALL TRIGGERS;
    --5
    ALTER TRIGGER TRG DISABLE;
    ALTER TRIGGER TRG ENABLE;
    --6
    --we must drop TRIGGER with same name created for another table before try to
    --CREATE OR REPLACE TIGGER with this name on different table.
    DROP TRIGGER TRG;

Task 17.7. DML triggers for view.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        CREATE OR REPLACE VIEW VV AS
            SELECT COL1, COL2 FROM TAB1;
    Then do next:
    1. Which trigger can be using only for view?
    2. Create trigger on view that if we insert row in view, row not will be inserted
    in table, but display in dbms_output text '10'. 
    Then make operation: INSERT INTO VV(COL1, COL2) SELECT COL1, COL2 FROM TAB1.
    Which result will be in view and TAB1? Which message will be in: DBMS_OUTPUT, in
    OUTPUT window and value at the SQL%ROWCOUNT after insert?
Solution:
    --1
    INSTEAD OF
    --2
    --fired for each row of the view that gets modified, for example:
    --INSTEAD OF INSERT OR UPDATE OR DELETE
    CREATE OR REPLACE TRIGGER TRG
        INSTEAD OF INSERT
        ON VV
        --FOR EACH ROW
    BEGIN
        DBMS_OUTPUT.PUT_LINE('10');
    END;
    --Rows not will be added in view and table.
    --DBMS_OUTPUT: 10 10 10; in output window: "3 rows inserted."; SQL%ROWCOUNT = 3.
    INSERT INTO VV(COL1, COL2) SELECT COL1, COL2 FROM TAB1;

Task 17.8. DML triggers. Another features.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5) PRIMARY KEY);
        INSERT INTO TAB1(COL1) VALUES(1);
        INSERT INTO TAB1(COL1) VALUES(2);
        COMMIT;
            DROP TABLE TAB2;
            CREATE TABLE TAB2 (COL1 NUMBER(5) CHECK(COL1 > 0));
    Then do next:
    1. Create trigger 'TRG' that will insert TAB2.COL1 = COL1 + 5 and this insert 
    must be COMMITED even if INSERT in TAB1 failed. If INSERT in TAB2 will fail then
    insert in TAB1 must be success. Insert in TAB1: COL1 = 1, COL1 = -100. 
    Explain result in both tables.
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG
        BEFORE INSERT
        ON TAB1
        FOR EACH ROW
    DECLARE
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO TAB2(COL1) VALUES(:NEW.COL1 + 5);
        --TCL commands: COMMITROLLBACKSAVEPOINT allowed in triggers with
        --PRAGMA AUTONOMOUS_TRANSACTION;
        COMMIT--Only commits the preceding INSERT, not the INSERT that fired
                --the trigger.
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    --
    INSERT INTO TAB1(COL1) VALUES(1); 
    COMMIT--in TAB1: 1, 2. In TAB2: 6.
    INSERT INTO TAB1(COL1) VALUES(-100); 
    COMMIT--in TAB1: -100, 1, 2. In TAB2: 6.


CHAPTER 18. "Creating Compound, DDL, and event Database Triggers"
Task 18.1. Sequence of execution of triggers.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
    Then do next:
    1. For TAB1 create trigger 'TRG1' with BEFORE INSERT that will be displayed 
    text '1'. Then create second trigger 'TRG2' with BEFORE INSERT, but this trigger 
    must be displayed '2' and fired only after trigger 'TRG1'. Also create trigger 
    'TRG3' with BEFORE INSERT that will be displayed text '3' and fired only after
    triggers 'TRG1' and 'TRG2'.
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG1
        BEFORE INSERT 
        ON TAB1
    BEGIN
        DBMS_OUTPUT.PUT_LINE('1');
    END;
    CREATE OR REPLACE TRIGGER TRG2
        BEFORE INSERT 
        ON TAB1
        FOLLOWS TRG1
    BEGIN
        DBMS_OUTPUT.PUT_LINE('2');
    END;
    CREATE OR REPLACE TRIGGER TRG3
        BEFORE INSERT 
        ON TAB1
        FOLLOWS TRG1, TRG2
    BEGIN
        DBMS_OUTPUT.PUT_LINE('3');
    END;
    
Task 18.2. Compound triggers.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
    Then do next:
    1. Since which version available compound trigger?
    2. For TAB1 create compound trigger 'TRG' which will be firing for inserting, 
    updating or deleting and that will be displayed text 'my' from common variable 
    'v' with string type + value from specific string`s variable 'r' that will be 
    indicate each part of trigger.
    Insert two rows: without subquery; using subquery. For each part of trigger 
    write quantity of firing.
Solution:
    --1
    11g
    --2
    --TIMING must be sequential. Quantity of TIMING can be 1, 2, 3 or 4.
    --TIMING`s duplicates not allowed.
    CREATE OR REPLACE TRIGGER TRG
        FOR INSERT OR
            UPDATE OR
            DELETE
        ON TAB1
    COMPOUND TRIGGER
        v VARCHAR2(100) := 'my'; --will be available in all 4 blocks
        --1
        BEFORE STATEMENT IS
            r VARCHAR2(100) := '1'; --will be available only in this block
        BEGIN
            DBMS_OUTPUT.PUT_LINE(v||r);
        END BEFORE STATEMENT;
        --2
        BEFORE EACH ROW IS
            r VARCHAR2(100) := '2';
        BEGIN
            DBMS_OUTPUT.PUT_LINE(v||r);
        END BEFORE EACH ROW;
        --3
        AFTER EACH ROW IS
            r VARCHAR2(100) := '3';
        BEGIN
            DBMS_OUTPUT.PUT_LINE(v||r);
        END AFTER EACH ROW;
        --4
        AFTER STATEMENT IS
            r VARCHAR2(100) := '4';
        BEGIN
            DBMS_OUTPUT.PUT_LINE(v||r);
        END AFTER STATEMENT;
    END;
    --Parts of trigger will be firing:
    --without subquery
    INSERT INTO TAB1 VALUES(4, 'four'); --1 2 3 4
    INSERT INTO TAB1 VALUES(5, 'five'); --1 2 3 4
    --with subquery
    INSERT INTO TAB1 
        SELECT * FROM (
                        SELECT 4, 'four' FROM DUAL
                        UNION ALL
                        SELECT 4, 'four' FROM DUAL
                      ); -- 1 2 2 3 3 4
    
Task 18.3. Mutating table.    
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        DROP TABLE TAB2;
        CREATE TABLE TAB2(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
    Then do next:
    1. Explain error of mutating table.
    2. Create NONcompound trigger 'TRG' on TAB2 for demonstrating mutating table.
Solution:
    --1
    When we try DELETEINSERTUPDATE table, can be occurs error of mutating table 
    when a row-level trigger attempts to read or change this table from which the 
    trigger was fired.
    --2
    CREATE OR REPLACE TRIGGER TRG 
       BEFORE INSERT --AFTER INSERT
       ON TAB2
       FOR EACH ROW
    DECLARE
        res NUMBER;
    BEGIN
        SELECT NVL(MAX(COL1), 0) + 1
          INTO res
            FROM TAB2;
        :NEW.COL1 := res;
    END;
    --FOR BEFORE INSERT:
    --If we try read and then insert 1 row - not will be error
        INSERT INTO TAB2(COL2) VALUES ('val');
        --When we try insert and read rows simultaneously (insert operations > 1) 
        --that will be error of mutating table.
        INSERT INTO TAB2(COL2) SELECT COL2 FROM TAB1;
    --FOR AFTER INSERT errors will be always
    
Task 18.4. Compound triggers for mutating table.  
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(10, 'three');
        COMMIT;
    Then do next:
    1. For TAB1 create BEFORE trigger that will be raise error if we insert or update 
    new row with COL1 not between variables 'minv' = minimum of COL1 and 'maxv' = 
    maximum of COL1.
Solution:
    --1
    DROP TRIGGER TRG;
    CREATE OR REPLACE TRIGGER TRG
        FOR INSERT OR UPDATE 
        ON TAB1
    COMPOUND TRIGGER
        minv NUMBER;
        maxv NUMBER;
        --
        BEFORE STATEMENT IS
        BEGIN
            SELECT MIN(COL1), MAX(COL1) 
              INTO minv, maxv
                FROM TAB1;
        END BEFORE STATEMENT;
        --
        BEFORE EACH ROW IS
        BEGIN
            IF :NEW.COL1 NOT BETWEEN minv AND maxv THEN
                RAISE_APPLICATION_ERROR(-20000, 'Not between '||minv||' and '||maxv);
            END IF;
        END BEFORE EACH ROW;        
    END;
    --will work
    INSERT INTO TAB1 (COL1, COL2) VALUES(4, 'four');
    INSERT INTO TAB1 SELECT * FROM TAB1;
    COMMIT;
    --not will work
    INSERT INTO TAB1 (COL1, COL2) VALUES(11, 'eleven');
    COMMIT;

Task 18.5. Trigger for mutating table and cascade deleting.
    Do next operations:
        DROP TABLE TAB1;
        DROP TABLE CT;
        CREATE TABLE TAB1(COL1 NUMBER PRIMARY KEY,
                          COL2 VARCHAR2(100));
        CREATE TABLE CT (C1 NUMBER,
                         C2 VARCHAR2(100),
                         CONSTRAINT TT_FK FOREIGN KEY (C1)
                             REFERENCES TAB1(COL1) ON DELETE CASCADE);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(3, 'three');
        INSERT INTO CT(C1, C2) VALUES(1, '111');
        INSERT INTO CT(C1, C2) VALUES(2, '222');
        COMMIT;
    Then do next:
    1. Demonstrate mutating table for cascade deleting.
Solution:
    --1
    --Error of mutating table will be performed, when we create trigger BEFORE DELETE
    --FOR EACH ROW (or AFTER DELETE) on table CT, that will be read data from CT.
    CREATE OR REPLACE TRIGGER TRG
        BEFORE DELETE
        ON CT
        FOR EACH ROW
    DECLARE
        res NUMBER;
    BEGIN
        SELECT COUNT(*)
          INTO res
            FROM CT;
    END;
    --Then try to delete row 2 from TAB1
    DELETE TAB1
        WHERE COL1 = 2;

Task 18.6. Comparing triggers and procedures.
    1. How defined trigger and procedure?
    2. Which objects contain source code for triggers and procedures?
    3. How invoke trigger and procedure?
    4. What about DTL?
    5. Which type of triggers (DML, DDL) will be fired if remove all rows from a 
    table by TRUNCATE TABLE statement?
Solution:
    --1
    CREATE OR REPLACE TRIGGER
    CREATE OR REPLACE PROCEDURE
    --2
    SELECT * FROM USER_TRIGGERS;
    SELECT * FROM USER_SOURCE;
    --3
    Triggers are implicitly invoke by DML, DDL, database events.
    Procedures are explicitly invoke.
    --4
    COMMITSAVEPOINTROLLBACK not allowed in trigger (except autonomous triggers).
    COMMITSAVEPOINTROLLBACK allowed in procedure.
    --5
    DML triggers - not fired; DDL triggers - fired.

Task 18.7. Creating DDL triggers.
    Do next operations:
        DROP TABLE T_LOG;
        CREATE TABLE T_LOG
            (DDL_COMMAND  VARCHAR2(100),
             OBJECT_OWNER VARCHAR2(100),
             OBJECT_NAME  VARCHAR2(100),
             USERNAME     VARCHAR2(100),
             CREATE_DATE  VARCHAR2(100));
    Then do next:
    1. Demonstrate trigger that allow create objects between 9 and 18 hours. Create
    this trigger as not enabled.
    2. Demonstrate trigger that will be insert into table T_LOG info about last DDL 
    operation. What will be writes in T_LOG if DDL operation failed?
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG_S
        BEFORE CREATE 
        ON SCHEMA
        DISABLE
    BEGIN
        IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 9 AND 17 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Create time is 9-18');
        END IF;
    END;
    --2
    --if DDL fail, then row not will be inserted into T_LOG, because AFTER DDL
    CREATE OR REPLACE TRIGGER TRG_D
        AFTER DDL
        ON SCHEMA
    BEGIN
        INSERT INTO T_LOG 
            SELECT ORA_SYSEVENT,
                   ORA_DICT_OBJ_OWNER,
                   ORA_DICT_OBJ_NAME,
                   USER,
                   SYSDATE
                FROM DUAL;
    END;
    
Task 18.8. Creating system event triggers.             
    Do next operations:
        DROP TABLE T_LOG;
        CREATE TABLE T_LOG
            (EVENT_DATE VARCHAR2(100),
             EVENT_NAME VARCHAR2(100));
    Then do next:
    1. Which user can create system event trigger?
    2. Create trigger (indicate which use for schema also and only for database) that
    will be insert data about date of event and name of event into T_LOG when:
    - error raised;
    - user enter to the database;
    - user exit from database;
    - database is opened;
    - database is closed (for which closing?);
    - a role change occurs from standby to primary or from primary to standby.
Solution:
    --1
    SYS
    --2
    --Trigger and table must be created by SYS (for table can have privs to table), 
    --or user must have privs to create database triggers.    
    --ERROR RAISED.
    --Can be on database and schema.
    CREATE OR REPLACE TRIGGER TRG_E
        AFTER SERVERERROR
        ON DATABASE
    BEGIN
        INSERT INTO T_LOG
            VALUES (SYSDATE, ORA_SYSEVENT);
    END;
    --USER ENTER TO THE DATABASE.
    --Can be on database and schema.
    CREATE OR REPLACE TRIGGER TRG_UEN
        AFTER LOGON
        ON DATABASE
    BEGIN
        INSERT INTO T_LOG
            VALUES (SYSDATE, ORA_SYSEVENT);
    END;
    --USER EXIT FROM DATABASE.
    --Can be on database and schema.
    CREATE OR REPLACE TRIGGER TRG_UEX
        BEFORE LOGOFF
        ON DATABASE
    BEGIN
        INSERT INTO T_LOG
            VALUES (SYSDATE, ORA_SYSEVENT);
    END;
    --DATABASE IS OPENED. For SHUTDOWN or SHUTDOWN IMMEDIATE.
    --Can be on database only.
    CREATE OR REPLACE TRIGGER TRG_DO
        AFTER STARTUP
        ON DATABASE 
    BEGIN
        INSERT INTO T_LOG
            VALUES (SYSDATE, ORA_SYSEVENT);
    END;
    --DATABASE IS SHUT DOWN. For SHUTDOWN or SHUTDOWN IMMEDIATE.
    --Can be on database only.
    CREATE OR REPLACE TRIGGER TRG_DS
        BEFORE SHUTDOWN
        ON DATABASE 
    BEGIN
        INSERT INTO T_LOG
            VALUES (SYSDATE, ORA_SYSEVENT);
    END;
    --AFTER DB_ROLE_CHANGE
    --Can be on database only.
    CREATE OR REPLACE TRIGGER TRG_DD
        AFTER DB_ROLE_CHANGE
        ON DATABASE
    BEGIN
        INSERT INTO T_LOG
            VALUES (SYSDATE, ORA_SYSEVENT);
    END;

Task 18.9. Alternative execute procedure in trigger. Benefits of database-event 
    trigger.
    Do next operations:
        DROP TABLE TAB1 CASCADE CONSTRAINT;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(3, 'three');
        CREATE OR REPLACE PROCEDURE PRC(res IN NUMBERIS
        BEGIN
            DBMS_OUTPUT.PUT_LINE(res);
        END;
    Then do next:
    1. Create BEFORE INSERT on TAB1 trigger that will be execute procedure 'PRC' 
    directly with parameter = 1.
    2. List benefits of database-event trigger.
    3. What privileges are required to manage triggers:
    - for enables you to create a trigger in any schema;
    - enables you to create a trigger on DATABASE;
    - if your trigger refers to any objects that are not in your schema.
Solution:
    --1 Without ; at the end
    CREATE OR REPLACE TRIGGER TRG
        BEFORE INSERT 
        ON TAB1
        CALL PRC(1)
    --2
    Improved data security:
    - Provide enhanced and complex secutiry checks;
    - Provide enhanced and complex auditing.
    Improved data integrity:
    - Enforce dynamic data integrity constraints;
    - Enforce complex referential integrity constraints;
    - Ensure that related operations are performed together implicitly.
    --3
    CREATE/ALTER/DROP(ANYTRIGGER privilege
    --
    ADMINISTER DATABASE TRIGGER privilege
    --
    EXECUTE privilege


CHAPTER 19. "PLSQL Compiler"
Task 19.1. Compiler settings.
    1. What mean PLSQL_CODE_TYPE, PLSQL_OPTIMIZE_LEVEL, PLSQL_CCFLAGS, PLSQL_WARNINGS
    and explain each? Since which version this settings available?
    2. Write what privs must grant and where we can find info about this? Indicate 
    default values.
    3. What settings are recommended for fastest performance?
Solution:
    --1
    Initialization parameters for PL/SQL compilation.
    PLSQL_CODE_TYPE - specifies the compilation mode for PL/SQL library units.
    PLSQL_OPTIMIZE_LEVEL - specifies the optimization level to be used to compile
                           PL/SQL library units.
    PLSQL_CCFLAGS - controls conditional compilation of each PL/SQL library unit 
                    independently.
    PLSQL_WARNINGS - enables or disables the reporting of warning messages by the 
                     PL/SQL compiler.
    Since version 10g.
    --2
    --Logins as sysdba
    --ALTER SESSION SET CONTAINER=ORCLPDB;
    --then GRANT SELECT ON V_$PARAMETER to HR;
    SELECT NAME,
           VALUE
        FROM V$PARAMETER
            WHERE NAME IN ('plsql_code_type',       --default=INTERPRETED
                           'plsql_optimize_level',  --default=2
                           'plsql_ccflags',         --NULL
                           'plsql_warnings');       --DISABLE:ALL
    --3
    PLSQL_CODE_TYPE = NATIVE;
    PLSQL_OPTIMIZE_LEVEL = 2;

Task 19.2. PLSQL_CODE_TYPE
    Do next operations:
        CREATE OR REPLACE PROCEDURE PRC IS
            res NUMBER;
        BEGIN
            FOR i IN 1..100000000 LOOP
                res := res + 1;
            END LOOP;
        END;
    Then do next:
    1. List and explain values for PLSQL_CODE_TYPE.
    2. Switch session`s PLSQL_CODE_TYPE to non-default value, display parameter`s 
    value for procedure 'PRC'. How to make procedure work with new parameter`s value?
    How much faster will be executed procedure?
Solution:
    --1
    INTERPRETED - PL/SQL library units will be compiled to PL/SQL bytecode format.
    NATIVE - PL/SQL library units will be compiled to machine code.
    - Unit have value of parameter when it was compiled. After changing parameter 
    need recompile library unit.
    - If a PL/SQL library unit is compiled native, all subsequent automatic 
    recompilations of that unit will use native compilation.
    --2
    --switch session and after switch to NATIVE need recompile procedure
    ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; --can be only for DBA
    SELECT * 
        FROM USER_PLSQL_OBJECT_SETTINGS
            WHERE NAME = 'PRC';
    --after switched session just use 'compile'
    ALTER PROCEDURE PRC COMPILE;
    --or recompile procedure directly
    ALTER PROCEDURE PRC COMPILE PLSQL_CODE_TYPE=NATIVE;
    --near 2x faster

Task 19.3. PLSQL_OPTIMIZE_LEVEL
    Do next operations:
        DECLARE
            v_start DATE;
            a NUMBER;
            b NUMBER;
            v_end DATE;
        BEGIN
            v_start := SYSDATE;
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MM:SS'));
            FOR i IN 1..100000000 LOOP
                a := i + 1;
                b := a + i;
            END LOOP;
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MM:SS'));
            v_end := SYSDATE;
            DBMS_OUTPUT.PUT_LINE(ROUND((v_end - v_start)*24*60*60));
        END;
    Then do next:
    1. List and explain values for PLSQL_OPTIMIZE_LEVEL.
    2. For block use different PLSQL_OPTIMIZE_LEVEL.
Solution:
    --1
    0 - Maintains the evaluation order and hence the pattern of side effects, 
        exceptions, and package initializations of Oracle9i and earlier releases. 
        Also removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and 
        restores the earlier rules for the evaluation of integer expressions. 
        Although code will run somewhat faster than it did in Oracle9i, use of level 
        0 will forfeit most of the performance gains of PL/SQL in Oracle 10g.
    1 - Applies a wide range of optimizations to PL/SQL programs including the 
        elimination of unnecessary computations and exceptions, but generally does 
        not move source code out of its original source order.
    2 - include level 1 and may move source code relatively far from its original 
        location.
    3 - since version 11g. Include level 2 and enables procedure inlining, which is 
        an optimization process that replaces procedure calls with a copy of the 
        body of the procedure to be called.
    --2
    ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;
    ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
    ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;
    ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

Task 19.4. PLSQL_WARNINGS. ALTER.
    Do next operations:
        CREATE OR REPLACE PROCEDURE PRC (a OUT VARCHAR2IS
        BEGIN
            a := '1';
        END;
    Then do next:
    1. What mean and since version available PLSQL_WARNINGS, which prefix have?
    2. List and explain categories of warnings. Which categories we can disable?
    3. How to set warning levels?
    4. Demonstrate activating all warnings. Recompile 'PRC'. 
    After recompile see in dictionary info about PLSQL_WARNINGS for 'PRC'.
    Also find errors in dictionary after recompiling.
    5. How to activate: warnings from 2 different categories, one error from another 
    categorie. List numeric ranges of errors of all categories.
    6. Activate one informational numeric warning without errors, three performance 
    numeric warnings without errors. Which value will be in PLSQL_WARNINGS?
    7. How to disable another categories disabling only one category?
Solution:
    --1
    Since 10g, 11g, 12c integrated PL/SQL warnings with prefix PLW. It is mean that 
    subprograms can be compile: 
    - success with compilation warnings;
    - failure with compilation error and compilation warnings.
    --2
    --We can disable or enable any categories.
    - SEVERE. Messages for conditions that may cause unexpected behavior or wrong 
    results, such as aliasing problems with parameters.
    - PERFORMANCE. Messages for conditions that may cause performance problems, such
    as passing a VARCHAR2 value to a NUMBER column in an INSERT statement.
    - INFORMATIONAL. Messages for conditions that do not have an effect on 
    performance or correctness, but that you may want to change to make the code more
    maintainable, such as unreachable code that can never be executed.
    - ALL. All warnings.
    --3
    - Declaratively. Using the PLSQL_WARNINGS initialization parameter.
    - Programmatically. Using the DBMS_WARNING package.
    --4
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
    ALTER PROCEDURE PRC COMPILE;
    SELECT * 
        FROM USER_PLSQL_OBJECT_SETTINGS
            WHERE NAME = 'PRC'
    SELECT * 
        FROM USER_ERRORS
            WHERE NAME = 'PRC'
    --5
    --5000-5999 for severe, 6000-6249 for informational, 7000-7249 for performance
    ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:SEVERE',
                                       'ENABLE:PERFORMANCE',
                                       'ERROR:07001';--it is will treat as error for
                                                     --warning 7001, not only warning
    --6
    ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:6002,ENABLE:(7002, 7003, 7004)';
    --Result:
    --DISABLE:ALL, ENABLE:  6002, ENABLE:  7002, ENABLE:  7003, ENABLE:  7004
    SELECT * FROM v$parameter WHERE name = 'plsql_warnings'
    --7
    ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:INFORMATIONAL';
    
Task 19.5. PLSQL_WARNINGS. DBMS_WARNING.
    1. How to activate one category`s warnings? Using name of category display 
    status of this category.
    2. How to ignore one specific warning 5001? Display current status for this 
    warning.
    3. How to activate all categories for system (not session) using one procedure?
    Display current status of all this warnings.
    4. Display name of category using number of warning. What happen if we write 
    wrong number of warning?
Solution:
    --1
    BEGIN
        DBMS_WARNING.ADD_WARNING_SETTING_CAT('SEVERE','ENABLE','SESSION');
    END;
    SELECT DBMS_WARNING.GET_WARNING_SETTING_CAT('SEVERE') FROM DUAL;
    --2
    BEGIN
        DBMS_WARNING.ADD_WARNING_SETTING_NUM(5001,'DISABLE','SESSION');
    END;
    SELECT DBMS_WARNING.GET_WARNING_SETTING_NUM(5001) FROM DUAL;
    --3
    BEGIN
        DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL', 'SYSTEM');
    END;
    SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;
    --4
    --For wrong number of warning will be error "invalid warning category"
    SELECT DBMS_WARNING.GET_CATEGORY(5000) FROM DUAL;


CHAPTER 20. "Managing PLSQL code"
Task 20.1. Conditional compilation.
    1. Which benefits of using conditional compilation?
    2. List reserved preprocessor control tokens.
    3. What mean directive?
    4. Which packages use for conditional compilation?
    5. What cannot appear in a conditional compilation directive?
Solution:
    --1
    - Allows using new features in new versions of database in PLSQL code and 
    simultaneously allow use this code in older version without removing code.
    - Helps debugging or tracing in IDE and hide that functionality in the 
    application while it runs at a production site.
    --2
    $IF, $THEN, $ELSIF, $ELSE, $END, $$, $ERROR
    --3
    Directive is $$.
    --4
    DBMS_PREPROCESSOR, DBMS_DB_VERSION
    --5
    In anonymous block placeholders can`t appear in a conditional compilation
    directives.
    BEGIN
        :my := 1; --valid
        $IF ... $THEN
            :my := 1; --invalid
        $END
    END;
    
Task 20.2. DBMS_DB_VERSION. Using conditional compilation.
    1. Write block where display version and release of database. After, display 
    text 'v12' if version of database = 12.
    2. Create function 'FNC' returning year + IN parameter (number) that 
    will be use RESULT_CACHE if version of database >= 11.
    3. Write block where display in DBMS_OUTPUT values of PLSQL_CODE_TYPE,
    PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS.
    4. Create procedure 'PRC' that will be displayed text 'PRC compiled', but first
    verify PLSQL_OPTIMIZE_LEVEL and if this <> 3, show warning as error with text 
    'lvl3' when trying compiling.
Solution:
    --1
    BEGIN
        DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION);
        DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.RELEASE);
        IF DBMS_DB_VERSION.VER_LE_12   OR
           DBMS_DB_VERSION.VER_LE_12_1 OR 
           DBMS_DB_VERSION.VER_LE_12_2 THEN
            DBMS_OUTPUT.PUT_LINE('v12');
        END IF;
    END;
    --2
    CREATE OR REPLACE FUNCTION FNC (a NUMBERRETURN NUMBER
        $IF DBMS_DB_VERSION.VERSION >= 11 $THEN RESULT_CACHE $END
    IS
    BEGIN
        RETURN EXTRACT(YEAR FROM SYSDATE) + a;
    END;
    --3
    BEGIN
        DBMS_OUTPUT.PUT_LINE($$PLSQL_CODE_TYPE);
        DBMS_OUTPUT.PUT_LINE($$PLSQL_OPTIMIZE_LEVEL);
        DBMS_OUTPUT.PUT_LINE($$PLSQL_WARNINGS);
    END;
    --4
    CREATE OR REPLACE PROCEDURE PRC IS
    BEGIN
        $IF $$PLSQL_OPTIMIZE_LEVEL <> 3 $THEN
            $ERROR 'lvl3' $END
        $END
        DBMS_OUTPUT.PUT_LINE('PRC compiled');
    END;

Task 20.3. PLSQL_CCFLAGS
    1. What mean PLSQL_CCFLAGS?
    2. Which priority have PLSQL_CCFLAGS over another parameters? Which priority 
    inside PLSQL_CCFLAGS?
    3. Assign to PLSQL_CCFLAGS my_val=100. Explain it. Further create function 'FNC'
    (using conditional compilation) that if value of my_val = 100, then return value
    of my_val, otherwise return value of PLSQL_CCFLAGS.
    How to see value of PLSQL_CCFLAGS from function?
    4. Assign to PLSQL_CCFLAGS directives: first for PLSQL_CCFLAGS, second with 
    BOOLEAN value, third directive with same name of second and number value. Check 
    PLSQL_CCFLAGS and also check directives and if have second directive then display
    text '1', for second directive - '2'.
    5. Display in DBMS_OUTPUT value of unsigned directive, for example with name 
    'aaBB'. What result will be? Why block completed without warnings?
Solution:
    --1
    PLSQL_CCFLAGS contain user-defined inquiry directives.
    --2
    High priority, if we use $$ it is will be run before other parameters. Inside 
    PLSQL_CCFLAGS - priority is right to left.
    --3
    --PLSQL_CCFLAGS = '<v1>:<c1>,<v2>:<c2>,...,<vn>:<cn>'
    --<v1> is unquoted PL/SQL identifier and can be a reserved word or a keyword.
    --<c1> is one of the following: BOOLEANPLS_INTEGER or NULL.
    ALTER SESSION SET PLSQL_CCFLAGS = 'my_val:100';
    --After this we can display value from my_val and PLSQL_CCFLAGS.
    --Value from my_val will be loading from PLSQL_CCFLAGS.
    BEGIN
        DBMS_OUTPUT.PUT_LINE($$my_val);
        DBMS_OUTPUT.PUT_LINE($$PLSQL_CCFLAGS);
    END;
    --! need recompile if we make new ALTER SESSION SET PLSQL_CCFLAGS = 'my_val:...';
    CREATE OR REPLACE FUNCTION FNC RETURN VARCHAR2 IS
        res VARCHAR2(100);
    BEGIN
        res :=  $IF $$my_val = 100 $THEN
                    $$my_val
                $ELSE $$PLSQL_CCFLAGS
                $END;
        RETURN res;
    END;
    SELECT FNC FROM DUAL;
    --
    SELECT * FROM USER_PLSQL_OBJECT_SETTINGS
        WHERE NAME = 'FNC';
    --4
    ALTER SESSION SET PLSQL_CCFLAGS = 'PLSQL_CCFLAGS:true,a:true,a:1';
    BEGIN
        IF $$PLSQL_CCFLAGS THEN
            DBMS_OUTPUT.PUT_LINE('PLSQL_CCFLAGS have value');
        ELSIF $$a=1 THEN --directives will be checked right to left, can`t check BOOL
            DBMS_OUTPUT.PUT_LINE('2');
        END IF;
    END;
    --5
    --result will be NULL
    --completed without warnings, because warnings raised only in subprograms
    BEGIN
        DBMS_OUTPUT.PUT_LINE($$aaBB);
    END;

Task 20.4. PLSQL_CCFLAGS, example for testing procedure. DBMS_PREPROCESSOR. 
    Predefined inquiry directives.
    1. Using PLSQL_CCFLAGS create procedure that will be checked one parameter from 
    PLSQL_CCFLAGS and will be display time of starting, finishig procedure and also
    make loop between 1 and variable 'v' with default value = 100000000 from 
    PLSQL_CCFLAGS.
    If you assign new parameter to PLSQL_CCFLAGS how to recompile procedure using
    old PLSQL_CCFLAGS values in procedure?
    2. Demonstrate DBMS_PREPROCESSOR. Explain it.
    3. Write block with nested block with label 'my' where write DBMS_OUTPUT with 
    directives and explain result:
    - number of the source line;
    - name of the current PL/SQL block;
    - owner of current PL/SQL unit;
    - type of the current PL/SQL unit;
Solution:
    --1
    ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true,val:100000000';
    CREATE OR REPLACE PROCEDURE PRC IS
        v NUMBER := $$val;
    BEGIN
        IF $$debug THEN
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'HH24:MI:SS'));
        END IF;
        --
        FOR i IN 1..v LOOP --or FOR i IN 1..$$val LOOP
            v := i;
        END LOOP;
        --
        IF $$debug THEN
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'HH24:MI:SS'));
        END IF;
    END;
    EXEC PRC;
    --even if assigns 'debug:false', procedure would have 'debug:true'
    ALTER SESSION SET PLSQL_CCFLAGS = 'debug:false,val:100000000';
    ALTER PROCEDURE PRC COMPILE REUSE SETTINGS;
    EXEC PRC;
    --2
    --It will printing in DBMS_OUTPUT code of procedure with actual values for 
    --directives.
    CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
        ('PROCEDURE','HR','PRC');
    --3
    BEGIN
        BEGIN <<my>>
        DBMS_OUTPUT.PUT_LINE($$PLSQL_LINE); --3
        DBMS_OUTPUT.PUT_LINE($$PLSQL_UNIT); --NULL. Retrieve name of head block (not
                                            --nested block or nested subprogram from
                                            --declare section)
        DBMS_OUTPUT.PUT_LINE($$PLSQL_UNIT_OWNER); --NULL
        DBMS_OUTPUT.PUT_LINE($$PLSQL_UNIT_TYPE); --ANONYMOUS BLOCK
                                                 --retrieve type of head PL/SQL block
                                                 --(not nested block or nested
                                                 --subprogram from declare section)
        END my;
    END;

Task 20.5. Obfuscation.
    1. What mean obfuscation?
    2. How to make obfuscation?
    3. List benefits of obfuscation.
Solution:
    --1
    Obfuscation (wrapping), of a PL/SQL unit is the process of hiding the PL/SQL 
    source code.
    --2
    Wrapping can be done with the WRAP utility and DBMS_DDL subprograms.
    --3
    - Others can`t see your source code.
    - Your source code is not visible through the USER_SOURCE, ALL_SOURCE, or 
      DBA_SOURCE data dictionary views.
    - SQL*Plus can process the obfuscated source files.
    - The Import and Export utilities accept wrapped files. You can back up or move 
      wrapped procedures.

Task 20.6. DBMS_DDL.
    Do next operations:
        CREATE OR REPLACE PROCEDURE PRC IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('my');
        END;
    Then do next:
    1. Remake procedure 'PRC' using wrapping from DBMS_DDL (two ways). Explain 
    result. How to restore normal code?    
Solution:
    --1
    --procedure
    DBMS_DDL.CREATE_WRAPPED
    --function
    DBMS_DDL.WRAP
    --For restoring need recompile original procedure again.
    --First way
    BEGIN
        DBMS_DDL.CREATE_WRAPPED
            (
            'CREATE OR REPLACE PROCEDURE PRC IS
            BEGIN
                DBMS_OUTPUT.PUT_LINE(''my'');
            END;'
            );
    END;
    --Source code will be replaced to text 'PROCEDURE PRC wrapped ...'    
    SELECT *
        FROM USER_SOURCE 
            WHERE NAME = 'PRC';    
    --Second way
    --Code from res we must compile manually and then procedure will be wrapped.
    DECLARE
        v VARCHAR2(4000) :='CREATE OR REPLACE PROCEDURE PRC IS
                            BEGIN
                                DBMS_OUTPUT.PUT_LINE(''my'');
                            END;';
        res VARCHAR2(4000);
    BEGIN
        res := DBMS_DDL.WRAP(v);
        DBMS_OUTPUT.PUT_LINE(res);
    END;

Task 20.7. Wrapper utility.
    Do next operations:
        CREATE TABLE TAB1(COL1 NUMBER,
                          COL2 VARCHAR2(100));
        CREATE OR REPLACE PROCEDURE PRC IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('my');
        END;
    Then do next:
    1. With which objects WRAP working?
    2. Which argument is mandatory? Which argument is optional?
    3. Which default extension for input and output files?
    4. Where spaces not allowed?
    5. Demonstrate WRAP for table TAB1 and procedure 'PRC' using all mandatory and 
    optional arguments. Explain result, which objects will be wraped?
    6. Which errors wrapper detects?
    7. Which part of package can wrap?
    8. Indicate YES or NO for DBMS_DDL and WRAP utility for next functionality:
    - Code obfuscation;
    - Dynamic obfuscation;
    - Obfuscate multiple programs at a time.
Solution:
    --1
    TYPE BODYPROCEDUREFUNCTIONPACKAGE BODY
    --2
    --mandatory
    INAME
    --optional
    ONAME
    --3
    input is .sql
    output is .plb
    --4
    Spaces not allowed around equal sign.
    --5
    --Save code of procedure to file 'prc.sql' on disk D
    --Open CMD Windows, switch to disk D by command: 
    CD /D D:\
    --Then in CMD run: 
    WRAP INAME=PRC.SQL ONAME=PRC_RES.PLB
    --Then in SQL Developer make:
    DROP TABLE TAB1;
    DROP PROCEDURE PRC;
    --Then in SQL Developer run command: 
    @d:\PRC_RES.PLB
    --Check code
    --In file PRC_RES.PLB table not will be wrapped, procedure will be (in database 
    --too).
    SELECT * FROM USER_SOURCE WHERE NAME = 'PRC';
    --6
    Wrapping does not detect syntax or semantic errors (for example, nonexistent 
    tables or views).
    Wrapping detects only tokenization errors (for example, runaway strings).
    Syntax or semantic errors are detected during PL/SQL compilation or when 
    executing the output file in SQL*Plus.
    --7
    Must wrap only the package body, not the package spec.
    --8
    DBMS_DDL and WRAP utility
    - Code obfuscation: YES YES
    - Dynamic obfuscation: YES NO
    - Obfuscate multiple programs at a time: NO YES.

Task 20.8. ACCESSIBLE BY clause.
    1. What do ACCESSIBLE BY?
    2. For which SQL statements can use ACCESSIBLE BY?
    3. What part of ACCESSIBLE BY clause is optional?
    4. What happen after compiling if entity named in an accessor is not exists?
    5. Where ACCESSIBLE BY clause not will be working?
Solution:
    --1
    The ACCESSIBLE BY clause limits access to a unit or subprogram by other units,
    listed at an accessor (white) list.
    --2
    ALTER TYPE
    CREATE TYPE
    CREATE TYPE BODY
    CREATE FUNCTION
    CREATE PROCEDURE
    CREATE PACKAGE
    --3
    The unit_kind specifies a FUNCTIONPACKAGEPROCEDURETRIGGERTYPE.
    --4
    Success. Entity named in an accessor (white) list is not required to exist.
    --5
    - The check will fail if the access is through static SQL, DBMS_SQL, dynamic SQL.
    - Any PLSQL unit of a package specification or package body will be checked 
    against the accessor list of the package specification.
    - RPC calls to a protected subprogram will always fail.
    - Calls to a protected subprogram from a conditional compilation directive will 
    fail.

Task 20.9. ACCESSIBLE BY clause. Part 2.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER);
        --
        CREATE OR REPLACE PROCEDURE PRC IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PRC');
        END;
        --
        CREATE OR REPLACE PROCEDURE PT IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PT_procedure');
            PRC;
        END;
        --
        CREATE OR REPLACE TRIGGER PT
            BEFORE INSERT
            ON TAB1
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PT_trigger');
            PRC;
        END;
    Then do next:
    1. Remake procedure PRC that will be accessible by procedure and trigger PT.
    2. Remake procedure PRC that will be accessible by procedure PT and trigger
    PT only from schema HR. Also demonstrate how much accessors with same name can 
    write.
    3. Make package 'PCK' for procedure PRC and this procedure must be accessible 
    only by procedure PT. 3 ways.
    4. Make package 'PCK' for procedure PRC and procedure PRC2 for demonstrating 
    what will be if in header of package spec define accessors that did not match 
    with accessors inside PRC in package spec and package body when we will use 
    procedure PT where call procedure PCK.PRC?
Solution:
    --1
    CREATE OR REPLACE PROCEDURE PRC ACCESSIBLE BY (PT) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('PRC');
    END;
    EXEC PT;
    --2
    CREATE OR REPLACE PROCEDURE PRC ACCESSIBLE BY (TRIGGER HR.PT, 
                                                   PROCEDURE HR.PT,
                                                   TRIGGER HR.PT, 
                                                   PROCEDURE HR.PT) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('PRC');
    END;
    EXEC PT; --in DBMS_OUTPUT: PT_procedure, PRC
    INSERT INTO TAB1 VALUES (1);
    --3
    --1st way
    CREATE OR REPLACE PACKAGE PCK ACCESSIBLE BY (PROCEDURE PT) IS
        PROCEDURE PRC;
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PRC IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PCK_package PRC');
        END;
    END;
    --2d way
    CREATE OR REPLACE PACKAGE PCK IS 
    --for 3rd way: CREATE OR REPLACE PACKAGE PCK ACCESSIBLE BY (PROCEDURE PT) IS
        PROCEDURE PRC ACCESSIBLE BY (PROCEDURE PT);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PRC ACCESSIBLE BY (PROCEDURE PT) IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PCK_package PRC');
        END;
    END;
    --
    CREATE OR REPLACE PROCEDURE PT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('PT_procedure');
        PCK.PRC;
    END;
    --4
    --Trigger and procedure PT will be compiled with errors and will not work,
    --but if we call only PCK.PRC2, then will be success
    CREATE OR REPLACE PACKAGE PCK ACCESSIBLE BY (PROCEDURE PT) IS
        PROCEDURE PRC ACCESSIBLE BY (TRIGGER PT);
        PROCEDURE PRC2;
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PRC ACCESSIBLE BY (TRIGGER PT) IS
            BEGIN DBMS_OUTPUT.PUT_LINE('PCK_package PRC'); END;
        PROCEDURE PRC2 IS
            BEGIN DBMS_OUTPUT.PUT_LINE('PCK_package PRC'); END;        
    END;
    CREATE OR REPLACE TRIGGER PT
        BEFORE INSERT
        ON TAB1
    BEGIN
        DBMS_OUTPUT.PUT_LINE('PT_trigger');
        PCK.PRC;
    END;
    CREATE OR REPLACE PROCEDURE PT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('PT_procedure');
        PCK.PRC;
        --PCK.PRC2; --will success, but need delete PCK.PRC from procedure PT
    END;

Task 20.10. Code-based access control: granting roles to program units.
    1. Demonstrate granting roles to procedure.
Solution:
    --1
    --Granting roles to procedure allows another schema execute this procedure with 
    --invoker rights without having privs to all underlying objects: tables, views...
    --But owner must have this role too.
    --Make this code under HR schema:
    DROP TABLE TAB_HR;
    CREATE TABLE TAB_HR(COL1 NUMBER);
    --
    CREATE OR REPLACE PROCEDURE PRC AUTHID CURRENT_USER IS
        res NUMBER;
    BEGIN
        SELECT COUNT(*)
          INTO res
            FROM HR.TAB_HR; --!!! need write schema
        DBMS_OUTPUT.PUT_LINE(res);
    END;
    --login as SYS
    LOGIN SYS AS SYSDBA;
    ALTER SESSION SET CONTAINER=ORCLPDB;
        CREATE USER HR2 IDENTIFIED BY 123;
        GRANT CREATE SESSION TO HR2;
    CREATE ROLE ROLE_HR;
    GRANT SELECT ON HR.TAB_HR TO ROLE_HR;
    GRANT ROLE_HR TO HR;
    GRANT ROLE_HR TO PROCEDURE HR.PRC; --need GRANT again, if recompiled HR.PRC
        GRANT EXECUTE ON HR.PRC TO HR2;
    --then connect to HR2
    EXEC HR.PRC;
    SELECT * FROM HR.TAB_HR
    
Task 20.11. Mark code as deprecated.
    Do next operations:
        DROP PACKAGE PCK;
        DROP PROCEDURE PRC;
            CREATE OR REPLACE PACKAGE PCK IS
                PROCEDURE PRC;
                v NUMBER := 5;
            END;
            CREATE OR REPLACE PACKAGE BODY PCK IS
                PROCEDURE PRC IS
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('PRC');
                END PRC;
            END;
            CREATE OR REPLACE PROCEDURE PROC IS
            BEGIN
                PCK.PRC;
                DBMS_OUTPUT.PUT_LINE(PCK.V);
            END;
    Then do next:
    1. What do pragma for deprecate, what happen if we use this pragma.
    2. For which PLSQL elements we can use it?
    3. Where we can use it?
    4. Remake:
    - make all package`s elements deprecated (2 ways);
    - make PROC deprecated;
    5. Explain deprecation of an overloaded procedure.
    6. When the pragma has no effect? Write example.
Solution:
    --1
    The DEPRECATE pragma marks a PL/SQL element as deprecated. The compiler issues 
    warnings when we compile PLSQL unit that references to deprecated PLSQL unit.
    --2
    Subprograms, packages, variables, constants, types, subtypes, exceptions, cursors
    --3
    The DEPRECATE pragma may only appear in the declaration section of a PL/SQL item.
    It must appear immediately after the declaration of an item to be deprecated.
    The DEPRECATE pragma must appears in the declaration sections of a package 
    specification, an object specification, a top level procedure, or a top level 
    function immediately after the keyword IS or AS.    
    --4
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
    --only package spec can have PRAGMA DEPRECATE
    CREATE OR REPLACE PACKAGE PCK IS
        PRAGMA DEPRECATE(pck,'Pck deprecated');
        PROCEDURE PRC;
        v NUMBER := 5;
    END;
    EXEC PROC;
    --second way
    CREATE OR REPLACE PACKAGE PCK IS        
        PROCEDURE PRC;
        PRAGMA DEPRECATE(PRC,'PCK.PRC deprecated');
        v NUMBER := 5;
        PRAGMA DEPRECATE(v,'PCK.v deprecated');
    END;
    --
    CREATE OR REPLACE PROCEDURE PROC IS
        PRAGMA DEPRECATE(PROC, 'PROC deprecated');
    BEGIN
        PCK.PRC;
        DBMS_OUTPUT.PUT_LINE(PCK.V);
    END;
    --5
    Will be deprecated only procedures with parameters that have PRAGMA DEPRECATE
    after the declaration
    --6
    --If an identifier is applied with a mismatched name, for example:
    CREATE OR REPLACE PACKAGE PCK IS
        PRAGMA DEPRECATE("pck",'Pck NOT deprecated'); --because correct name is "PCK"
        PROCEDURE PRC;
        v NUMBER := 5;
        PRAGMA DEPRECATE(prc,'PRC NOT deprecated'); --because must write after PRC
    END;    
    
    
CHAPTER 21. "Dependencies"
Task 21.1. Dependencies.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
        --
        CREATE OR REPLACE VIEW VW AS
            SELECT COL1, COL2 FROM TAB1;
        --
        CREATE OR REPLACE PROCEDURE PRC IS
            res NUMBER;
        BEGIN
            SELECT COUNT(*)
              INTO res
                FROM VW;
            DBMS_OUTPUT.PUT_LINE(res);
        END;
    Then do next:
    1. For 'TAB1', 'VW' and 'PRC' indicate:
    - direct dependencies;
    - undirect dependencies;
    2. For 'TAB1' and 'VW' indicate:
    - dependent object;
    - referenced object.
    3. Which status will have 'VW' if we will drop 'TAB1'?
    4. Inidicate dependent or referenced for: PACKAGE BODYPACKAGE SPEC, SEQUENCE,
    SUBPROGRAMS, SYNONYMTABLETRIGGER, USER_DEFINED object, USER_DEFINED 
    collection, VIEW.
    5. Explain status of 'VW' if we:
    - add new column in TAB1;
    - drop column COL1 from TAB1.
    6. What mean local dependencies and remote dependencies?
    7. Find direct dependencies for 'TAB1'.
    8. Explain how display direct and indirect dependencies for 'TAB1'.
Solution:
    --1
    Direct dependence between view and table.
    Undirect dependence between procedure and table.
    --2
    TAB1 is referenced. VW is dependent.
    --3
    Invalid.
    --4
    PACKAGE BODY - dependent only. SEQUENCE - referenced only. Other objects - both.
    --5
    --If changed objects in referenced object not listed on dependent object, that 
    --dependent object will be VALID.
    Starting 11g, status of view will be VALID if we add new column, because this
    column not list in view.
    --
    If we drop COL1 that listed in VIEW, then VIEW will be invalid.
    --6
    If all objects in same database it is mean local dependencies.
    If referenced object in remote database then it is remote dependencies.
    --7
    SELECT *
        FROM USER_DEPENDENCIES
            WHERE REFERENCED_NAME = 'TAB1'
    --8
    --Run 'utldtree.sql' from $ORACLE_HOME/rdbms/admin
    --copy to disk D and run in SQL DEVELOPER. Result of created objects can find in
    --SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME LIKE '%TREE%'
    @d:\utldtree.sql
    --!!! then execute procedure without ;
    EXECUTE DEPTREE_FILL(
                        'TABLE', --object_type
                        'HR',    --object_owner
                        'TAB1'   --object_name
                        )
    --further we can write query
    SELECT NESTED_LEVEL,
           TYPE,
           NAME
        FROM DEPTREE
            ORDER BY SEQ#;
    
Task 21.2. Dependencies. Part 2.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
        --
        CREATE OR REPLACE VIEW VW AS
            SELECT COL1, COL2 FROM TAB1;
        --
        CREATE OR REPLACE VIEW VW2 AS
            SELECT COL1, COL2 FROM VW;
        --
        CREATE OR REPLACE PROCEDURE PRC IS
            res NUMBER;
        BEGIN
            SELECT COUNT(*)
              INTO res
                FROM VW2;
            DBMS_OUTPUT.PUT_LINE(res);
        END;
    Then do next:
    1. List and explain object`s status for USER_OBJECTS, DBA_OBJECTS, ALL_OBJECTS.
    Which statuses can have TABLE?
    2. Explain which status can be for 'PRC' if 'VW' will be INVALID. How to 
    recompile objects 'PRC', explain.
    3. Write status for VIEWFUNCTION and PROCEDURE if we add column in referenced
    table and if we alter column in referenced table.
Solution:
    --1
    --For USER_OBJECTS available only VALID, INVALID.
    --Table can be only VALID.
    VALID - the user object succesfully compiled.
    COMPILED WITH ERRORS - compiling produced errors.
    INVALID - for objects changed references (only dependent object can be invalid).
    UNAUTHORIZED - access priv on a referenced object was revoked (only dependent 
                   object can be unauthorized).
    --2
    'PRC' will be INVALID too. All direct dependent and indirect dependent objects 
    will be INVALID.
    EXECUTE PRC; --this mean that server try compile procedure and recompile 
                 --everything inside this code. May be will success, may be not.
    --3
    --may be VALID if recompiling when calling
                ADD COLUMN          ALTER COLUMN
    VIEW        valid               may valid or not
    FUNCTION    invalid             may valid or not
    PROCEDURE   may valid or not    may valid or not
    
Task 21.3. Packages and dependencies.
    Do next operations:
        CREATE OR REPLACE PACKAGE PCK IS
            PROCEDURE PRC;
        END;
        CREATE OR REPLACE PACKAGE BODY PCK IS
            PROCEDURE PRC IS
            BEGIN
                DBMS_OUTPUT.PUT_LINE('PRC');
            END;           
        END;
        CREATE OR REPLACE PROCEDURE PROC IS
        BEGIN
            PCK.PRC;
        END;
    Then do next:
    1. Write status for 'PROC' if we add new procedure to 'PCK'.
Solution:
    --1
    VALID

Task 21.4. Synonyms and dependencies.
    Do next operations:
        DROP TABLE TAB1;
        DROP TABLE TAB2;
            CREATE TABLE TAB1(COL1 NUMBER, COL2 VARCHAR2(100));
            INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
                CREATE TABLE TAB2(COL1 NUMBER);
                INSERT INTO TAB2(COL1) VALUES(2);
                    CREATE OR REPLACE SYNONYM SYN FOR TAB1;
                        CREATE OR REPLACE VIEW VW AS SELECT * FROM SYN;
    Then do next:
    1. Explain status for VW if we recreate SYN for another table TAB2.
    2. List circumstances when recreating synonym not make INVALID dependent non-VIEW
    objects and VIEW.
    3. Can we create synonyms for remote schema objects?
Solution:
    --1
    INVALID
    --2
    --Non-VIEW
    - The column order, column names, and column data types of the tables need to be
      identical;
    - The privileges on the newly referenced table and its columns are a superset of
      the set of privileges on the original table. These privileges must not be 
      derived through roles alone;
    - The names and types of partitions and sub-partitions need to be identical;
    - The tables are of the same organization type.
    - Object type columns are of the exact same type.
    --VIEW
    --all the above and next:
    - Columns and order of columns defined for primary key and unique indexes, NOT
      NULL constraints, primary key and unique constraints must be identical.
    - The dependent view cannot have any referential constraints.
    --3
    Yes, but cannot create synonyms for objects declared in PL/SQL subprograms or
    packages.
    
Task 21.5. Recommendations to reducing invalidation.
    1. List recommendations to reducing invalidation.
    2. When validations occurs automatically?
    3. Suppose we created package 'PCK' with a procedure that works 10 minutes.
    Then in the first session we run this procedure. After 5 seconds in second session
    we change this package and try to compile. What will happen in first session and 
    in second?
Solution:
    --1
    - Add new items to the end of the package;
    - Reference each table through a view
    --2
    When object is referenced, it is mean when you try to call object.
    --3
    In first session procedure will be working 10 minutes. Second session will wait 
    end of procedure works and after that compile package.

Task 21.6. Remote dependencies. Create remote database and link.
    1. For 12c version create new plugable database with: name = 'NEW_DB', user 
    name = 'MY_USER', password for user = 'MY_PASS'.
    2. Display OPEN_MODE in SQLplus for NEW_DB.
    3. After point 1 create user U1 with password 123 and grant to him necessary
    privs.
    4. After point 2 which file must correct for make connection in SQL_DEVELOPER.    
    5. Connect to NEW_DB via SQL_PLUS. 
    6. Create database link 'MY_LINK' for user from new database NEW_DB and make 
    SELECT * FROM DUAL.
    7. How to find all links?
Solution:
    --1
    -- It is for 12c.
    -- Use Database Configuration Assistant (DBCA). 
    -- Further choose MANAGE PLUGGABLE DATABASES. 
    -- Further choose CREATE A PLUGGABLE DATABASE. Select container database.
    -- Further choose CREATE A NEW PLUGGABLE DATABASE FROM PDB SEED.
    -- Write database name = NEW_DB, username = MY_USER, password for user = MY_PASS.
    -- Location of our new database: C:\APP\MYU\ORADATA\ORCL\{PDB_NAME}. Push NEXT.
    --2
    --open SQLPLUS
    --connect to SYS AS SYSDBA
    SELECT NAME, OPEN_MODE FROM V$PDBS;
    --3
    ALTER SESSION SET CONTAINER=NEW_DB;
    CREATE USER U1
        IDENTIFIED BY "123";
    GRANT CREATE SESSION TO U1;
    GRANT CONNECT TO U1;
    GRANT CREATE TABLE TO U1;
    GRANT CREATE PROCEDURE TO U1;
    GRANT CREATE VIEW TO U1;
    GRANT UNLIMITED TABLESPACE TO U1;
    --4
    --Correct TNSNAMES.ORA. Just copy any pluggable database and rename name and
    --SERVICE_NAME to NEW_DB.
    --5
    --In SQLplus
    CONN
    U1/123@NEW_DB;
    --6
    --Make operations as SYS and also need switch to old pluggable database
    --(for example ORCLPDB)
    SYS AS SYSDBA;
    ALTER SESSION SET CONTAINER=ORCLPDB;
    --DROP PUBLIC DATABASE LINK MY_LINK;
    CREATE PUBLIC DATABASE LINK MY_LINK 
        CONNECT TO U1 
            IDENTIFIED BY "123"
                USING 'NEW_DB';
    --
    SELECT * FROM DUAL@MY_LINK;
    --7
    SELECT * FROM ALL_DB_LINKS

Task 21.7. Remote dependencies.
    Do next operations:
        --IN FIRST SCHEMA
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(5)     DEFAULT 0, 
                          COL2 VARCHAR2(100) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        COMMIT;
        --
        CREATE OR REPLACE VIEW VW AS
            SELECT COL1, COL2 FROM TAB1;
        --
        CREATE OR REPLACE PROCEDURE PRC IS
            res NUMBER;
        BEGIN
            SELECT COUNT(*)
              INTO res
                FROM VW;
            DBMS_OUTPUT.PUT_LINE(res);
        END;
        --IN SECOND SCHEMA
        CREATE OR REPLACE PROCEDURE PRC2 IS
        BEGIN
            PRC@MY_LINK;
            DBMS_OUTPUT.PUT_LINE('remote PRC success');
        END;
    Then do next:
    1. If we modify column`s type for TAB1 what status will have objects: VW, PRC, 
    PRC2. What happening if we try execute PRC2?
    2. How to check dependencies mode in dictionary?
    3. Explain two dependencies modes for remote objects, indicate default. How to
    switch to non-default mode?
Solution:
    --1
    VW - INVALID. PRC - INVALID. PRC2 - VALID. 
    First execute will validate VW and PRC and returns error for PRC2.
    Second execute will validate PRC2 and will be success.
    --2
    Oracle try to check status of remote object in remote USER_OBJECTS.
    SELECT * 
        FROM v$parameter
            WHERE NAME='remote_dependencies_mode'
    --3
    --default
    Oracle check timestamp of compilation of remote OBJECT and timestamp of
    compilation of local procedure. If compilation time of local procedure older then
    remote OBJECT - it is will be valid, otherwise - NOT VALID.
    --
    Comparing signatures between dependent and referenced objects. The signature of 
    procedure is: 
    - the name of the procedure;
    - the quantity of parameters;
    - the data types of the parameters;
    - the modes of the parameters;
    - the datatype of the return value for a function.
    The signature of the remote procedure is saved in the local procedure.
    --Switch to signature.
    --Then need recreate procedure PRC2
    ALTER SESSION SET REMOTE_DEPENDENCIES_MODE='SIGNATURE';
    --Then if we just recompile PRC (without changes) after compile PRC2, then 
    --executing PRC2 not raise error. But if we add for example new parameter to PRC 
    --and recompile again, then executing PRC2 will raise error and status PRC2 will
    --be INVALID.

Task 21.8. Notices about recompilation.
    1. When recompiling dependent procedures and functions is unsuccessful?
    2. When recompiling dependent procedures and functions is successful?
    3. How to minimize dependency failures for procedures?
Solution:
    --1
    - The referenced objects is dropped or renamed;
    - The data type of the referenced column is changed;
    - The referenced column is dropped;
    - A referenced view is replaced by a view with different columns;
    - The parameter list of a referenced procedure is modified.
    --2
    - The referenced table has new columns;
    - The data type of referenced columns has not changed;
    - A private table is dropped, but a public table that has the same name and 
    structure exists;
    - The PL/SQL body of a referenced procedure has been modified and recompiled 
    successfully.
    --3
    - Declaring records with the %ROWTYPE attribute;
    - Declaring variables with the %TYPE attribute;
    - Querying with the SELECT * notation;
    - Including a columns list with INSERT statements.
    
    
CHAPTER 22. "Another features"
Task 22.1. Using DBMS_ERRLOG
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER(1)   DEFAULT 0, 
                          COL2 VARCHAR2(3) NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        COMMIT;
    Then do next:
    1. Insert into TAB1 values and use DBMS_ERRLOG:
        2  'two'
        11 'eleven'
        12 'twelve'
Solution:
    --1
    EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('TAB1');
    DESC err$_TAB1
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE err$_TAB1'; --clear table
        INSERT INTO TAB1(COL1, COL2)
            SELECT 2, 'two' FROM DUAL
            UNION ALL
            SELECT 11, 'eleven' FROM DUAL
            UNION ALL
            SELECT 12, 'twelve' FROM DUAL
                LOG ERRORS REJECT LIMIT UNLIMITED;
        --COMMIT;
    END;
    SELECT * FROM err$_TAB1

Task 22.2. Another features. Part 1.
    1. Create recursive function.
    2. Create function that split a string 'a;b;c;d' into elements and return varray.
    Function must have two arguments: 'p_str' for string, 'p_d' for delimiter. Return
    type is max 32767 elements.
Solution:
    --1
    CREATE OR REPLACE FUNCTION FNC_RECURS(pin NATURALNRETURN NUMBER IS
    BEGIN
        IF pin = 0 THEN
            RETURN 1;
        ELSE 
            RETURN pin * FNC_RECURS(pin - 1);
        END IF;
    END;
    --2
    CREATE OR REPLACE FUNCTION FNC_SPLIT(p_str VARCHAR2 DEFAULT NULL
                                         p_d   VARCHAR2 DEFAULT NULL)
                                                    RETURN SYS.ODCIVARCHAR2LIST IS
        v_str VARCHAR2(32767)      := p_str || p_d;
        v_arr SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
    BEGIN
        WHILE INSTR(v_str, p_d) > 1 LOOP
            v_arr.EXTEND;
            v_arr(v_arr.LAST) := SUBSTR(v_str, 1, INSTR(v_str, p_d) - 1);
            v_str := SUBSTR(v_str, INSTR(v_str, p_d) + 1);
        END LOOP;
        RETURN v_arr;
    END;