Training to exam Oracle Database 19c: Program with PL/SQL 1Z0-149 (EN)

CHAPTER 1. "Introduction in PL/SQL"
CHAPTER 2. "Overview of PL/SQL"
CHAPTER 3. "Declaring PLSQL Variables"
CHAPTER 4. "Writing executable statements"
CHAPTER 5. "Interacting with Oracle DB Server"
CHAPTER 6. "Writing Control structure"
CHAPTER 7. "Working with Composite Data Types"
CHAPTER 8. "Using explicit cursors"
CHAPTER 9. "Handling Exceptions"
CHAPTER 10. "Creating Procedure"
CHAPTER 11. "Creating Function"
CHAPTER 12. "Packages"
CHAPTER 13. "Working with Packages"
CHAPTER 14. "Using oracle-supplied Packages"
CHAPTER 15. "Dynamic SQL"
CHAPTER 16. "Advanced features in PL/SQL"
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 1. "Introduction in PL/SQL"
Task 1.1.
    1. What is PL/SQL.
Solution:
    --1
    PL/SQL is Oracle Corporation`s procedural language for SQL and the Oracle 
    relational database.


CHAPTER 2. "Overview of PL/SQL"
Task 2.1. 
    1. Write structure of PL/SQL block (indicate optional and mandatory sections):
Solution:
    --1
    DECLARE   --optional
        variables, cursors, user-defined exceptions;
    BEGIN     --mandatory
        SQL and PL/SQL statments;
    EXCEPTION --optional
        exception handling;
    END;      --mandatory
        
Task 2.2.
    1. List block types, write examples. 
    2. Which block types are subprograms?
    3. List engines in PL/SQL block.
Solution:
    --1
    --Anonymous block - is an executable statement
    [DECLARE]
        ...;
    BEGIN
        statements;
    [EXCEPTION]
        ...;
    END;
    --Procedure
    CREATE OR REPLACE PROCEDURE MY_PROCEDURE IS
        ...;
    BEGIN
        statements;
    [EXCEPTION]    
        ...;
    END;
    --Function
    CREATE OR REPLACE FUNCTION MY_FUNCTION RETURN DATATYPE IS
        ...;
    BEGIN
        statements;
        RETURN value;
    [EXCEPTION]
        ...;
    END;
    --2
    Subprograms are procedures and functions.
    --3
    PL/SQL block contain: PL/SQL engine and SQL engine.

Task 2.3.
    For block:
    BEGIN
        DBMS_OUTPUT.PUT_LINE('my_text');
    END;
    1. How to create script at external file and run this in SQL Developer?    
    2. How to run this script in SQLPlus?
    3. How to run this script without writing path in SQLPlus and SQL Developer?
Solution:    
    --1
    Create file in directory "D:\text.sql" with next data:
    BEGIN
        DBMS_OUTPUT.PUT_LINE('my_text');
    END;
    Then write in SQL_developer command and run it:
    @D:\text.sql
    --2
    --Login to database in sqlplus.
    myuser/mypass@mydatabase (for example hr/hr@orclpdb)  
    --then write:
    SET SERVEROUTPUT ON;
    @D:\text.sql
    /
    --3
    --For SQLPlus
    --You can change directory in CMD Windows with command "D:".
    --Then write command "sqlplus" in CMD Windows, login and then write in SQLPlus
    @text.sql
    /
    --In SQL Developer write and run
    cd d:
    @text.sql
    
    
CHAPTER 3. "Declaring PLSQL Variables"
Task 3.1.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5), 
                           COL2 VARCHAR2(100) DEFAULT 'zero' NOT NULL);
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');         
        COMMIT;
    Then do next:
    1. Write naming rules for variables. List namespaces and objects for them.
    2. What will be if we try declare two variables with same name?
    Declare and display into DBMS_OUTPUT: 
    'var1' with number type, null not allowed; 
    'var2' with varchar2 (with max length) type default 10;
    'var 3' constant with date type;
    'VAR_4' with default number 4 and declare with rules as 'var 3';
    'begin' with varchar2 type default 'begin'.
    3. List types of PL/SQL variables and non-PL/SQL variables. Explain them.
    4. Declare and then use SELECT INTO:
    - for variable with type as TAB1.COL2 that have NOT NULL and default 'zero';
    - for variable, that have same type as this first variable (do not write
    TAB1.COL2); which default value will be in this variable?
    5. Write variable 'v' with subtype where allows values between -1 to 5. Explain.
    6. What another name of bind variables. Using TAB1 create example of bind 
    variables (define with two ways) with types: number and string. Display this 
    variables manually. Display this variables automatically.
Solution:
    --1
    Begin with letter,
    Name long <= 30 symbols (in Oracle 12c Release 2 (12.2) <= 128).
    Contain only: A-Z; a-z; 0-9; _; $; #.
    One user can`t create objects with same names in one namespace.
    For name not allowed using Oracle`s server-reserved words.
    In double quotation marks can write any name, but always must use this name 
    with "". Ordinary uppercase names declared with "", you can use without "" with 
    case-insencitive.
    - USER, ROLES;
    - TABLES, VIEWS, SEQUENCES, PRIVATE SYNONYMS, USER-DEFINED TYPES;
    - PUBLIC SYNONYMS;
    - INDEXES;
    - CONSTRAINTS.
    --2
    DECLARE 
        --var1 NUMBER; --will be error if we try use this variable
        var1 NUMBER NOT NULL := 5*2; --need value, because NOT NULL
        var2 VARCHAR2(32767) DEFAULT '10';
        "var 3" CONSTANT DATE := TO_DATE('01.01.2000','DD.MM.YYYY'); 
        "VAR_4" NUMBER := 4;
        "begin" VARCHAR2(100) := 'begin';
    BEGIN 
        DBMS_OUTPUT.PUT_LINE('Var1='||var1); --will be error, if declare two "var1"
        DBMS_OUTPUT.PUT_LINE('Var2='||var2);
        DBMS_OUTPUT.PUT_LINE('Var3='||"var 3");
        DBMS_OUTPUT.PUT_LINE('VAR_4='||vAr_4);
        DBMS_OUTPUT.PUT_LINE('begin='||"begin");
    END;
    --3
    PL/SQL variables (some types have different maximum sizes in PL/SQL and SQL):
    --Scalar - can hold a single value.
    "DATA TYPE"    "CATEGORY"  "RANGE"               "NOTES"
    CHAR           Characters  Up to 32767 bytes     Fixed length characters.
                               (default 1 byte)
    VARCHAR2       Characters  Up to 32767 bytes     Variable character
    
    NUMBER(P, S)   Number      P = [1;38]
                               S = [-84; 127]
    BINARY_INTEGER Number      Integers:             Faster than NUMBER
    (PLS_INTEGER)              [-2,147,483,648;
                                2,147,483,647]    
    BOOLEAN        Boolean     TRUEFALSENULL
    BINARY_FLOAT   Number      Represents floating-
                               point number in 
                               IEEE 754 format. It
                               requires 5 bytes to
                               store the value.
    BINARY_DOUBLE  Number      Represents floating-
                               point number in 
                               IEEE 754 format. It
                               requires 9 bytes to
                               store the value.
    DATE                       Between 4712 B.C.     Stores year, month, day, hour,
                               and A.D. 9999         minute, second
    TIMESTAMP(P)               Between 4712 B.C.     Stores year, month, day, hour,
                               and A.D. 9999         minute, second and fractional
                                                     second. P - precision: 1 - 9, 
                                                     default precision = 6.
    TIMESTAMP WITH             Between 4712 B.C.     Includes a time-zone 
    TIME ZONE                  and A.D. 9999         
    
    TIMESTAMP WITH             Between 4712 B.C.     Includes a local time-zone 
    LOCAL TIME ZONE            and A.D. 9999             
    
    INTERVAL YEAR                                    Stores interval of years and
    TO MONTH                                         months
    
    INTERVAL DAY                                     Stores interval of days, hours,
    TO SECOND                                        minutes and seconds
    --LOB data types
    --CLOB
    A character large object containing single-byte or multibyte characters. Maximum
    size is (4gb - 1) * (DB_BLOCK_SIZE). Stores national character set data.
    --NCLOB
    A character large object containing Unicode characters. Both fixed-width and 
    variable-width character sets are supported, both using the database national 
    character set. Maximum size is (4 gb - 1) * (database block size); stores national
    character set data.
    --BLOB
    A binary large object. Maximum size is (4 gb - 1) * (DB_BLOCK_SIZE initializtion
    parameter (8TB to 128 TB)). Can use for store images.
    --BFILE
    Binary data stored in an external file (up to 4gb). Can use for store video.  
    --Composite data types
    Can contain different data types. For example: BOOLEAN + DATE + BLOB.
    --4
    DECLARE
        var1 TAB1.COL2%TYPE NOT NULL := 'zero'; --NOT NULL and DEFAULT from TAB1 not 
                                                --will be passed.
        var2 var1%TYPE := 'new'; --DEFAULT from var1 not will be passed,
                                 --but NOT NULL will be passed.
    BEGIN
        SELECT COL2,
               COL2
          INTO var1,
               var2
            FROM TAB1
                WHERE COL1 = 1;
    END;
    --5
    DECLARE
        --Range of values allow only for PLS_INTEGER and its subtypes
        SUBTYPE PLS IS PLS_INTEGER RANGE -1..5;
        v PLS;
    BEGIN
        v := -1;
        DBMS_OUTPUT.PUT_LINE(v);
    END;
    --6
    /* another name is HOST variables */
    VARIABLE var1 NUMBER /* for bind variable do not assign scale for NUMBER */
    VAR var2 VARCHAR2(100)
    --SET AUTOPRINT ON /* this is for automatic display bind variables */
    BEGIN
        SELECT COL1, 
               COL2 
          INTO :var1, 
               :var2
            FROM TAB1
                WHERE COL1 = 1;
    END;
    /
    PRINT var1 var2 
    
    
CHAPTER 4. "Writing executable statements"
Task 4.1.
    1. What mean identifier? Write two examples, explain it.
    2. What mean delimiters? Write three examples.
    3. What mean literals? Write three examples (string, number, BOOL).
    4. How write comment (2 ways).
    5. In PL/SQL block which functions can use and which functions can`t use?
    6. How assign sequence to variable?
    7. List two types of data type conversion.
    8. List lexical units?
Solution:
    --1
    --Identifiers are the names to PL/SQL objects.
    v_var1, 
    "v var 2" --for using this identifier need write exactly the same
    --2
    --A delimiter is a simple or compound symbol that has a special meaning to 
    --PL/SQL. For example, you use delimiters to represent arithmetic operations such 
    --as addition and subtraction.
    ; + -    --simple
    <> != || --compound
    --3
    --Literals is any value assigned to a variable.
    v_var := 'my_name';
    v_num := 55;
    v_bool := FALSE;
    --4
    -- this is one line comment
    /* this is multi line comment */
    --5
    --can use single-row functions
    v_var := LENGTH(my_var);
    v_var := NVL(my_var, 1);
    --can`t use DECODENVL2 and GROUP functions
    --6
    --starting 11g:
    DECLARE
        v_var NUMBER;
    BEGIN
        v_var := my_seq.NEXTVAL;
    END;
    --before 11g:
    DECLARE
        v_var NUMBER;
    BEGIN
        SELECT my_seq.NEXTVAL 
          INTO v_var
            FROM DUAL;
    END;
    --7
    --implicit conversion
    --explicit conversion
    --8
    It is comment, delimiters, identifiers, literals.
    
Task 4.2.
    1. Write anonymous block with nested block:
    - in comments indicate outer block and inner block;
    - in outer block define variable 'v1' NUMBER with default = 1 and variable 
    'v2' NUMBER with default = 2; 
    - in inner block define variable 'v2' with default value = 3.
    Display in inner block at the DBMS_OUTPUT for 'v2' both values: 2 and 3. Then
    after inner block display at the DBMS_OUTPUT for 'v2' values = 3.
Solution:    
    --1
    BEGIN
        <<outer>>
        --<<outer>> --for block must be unique label
        DECLARE --outer block
            v1 NUMBER := 1;
            v2 NUMBER := 2;
        BEGIN
            --inner block
            DECLARE
                v2 NUMBER := 3;
            BEGIN
                DBMS_OUTPUT.PUT_LINE('v2='||outer.v2);
                DBMS_OUTPUT.PUT_LINE('v2='||v2);
                outer.v2 := v2;
            END;         
            DBMS_OUTPUT.PUT_LINE('v2='||v2);
        END outer;
    END;


CHAPTER 5. "Interacting with Oracle DB Server"
Task 5.1.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5), COL2 VARCHAR2(100));
        INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one'); 
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');         
        COMMIT;
    Then do next:    
    1. Explain how to use DML, DDL, DCL, TCL statements in PL/SQL.
    2. Which precedence have column`s name and variable. Write example how define
    variable with name 'LENGTH' and also how use function LENGTH.
    3. Explain implicit cursors and for TAB1 write example uses implicit cursors 
    with different cursor`s attributes.
Solution:
    --1
    --We can use DML, TCL directly. DDL and DCL need use with dynamic SQL.
    --2
    --Column`s name have precedence over variable.
    DECLARE
        LENGTH NUMBER := -1;
    BEGIN 
        DBMS_OUTPUT.PUT_LINE(STANDARD.LENGTH(LENGTH));
    END;
    --3
    --A SQL (implicit) cursor is opened by the database to process each SQL 
    --statement that is not associated with an explicit cursor.
    BEGIN
        --attribute SQL%NOTFOUND
        DELETE TAB1
            WHERE COL1 = 999;
        IF SQL%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('NOTFOUND');            
        END IF;        
        --attributes SQL%FOUND and SQL%ROWCOUNT
        DELETE TAB1
            WHERE COL1 = 1;
        IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('DELETE ROWS='||SQL%ROWCOUNT);
        END IF;       
        --after COMMIT or ROLLBACK data in attributes will be lossed.
        COMMIT--ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('DELETE ROWS after COMMIT or ROLLBACK='||SQL%ROWCOUNT);
        --SQL%ISOPEN always has the value FALSE
        IF NOT SQL%ISOPEN THEN 
            DBMS_OUTPUT.PUT_LINE('FALSE');
        END IF;
    END;
    

CHAPTER 6. "Writing Control structure"
Task 6.1. IF...END_IF
    1. Define number`s variables: v0 = 0, v1 = 1, v2 = 2, v3 = 3, v4 = NULL.
    Then using 'IF...END IF' write 1 block where:
    - if v0 = v1 then display 1;
    - if v0 = v2 or v3 then display 23;
    - if v0 <> v4 then display 'NOT_NULL';
    in otherwise display 'nothing'.
    Which condition will be completed?
Solution:    
    --1
    --In IF...END statement first met condition will be completed. Otherwise will be 
    --run section 'ELSE'
    DECLARE
        v0 NUMBER := 0;
        v1 NUMBER := 1;
        v2 NUMBER := 2;
        v3 NUMBER := 3;
        v4 NUMBER := NULL;
    BEGIN
        IF v0 = v1 THEN
            DBMS_OUTPUT.PUT_LINE(1);
        ELSIF v0 = v2 OR v0 = v3 THEN
            DBMS_OUTPUT.PUT_LINE(23);
        ELSIF v0 <> NVL(v4, -1) THEN --this condtition will be completed
            DBMS_OUTPUT.PUT_LINE('NOT_NULL'); 
        ELSE
            DBMS_OUTPUT.PUT_LINE('nothing');
        END IF;
    END;

Task 6.2. CASE
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5), COL2 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. Which condition will be completed in CASE operator?
    2. For TAB1 make query using CASE (indicate type of this case) where :
    - if COL1 = 1 then display COL2;
    - if 2 <= COL1 <= 3 then display 22;
    - if COL1 <> NULL then display 'NOT_NULL';
    in otherwise display 'nothing'.
    3. Define number`s variables: v0 = 0, v1 = 1, v2 = 2, v3 = 3, v4 = NULL.
    Then write 1 block where use two different CASE (write type):
    - if v0 = v1 then display 1; 
    - if v0 = v2 or v3 then display 23;
    - if v0 <> v4 then display 'NOT NULL';
    in otherwise display 'nothing'. 
Solution:
    --1
    --First met condition will be performed, otherwise will be run section 'ELSE'
    --2 CASE expression (searched)
    SELECT CASE
             WHEN COL1 = 1 
               THEN COL2
             WHEN COL1 BETWEEN 2 AND 3 
               THEN '22' --must be string because first condition have type - string
             WHEN COL1 IS NOT NULL 
               THEN TO_CHAR('NOT NULL')
             ELSE 'nothing'
           END
        FROM TAB1 
    --3
    DECLARE
        v0 NUMBER := 0;
        v1 NUMBER := 1;
        v2 NUMBER := 2;
        v3 NUMBER := 3;
        v4 NUMBER := NULL;
        res VARCHAR2(10);
    BEGIN
        --CASE expression (searched case statement)
        res :=  CASE
                  WHEN v0 = v1
                    THEN TO_CHAR(1)
                  WHEN v0 IN (v2, v3)
                    THEN TO_CHAR(23)
                  WHEN v0 <> NVL(v4, -1)
                    THEN 'NOT NULL
                  ELSE
                    'nothing'
                END;
        DBMS_OUTPUT.PUT_LINE(res);
        --CASE statement (searched case statement)
        CASE
          WHEN v0 = v1
            THEN DBMS_OUTPUT.PUT_LINE(1);
          WHEN v0 IN (v2, v3)
            THEN DBMS_OUTPUT.PUT_LINE(23);
          WHEN v0 <> NVL(v4, -1)
            THEN DBMS_OUTPUT.PUT_LINE('NOT NULL');
          ELSE
            DBMS_OUTPUT.PUT_LINE('nothing');
        END CASE;
    END;    

Task 6.3. Loops
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5), COL2 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. Define x = 0, then write a BASIC loop, where add 1 to x on each step of cycle, 
    then stop cycle when x = 3.
    2. Write a FOR loop from 1 to 5, write a FOR loop from 5 to 1. Which type have
    loop counter?
    3. Define x = 0, then write a WHILE loop, where add 1 to x on each step of cycle.
    Stop loop when x >= 3.
    4. Write FOR loop from 1 to 4 with label 'loop1'. Then inside 'loop1' write
    nested loop FOR from 1 to current step of 'loop1' with label 'loop2' where 
    must be condition: if 'loop1' equal 3 then stop 'loop1'.
    5. Make FOR loop from 1 to 4 where display all steps, but simulate step = 0.5 and
    if step = 1,5 then go to next step (also do not display step = 1,5). Explain it.
    6. Demonstrate and explain situation when we use FOR loop with counter name equal 
    variable name default 5.
    7. Demonstrate GOTO statement (include nested block), write restrictions.
Solution:
    --1
    DECLARE
        x NUMBER := 0;
    BEGIN
        LOOP
            x := x + 1;
            DBMS_OUTPUT.PUT_LINE(x);
            EXIT WHEN x = 3;
        END LOOP;
    END;
    --2
    --integer FOR loop without reverse
    BEGIN 
        FOR i IN 1..5 LOOP
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    END;
    --integer FOR loop with reverse
    BEGIN 
        FOR i IN REVERSE 1..5 LOOP
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    END;    
    --3
    --while loop
    DECLARE
        x NUMBER := 0;
    BEGIN
        WHILE x < 3 LOOP
            DBMS_OUTPUT.PUT_LINE(x);
            x := x + 1;
        END LOOP;    
    END;
    --4
    BEGIN
        <<loop1>>
        FOR i IN 1..4 LOOP
            <<loop2>>
            <<loop2>>
            FOR j IN 1..i LOOP
                EXIT loop1 WHEN i = 3;
                DBMS_OUTPUT.PUT_LINE('i='||i);
                DBMS_OUTPUT.PUT_LINE(' j='||j);
            END LOOP loop2;
        END LOOP loop1;
    END;
    --5
    DECLARE
        step NUMBER := 0.5;
    BEGIN
        FOR i IN 1..4 LOOP
            CONTINUE WHEN i * step = 1.5; --In this loop all actions on the step =1.5
                                          --after CONTINUE will not be performed
            DBMS_OUTPUT.PUT_LINE(i * step);
        END LOOP;
    END;
    --6
    DECLARE
        i NUMBER := 5;
    BEGIN
        --FOR i IN 1..i LOOP --will be error
        FOR i IN 1..3 LOOP
            DBMS_OUTPUT.PUT_LINE(i); --result = 1, 2, 3
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(i); --result = 5
    END;
    --7
    --GOTO statement can transfer control to label wrote before a block or statement.
    --Cannot transfer control into an IF statement
    BEGIN
        NULL;
        IF 1 = 1 THEN
            GOTO my;
        END IF;
        BEGIN
            <<my>> DBMS_OUTPUT.PUT_LINE(1); --not will be displayed
        END;
        <<my>>DBMS_OUTPUT.PUT_LINE(2); --will be displayed
        --<<my>>DBMS_OUTPUT.PUT_LINE(3); --label must be unique in its scope
    END;
    
    
CHAPTER 7. "Working with Composite Data Types"
Task 7.1. RECORD.
    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. Where we can declare records? When we can`t create and use a RECORD type?
    What about record based on table with invisible column?
    2. Declare a PL/SQL record: programmer-defined records. Two ways: using TAB1 and 
    not (but structure how as TAB1). 
    Then into any record load row with fields: 4, 'four'. Display values of record.
    Then insert into tab1 this record (two syntax).
    Then load first row from TAB1 into this record (two syntax). 
    3. On which database objects we can create table-based record. Declare a PL/SQL 
    table-based record using TAB1.
    Then load first row from TAB1 into this record (two syntax). Display values of 
    record.
    Then update TAB1 where COL1 = 2 by values from record (two syntax).
    4. Declare programmer-defined record using TAB1 with nested programmer-defined 
    record with 3 fields: NUMBERVARCHAR2(100), DATE
    Insert into non-nested record values from TAB1 where COL1 = 1. Insert into nested
    record values: 0, 'zero', '01.01.2000'. Display values of record.
    5. How much fields can store a record?
    6. How to test record for nullity, equality or inequality?
Solution:
    --1
    Can declare in declarative part of any block, subprogram or package.
    Can`t create at schema level. Can`t use as attribute data type in ADT. RECORD 
    type in a package spec is incompatible with identically local RECORD type.
    RECORD defined on table with virtual column can`t be inserted into this table.
    Enabling column visible allows access in RECORD to this column, invisible - not.
    --2
    DECLARE
        --using table`s type
        TYPE type_rec1 IS RECORD (field1 TAB1.COL1%TYPE,
                                  field2 TAB1.COL2%TYPE);
        v_rec1 TYPE_REC1;
        --without using table`s type
        TYPE type_rec2 IS RECORD (field1 NUMBER DEFAULT 0, 
                                  field2 VARCHAR2(100) NOT NULL DEFAULT 'zero');
        v_rec2 TYPE_REC2;    
    BEGIN
        SELECT 4, 
               'four'
          INTO v_rec1
            FROM DUAL;
        DBMS_OUTPUT.PUT_LINE(v_rec1.FIELD1||' '||v_rec1.FIELD2);        
        INSERT INTO TAB1 VALUES v_rec1;
        INSERT INTO TAB1(COL2, COL1) VALUES(v_rec1.FIELD2, v_rec1.FIELD1);
        COMMIT;
        
        SELECT *
          INTO v_rec1
            FROM TAB1
                WHERE COL1 = 1;
        
        SELECT COL2, 
               COL1
          INTO v_rec1.FIELD2,
               v_rec1.FIELD1
            FROM TAB1
                WHERE COL1 = 1;               
    END;
    --3
    --Can create table-based record on table or view.
    DECLARE
        v_rec TAB1%ROWTYPE;
    BEGIN
        SELECT *
          INTO v_rec
            FROM TAB1
                WHERE COL1 = 1;            
        DBMS_OUTPUT.PUT_LINE(v_rec.COL1||' '||v_rec.COL2);
        
        --
        SELECT COL2, COL1
          INTO v_rec.COL2, v_rec.COL1
            FROM TAB1
                WHERE COL1 = 1;            
        DBMS_OUTPUT.PUT_LINE(v_rec.COL2||' '||v_rec.COL1);
        
        --
        UPDATE TAB1
            SET ROW = v_rec
                WHERE COL1 = 2;
        UPDATE TAB1
            SET COL1 = v_rec.COL1, 
                COL2 = v_rec.COL2
                WHERE COL1 = 2;
        COMMIT;        
    END;
    --4
    DECLARE
        TYPE nested_rec IS RECORD (nf1 NUMBER,
                                   nf2 VARCHAR2(100),
                                   nf3 DATE);
        TYPE rec IS RECORD (field1 TAB1.COL1%TYPE,
                            field2 TAB1.COL2%TYPE,
                            field3 NESTED_REC);
        v_rec REC;
    BEGIN
        SELECT COL1, COL2,
               0, 'zero', TO_DATE('01.01.2000','DD.MM.YYYY')
          INTO v_rec.FIELD1, v_rec.FIELD2,
               v_rec.FIELD3.NF1, v_rec.FIELD3.NF2, v_rec.FIELD3.NF3
            FROM TAB1
                WHERE COL1 = 1;
        DBMS_OUTPUT.PUT_LINE(v_rec.FIELD1 ||' '||
                             v_rec.FIELD2 ||' '||
                             v_rec.FIELD3.NF1 ||' '||
                             v_rec.FIELD3.NF2 ||' '||
                             v_rec.FIELD3.NF3);
    END;
    --5
    Record can have as many fields as necessary.
    --6
    Cannot be tested
    
Task 7.2. INDEX BY tables (associative arrays)
    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. Explain structure of INDEX BY tables. What size of this collection?
    2. Declare INDEX BY TABLES variables:
    - with scalar values;
    - with non scalar values (2 ways)
    Assign to this collections two elements and display them.
    3. Demonstrate how to make an associative array persistent for the life of a 
    database session.
Solution:
    --1
    Have two columns:
    - Primary key of integer or string data type.
    - Column of scalar or record data type.
    Size of this collection depends on the values that the key data type can hold.
    --2
    DECLARE
        --scalar
        TYPE type_ind_s IS TABLE OF NUMBER --TABLE OF VARCHAR2(100)
            INDEX BY PLS_INTEGER--INDEX BY VARCHAR2(100)   
        v_s type_ind_s;        
        --record
        TYPE type_rec IS RECORD (f1 NUMBER DEFAULT 0,
                                 f2 TAB1.COL2%TYPE);
        TYPE type_ind_ns IS TABLE OF type_rec
            INDEX BY PLS_INTEGER;            
        v_ns type_ind_ns;        
        --rowtype
        TYPE type_row IS TABLE OF TAB1%ROWTYPE
            INDEX BY PLS_INTEGER;
        v_rt type_row;
    BEGIN
        --scalar
        v_s(3) := 33;
        v_s(-1) := -11;
        DBMS_OUTPUT.PUT_LINE(v_s(3)||v_s(-1));
        --record
        v_ns(2).f1 := 22; v_ns(2).f2 := 'two';
        v_ns(4).f1 := 44; v_ns(4).f2 := 'four';
        DBMS_OUTPUT.PUT_LINE('record for index 2: '||v_ns(2).f1||' '||v_ns(2).f2);
        DBMS_OUTPUT.PUT_LINE('record for index 4: '||v_ns(4).f1||' '||v_ns(4).f2);
        --rowtype
        FOR i IN 1..2 LOOP
            SELECT * 
              INTO v_rt(i)
                FROM TAB1
                    WHERE COL1 = i;
            DBMS_OUTPUT.PUT_LINE(v_rt(i).COL1||' '||v_rt(i).COL2);
        END LOOP;        
    END;
    --3
    --make package specification with array and populate it in package body
    CREATE OR REPLACE PACKAGE PCK IS
        TYPE arr IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
        v arr;
    END PCK;
    CREATE OR REPLACE PACKAGE BODY PCK IS
    BEGIN
        FOR i IN 1..3 LOOP
            v(i) := i * 10;
        END LOOP;
    END PCK;
    EXEC DBMS_OUTPUT.PUT_LINE(PCK.v(1)); --result 10
    EXEC DBMS_OUTPUT.PUT_LINE(PCK.v(3)); --result 30
    
Task 7.3.1. INDEX BY tables (associative arrays) methods. NUMBERS.
    Examine block:
        TYPE t IS TABLE OF NUMBER 
            INDEX BY PLS_INTEGER;
        v_v t; 
        v_v(-1) := -11;
        v_v(0) := 99;
        v_v(1) := 11;
        v_v(3) := 22;
    And make next operations:
    1. Create block with this variables and list methods using with INDEX BY tables.
    Write and explain result for each method.
Solution:
    --1
    DECLARE
        TYPE t IS TABLE OF NUMBER 
            INDEX BY PLS_INTEGER;
        v_v t; 
    BEGIN
        v_v(-1) := -11;
        v_v(0) := 99;
        v_v(1) := 11;
        v_v(3) := 22;
        --EXISTS(n). Returns TRUE if n-element exists in collection.
        IF v_v.EXISTS(0) THEN
            DBMS_OUTPUT.PUT_LINE('EXISTS=TRUE '||v_v(0)); --result=99
        ELSE
            DBMS_OUTPUT.PUT_LINE('EXISTS=FALSE');
        END IF;
        --COUNT. Count of indexes. If collection empty, then 0.
        DBMS_OUTPUT.PUT_LINE('COUNT='||v_v.COUNT); --result=4
        --FIRST. Minimum index. If collection empty, then NULL.
        DBMS_OUTPUT.PUT_LINE('FIRST='||v_v.FIRST); --result=-1
        --LAST. Maximum index. If collection empty, then NULL.
        DBMS_OUTPUT.PUT_LINE('LAST='||v_v.LAST); --result=3
        --PRIOR(n). If not have preceding index, then NULL.
        DBMS_OUTPUT.PUT_LINE('PRIOR='||v_v.PRIOR(3)); --result=1
        --NEXT(n). If not have succeeding index, then NULL.
        DBMS_OUTPUT.PUT_LINE('NEXT='||v_v.NEXT(3)); --result=NULL
        --DELETE(x, y). X must be <= Y.
        v_v.DELETE(1, 2); --DELETE elements with indexes between 1 and 2.
        v_v.DELETE(3);    --DELETE element with index = 3
        v_v.DELETE;       --DELETE all elements           
    END;
    
Task 7.3.2. INDEX BY tables (associative arrays) methods. STRINGS.
    1. Examine block and write result for each method.
        DECLARE
            TYPE t IS TABLE OF VARCHAR2(100) 
                INDEX BY VARCHAR2(100);
            v_v t; 
        BEGIN
            v_v('one') := '11';
            v_v('two') := '22';
            v_v('three') := '33';
            v_v('four') := '44';

            IF v_v.EXISTS(0) THEN
                DBMS_OUTPUT.PUT_LINE('EXISTS='||v_v(0));
            ELSE
                DBMS_OUTPUT.PUT_LINE('EXISTS=FALSE');
            END IF;
            DBMS_OUTPUT.PUT_LINE('COUNT='||v_v.COUNT); 
            DBMS_OUTPUT.PUT_LINE('FIRST='||v_v.FIRST); 
            DBMS_OUTPUT.PUT_LINE('LAST='||v_v.LAST); 
            DBMS_OUTPUT.PUT_LINE('PRIOR='||v_v.PRIOR('two')); 
            DBMS_OUTPUT.PUT_LINE('NEXT='||v_v.NEXT('two')); 
            v_v.DELETE('one', 'two');
            v_v.DELETE('two');
            v_v.DELETE;
        END;
    2. What will happen for using methods (list this methods) if you change values of
    NLS_SORT and NLS_COMP after populating an associative array indexed by string?
Solution:
    --1
    EXISTS = FALSE
    COUNT  = 4
    FIRST  = four
    LAST   = two
    PRIOR  = three
    NEXT   = NULL
    DELETE('one', 'two') - will be deleted v_v('one'), v_v('three'), v_v('two')
    DELETE('two') - will be deleted only v_v('two'). If not found, then error not be.
    DELETE - will be deleted all elements
    --2
    FIRSTLASTNEXTPRIOR might return unexpected values or raise exceptions.

Task 7.4. NESTED TABLES
    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 difference between associative arrays and nested tables.
    2. What is: null collection (nested table); empty collection (nested table)?
    3. Create NESTED TABLES with scalar and non-scalar (2 ways) values. Add to this 
    NESTED TABLES: for scalar - 6 new values without using loop (2 in declare section 
    and 2 in execution section with directly assigning and 2 without), for 
    non-scalar - 4 values using loop. Then, using loop, display all values from this 
    nested tables.
    For scalar variable: delete sixth element, delete fiveth element, then delete 2-4
    elements, then delete all elements.
    4. How to add new elements (but they have NOT NULL constraint) to collection?
Solution:
    --1
    - Nested table have not INDEX BY clause;
    - Nested table can be used in SQL;
    - Initialization required;
    - Extend required;
    - Can be stored in database.
    --2
    An uninitialized nested table variable is a null collection. 
    Empty nested table variable is initialized nested table variable without values.
    --3
    DECLARE
        --scalar
        TYPE type_s IS TABLE OF VARCHAR2(100); --TABLE OF NUMBER;
        v_s type_s := type_s('one', 'two');
        
        --record
        TYPE type_rec IS RECORD (f1 NUMBER DEFAULT 0,
                                 f2 TAB1.COL2%TYPE);
        TYPE type_ns IS TABLE OF type_rec;  
        v_ns type_ns := type_ns();
        
        --rowtype
        TYPE type_row IS TABLE OF TAB1%ROWTYPE;
        v_rt type_row := type_row();
    BEGIN
        --scalar
        v_s.EXTEND(2);
        v_s(3) := 'three';
        v_s(4) := 'four';
            v_s.EXTEND(2, 4);
        FOR i IN v_s.FIRST..v_s.LAST LOOP
            DBMS_OUTPUT.PUT_LINE('v_s('||i||') '||v_s(i));
        END LOOP;

        --delete block
        v_s.DELETE(6);              --delete 6th element 
        v_s.TRIM--v_s.TRIM(1);    --delete 5th element 
        v_s.DELETE(2, 4);           --delete 2-4 elements
        v_s.DELETE;                 --delete all elements
        DBMS_OUTPUT.PUT_LINE(v_s.COUNT);
        
        --record
        FOR i IN 1..2 LOOP
            v_ns.EXTEND;
            SELECT COL1, COL2
              INTO v_ns(i)
                FROM TAB1
                    WHERE COL1 = i;
            DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);                    
        END LOOP;
        FOR i IN 3..4 LOOP
            v_ns.EXTEND;
            v_ns(i).f1 := i; v_ns(i).f2 := i;
            DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);
        END LOOP;        
        
        --rowtype
        FOR i IN 1..2 LOOP
            v_rt.EXTEND;
            SELECT COL1, COL2
              INTO v_rt(i)
                FROM TAB1
                    WHERE COL1 = i;
            DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);                    
        END LOOP;     
        FOR i IN 3..4 LOOP
            v_rt.EXTEND;
            v_rt(i).COL1 := i; v_rt(i).COL2 := i;
            DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
        END LOOP
    END;
    --4
    Use EXTEND(n, i)
    
Task 7.5. Assigning set operation results to nested table.
    1. Declare fisrt nested table (with values: NULLNULL, 1, 1, 2, 2, 3, 3), second
    nested table (with values: 2, 2, 3, 3, 4, 4, NULLNULL). Then demonstrate:
    - MULTISET for nested tables, write result.
    - SET for nested tables, write result.
Solution:
    --1
    DECLARE
        TYPE nt IS TABLE OF NUMBER--type for two variables must be same
        a nt := nt(NULLNULL, 1, 1, 2, 2, 3, 3);
        b nt := nt(2, 2, 3, 3, 4, 4, NULLNULL);
        res nt; --result type must be as type of variables
    BEGIN
        --MULTISET EXCEPT. Returns a nested table whose elements are in the first
        --nested table but not in the second nested table. Keyword ALL is default.
        res := a MULTISET EXCEPT /* ALL */ b; --1 1 
        res := a MULTISET EXCEPT DISTINCT b; --1
        
        --MULTISET INTERSECT. Returns a nested table whose values are common in the 
        --two input nested tables. Keyword ALL is default.
        res := a MULTISET INTERSECT /* ALL */ b; --NULLNULL, 2, 2, 3, 3
        res := a MULTISET INTERSECT DISTINCT b; --NULL, 2, 3
        
        --MULTISET UNION. Returns a nested table whose values are those of the two 
        --input nested tables. Keyword ALL is default.
        --NULL,NULL,1,1,2,2,3,3,2,2,3,3,4,4,NULL,NULL
        res := a MULTISET UNION /* ALL */ b;
        res := a MULTISET UNION DISTINCT b; --NULL,1,2,3,4

        --SET. Function takes a nested table and returns a nested table of
        --the same data type without duplicates.
        res := SET(a); --NULL, 1, 2, 3
        res := SET(b); --2, 3, 4, NULL
        
        FOR i IN res.FIRST..res.LAST LOOP
            DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(res(i)), 'NULL'));
        END LOOP;
    END;
    
Task 7.6. NESTED TABLE stored in the database.
    1. Create table TABN with NESTED TABLE stores in database with columns: COL1 
    NUMBER, COL2 VARCHAR2(100), COL3 NESTED TABLE with numbers. Add to TABN 1 new row.    
Solution:
    --1
    DROP TABLE TABN;
    CREATE OR REPLACE TYPE type_n IS TABLE OF NUMBER;        
    CREATE TABLE TABN (COL1 NUMBER
                       COL2 VARCHAR2(100), 
                       COL3 TYPE_N)
        NESTED TABLE COL3 STORE AS COL3_TABLE;
    INSERT INTO TABN (COL1, COL2, COL3) 
        VALUES (1, 'one', TYPE_N(11, 22, 33));
    COMMIT;
    SELECT * FROM TABN;
    
Task 7.7. VARRAYS
    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. What difference between VARRAY and other collections?
    2. Define variables VARRAYS: with 4 elements with scalar and non-scalar 
    types (2 ways). Fill this VARRAYs by values using loop. Display values.
    For any variable: delete fourth element, then delete 2-3 elements, then delete 
    all elements.
Solution:
    --1
    Varray can`t extend over his size. Varray have bounded number of elements.
    Bound of varray must be a positive integer.
    --2
    DECLARE
        --scalar
        TYPE type_s IS VARRAY(4) OF NUMBER--VARRAY(4) OF VARCHAR2(100);
        v_s type_s := type_s();
        
        --record
        TYPE type_rec IS RECORD (f1 NUMBER DEFAULT 0,
                                 f2 TAB1.COL2%TYPE);
        TYPE type_ns IS VARRAY(4) OF type_rec;  
        v_ns type_ns := type_ns();
        
        --rowtype
        TYPE type_row IS VARRAY(4) OF TAB1%ROWTYPE;
        v_rt type_row := type_row();
    BEGIN
        --scalar
        FOR i IN 1..v_s.LIMIT LOOP
            v_s.EXTEND;
            v_s(i) := i;
            DBMS_OUTPUT.PUT_LINE('v_s('||i||') '||v_s(i));
        END LOOP;
        
        --delete block
        v_s.TRIM;    --delete 4th element   
        v_s.TRIM(2); --delete 2 elements from the end of a collection
        v_s.DELETE;  --delete all elements, but delete(a, b) NOT ALLOWED for varrays
        
        --record
        FOR i IN 1..2 LOOP
            v_ns.EXTEND;
            SELECT COL1, COL2
              INTO v_ns(i)
                FROM TAB1
                    WHERE COL1 = i;
            DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);                    
        END LOOP;
        FOR i IN 3..4 LOOP
            v_ns.EXTEND;
            v_ns(i).f1 := i; v_ns(i).f2 := i;
            DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);
        END LOOP;
        
        --rowtype
        FOR i IN 1..2 LOOP
            v_rt.EXTEND;
            SELECT COL1, COL2
              INTO v_rt(i)
                FROM TAB1
                    WHERE COL1 = i;
            DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
        END LOOP;          
        FOR i IN 3..4 LOOP
            v_rt.EXTEND;
            v_rt(i).COL1 := i; v_rt(i).COL2 := i;
            DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
        END LOOP;        
    END;
    
Task 7.8. VARRAY stored in the database.
    1. Create table TABV with VARRAY stored in database with columns: COL1 
    NUMBER, COL2 VARCHAR2(100), COL3 VARRAY with numbers. Add to TABV 1 new row.
    2. Using anonymous block demonstrate:
    - which method does not raise the predefined exception COLLECTION_IS_NULL;
    - which method usefull only on varrays. Explain it.
Solution:
    --1
    DROP TABLE TABV;
    CREATE OR REPLACE TYPE type_n IS VARRAY(2) OF NUMBER;        
    CREATE TABLE TABV (COL1 NUMBER
                       COL2 VARCHAR2(100), 
                       COL3 TYPE_N);
    INSERT INTO TABV (COL1, COL2, COL3) 
        VALUES (1, 'one', TYPE_N(11, 22));
    COMMIT;
    SELECT * FROM TABV;
    --2
    DECLARE
        TYPE var IS VARRAY(3) OF NUMBER;
        v var;
    BEGIN
        IF NOT v.EXISTS(1) THEN --only EXISTS for NULL collection not raise exception
            DBMS_OUTPUT.PUT_LINE('Not exception');
        END IF;
        v := var(1, NULL);
        --Method LIMIT return max available quantity of elements (bound) in VARRAY.
        --For INDEX BY tables and NESTED TABLES this method return NULL.
        DBMS_OUTPUT.PUT_LINE(v.LIMIT); --result = 3
        --but COUNT method return quantity of initialized elements with values or 
        --with NULL. For varrays, FIRST always returns 1 and LAST always equals COUNT
        DBMS_OUTPUT.PUT_LINE(v.COUNT); --result = 2
    END;

Task 7.9. Collections 'SYS.'.
    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. Create SYS. collections with number and string types. Explain length of types.
    In number`s collection put values of TAB1.COL1 from first two rows.
    In string`s collection put value 'zero'.
    Using query with number`s collection put count of elements from number`s 
    collection to variable 'res'. Display value of this variable.
Solution:
    DECLARE
        --Stores varrays of NUMBERs
        --VARRAY(32767) OF NUMBER
        n SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();    
            --Stores varrays of VARCHAR2s
            --VARRAY(32767) OF VARCHAR2(4000)
            s SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
        res NUMBER;
    BEGIN
        --
        FOR i IN 1..2 LOOP
            n.EXTEND;
            SELECT COL1
              INTO n(i)
                FROM TAB1
                    WHERE COL1 = i;
            DBMS_OUTPUT.PUT_LINE(n(i));
        END LOOP;
        
        --
        s.EXTEND;
        s(s.COUNT) := 'zero';
        DBMS_OUTPUT.PUT_LINE(s(s.COUNT));
        
        --
        SELECT COUNT(*)
          INTO res
            FROM TABLE(n);
        DBMS_OUTPUT.PUT_LINE(res);        
    END;

Task 7.10. Collection comparisons.
    1. Explain compare collection variables to the value NULL, to each
    other (for equality and inequality), with SQL Multiset conditions:
    - associative array;
    - nested table;
    - varray.
Solution:
    --1
    -- associative array
    Cannot compare associative array variables.
    --
    For nested tables and varrays use the IS[NOTNULL operator when comparing to 
    the NULL value.
    --
    Two nested table variables are equal if and only if they have the 
    same set of elements (in any order) and have not NULL values.
    NULL nested table <> NULL nested table
    If two nested table variables have the same nested table type, and that nested 
    table type does not have elements of a record type, then you can compare the 
    two variables for equality or inequality with the relational operators equal(=)
    --For nested tables can use MULTISET conditions
    DECLARE
        TYPE nt IS TABLE OF NUMBER;
        n0 nt;
        n1 nt := nt();
        n2 nt := nt(NULL);
        n3 nt := nt(1, 2);
        n4 nt := nt(1, 2, 3, 3, NULL);
    BEGIN
        IF n0 IS A SET THEN 
            DBMS_OUTPUT.PUT_LINE('IS A SET');
        ELSIF n0 IS NOT A SET THEN 
            DBMS_OUTPUT.PUT_LINE('IS NOT A SET');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NULL');
        END IF;
        IF n1 IS A SET THEN DBMS_OUTPUT.PUT_LINE('IS A SET'); END IF;
        IF n2 IS A SET THEN DBMS_OUTPUT.PUT_LINE('IS A SET'); END IF;
        IF n3 IS A SET THEN DBMS_OUTPUT.PUT_LINE('IS A SET'); END IF;
        IF n4 IS NOT A SET THEN DBMS_OUTPUT.PUT_LINE('IS NOT A SET'); END IF;
        --
        IF n0 IS EMPTY THEN 
            DBMS_OUTPUT.PUT_LINE('IS EMPTY');
        ELSIF n0 IS NOT EMPTY THEN 
            DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NULL');
        END IF;
        IF n1 IS EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS EMPTY'); END IF;
        IF n2 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
        IF n3 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
        IF n4 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
        --
        IF 1 MEMBER OF n0 THEN 
            DBMS_OUTPUT.PUT_LINE('MEMBER');
        ELSIF 1 NOT MEMBER OF n0 THEN 
            DBMS_OUTPUT.PUT_LINE('NOT MEMBER');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NULL');
        END IF;
        IF n1 IS EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS EMPTY'); END IF;
        IF n2 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
        IF n3 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
        IF n4 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
        --
        IF 1 MEMBER OF n0 THEN 
            DBMS_OUTPUT.PUT_LINE('MEMBER');
        ELSIF 1 NOT MEMBER OF n0 THEN 
            DBMS_OUTPUT.PUT_LINE('NOT MEMBER');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NULL');
        END IF;
        IF 1 NOT MEMBER n1 THEN DBMS_OUTPUT.PUT_LINE('NOT MEMBER'); END IF;
        IF 1 MEMBER OF n2 THEN 
            DBMS_OUTPUT.PUT_LINE('MEMBER');
        ELSIF 1 NOT MEMBER OF n2 THEN 
            DBMS_OUTPUT.PUT_LINE('NOT MEMBER');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NULL');
        END IF;
        IF 1 MEMBER n3 THEN DBMS_OUTPUT.PUT_LINE('MEMBER'); END IF;
        IF 1 MEMBER OF n4 THEN DBMS_OUTPUT.PUT_LINE('MEMBER'); END IF;
        --
        IF n1 SUBMULTISET OF n0 THEN DBMS_OUTPUT.PUT_LINE('SUBMULTISET'); END IF;
        IF n0 SUBMULTISET n1 OR 
           n0 NOT SUBMULTISET n1 THEN 
            DBMS_OUTPUT.PUT_LINE('SUBMULTISET OR NOT SUBMULTISET');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NULL');
        END IF;
        IF n3 SUBMULTISET n4 THEN DBMS_OUTPUT.PUT_LINE('SUBMULTISET'); END IF;
    END;

Task 7.11. Using multidimensional collections.
    1. Create number associative array 'aaa' with elements. Each element must
    consist of number associative array 'a' with two elements (for example: 10, 20; 
    30, 40; ...). Fill array 'aaa' with different ways. Delete last element of array 
    'a' from last element of 'aaa'. Display all 'a' elements from array 'aaa'.
    2. Remake point 1, but use nested table and instead DELETE replace first element
    from 'aaa' by last element from 'aaa' and replace (without loop) all 'a' elements
    from first 'aaa' element by value = 0.
Solution:
    --1
    DECLARE
        TYPE t IS TABLE OF NUMBER 
            INDEX BY PLS_INTEGER;
        TYPE ttt IS TABLE OF t
            INDEX BY PLS_INTEGER;
        a t;
        aaa ttt;
    BEGIN
        a(1) := 10;
        a(2) := 20;
            aaa(1) := a;
        --
        aaa(2)(1) := 30;
        aaa(2)(2) := 40;
        --
        aaa(aaa.LAST).DELETE(aaa(aaa.LAST).LAST);
        --
        FOR i IN aaa.FIRST..aaa.LAST LOOP
            FOR j IN aaa(i).FIRST..aaa(i).LAST LOOP
                DBMS_OUTPUT.PUT_LINE(aaa(i)(j));
            END LOOP;
        END LOOP;
    END;
    --2
    DECLARE
        TYPE t IS TABLE OF NUMBER;
        TYPE ttt IS TABLE OF t;
        a t := t(10, 20);
        aaa ttt := ttt(a, t(30, 40));        
    BEGIN
        a.DELETE;
        a.EXTEND(2);
        a(1) := 50; 
        a(2) := 60;
            aaa.EXTEND;
            aaa(3) := a;
        --
        aaa.EXTEND;
        aaa(4) := t(70, 80);
        --
        aaa(aaa.FIRST) := aaa(aaa.LAST);
        aaa(aaa.FIRST)(1) := 0;
        aaa(aaa.FIRST)(2) := 0;
        --alternative is aaa(aaa.FIRST) := t(0, 0);
        --
        FOR i IN aaa.FIRST..aaa.LAST LOOP
            FOR j IN aaa(i).FIRST..aaa(i).LAST LOOP
                DBMS_OUTPUT.PUT_LINE(aaa(i)(j));
            END LOOP;
        END LOOP;
    END;    

Task 7.12. Operations with collections.
    Examine next block:
        DECLARE
            TYPE a1 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
            TYPE a2 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;    
                TYPE n1 IS TABLE OF NUMBER;
                TYPE n2 IS TABLE OF NUMBER;    
                    TYPE v1 IS VARRAY(3) OF NUMBER;
                    TYPE v2 IS VARRAY(3) OF NUMBER;    
            acc1 a1;
            acc2 a1;
            acc3 a2; 
            acc1(1) := 10;
            acc2(1) := 20;
            acc3(1) := 30;
                nest1 n1 := n1(1);
                nest2 n1 := n1(2);
                nest3 n2 := n2(3);
                    vv1 v1 := v1(1);
                    vv2 v1 := v1(2);
                    vv3 v2 := v2(3);
                        s1 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1);
                        s2 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(2);
                        s3 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(3);
        BEGIN
            acc1(1) := 10;
            acc2(1) := 20;
            acc3(1) := 30;
            acc1 := acc2;
            acc1 := acc3;
            acc1(1) := acc3(1);
            acc1 := a1(1);
            acc1 := a1();
            acc1 := a1;
            acc1(1) := NULL;
                nest1 := nest2;
                nest1 := nest3;
                nest1(1) := nest3(1);
                nest1(1) := NULL;
                nest1 := n2();
                nest1 := n2(NULL);
                nest1 := n1;
                nest3 := n2(NULL);
                    vv1 := vv2;
                    vv1 := vv3;
                    vv1(1) := nest3(1);
                    vv1 := v1();
                    vv1 := v1(NULL);
                        s1 := s2;
                        s1 := s3;
        END;
    1. By commenting exclude all wrong rows.
    2. Demonstrate how to use collections (each have 2 elements) in SQL queries?
    Explain COLUMN_VALUE.
Solution:
    --1
    DECLARE
        TYPE a1 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
        TYPE a2 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;    
            TYPE n1 IS TABLE OF NUMBER;
            TYPE n2 IS TABLE OF NUMBER;    
                TYPE v1 IS VARRAY(3) OF NUMBER;
                TYPE v2 IS VARRAY(3) OF NUMBER;    
        acc1 a1;
        acc2 a1;
        acc3 a2; 
        --acc1(1) := 10;
        --acc2(1) := 20;
        --acc3(1) := 30;
            nest1 n1 := n1(1);
            nest2 n1 := n1(2);
            nest3 n2 := n2(3);
                vv1 v1 := v1(1);
                vv2 v1 := v1(2);
                vv3 v2 := v2(3);
                    s1 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1);
                    s2 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(2);
                    s3 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(3);
    BEGIN
        acc1(1) := 10;
        acc2(1) := 20;
        acc3(1) := 30;
        acc1 := acc2;
        --acc1 := acc3;
        acc1(1) := acc3(1);
        --acc1 := a1(1);
        acc1 := a1();
        --acc1 := a1;
        acc1(1) := NULL;
            nest1 := nest2;
            --nest1 := nest3;
            nest1(1) := nest3(1);
            nest1(1) := NULL;
            --nest1 := n2();
            --nest1 := n2(NULL);
            --nest1 := n1;
            nest3 := n2(NULL);
                vv1 := vv2;
                --vv1 := vv3;
                vv1(1) := nest3(1);
                vv1 := v1();
                vv1 := v1(NULL);
                    s1 := s2;
                    s1 := s3;
    END;
    --2
    --If datatype of elements is a scalar type, then the nested table or varray has 
    --a single column of that type, called COLUMN_VALUE.
    --Associative array can`t use in SQL query. VARRAY and NESTED TABLE can use if
    --they stored in database.
    CREATE OR REPLACE TYPE schema_nest IS TABLE OF NUMBER;
    CREATE OR REPLACE TYPE schema_varr IS VARRAY(2) OF NUMBER;
    DECLARE
        --TYPE loc IS TABLE OF NUMBER
        --v_loc loc := loc(1,2); !!! local nested table can`t use in SQL-query
        nt schema_nest := schema_nest(1,2);
        va schema_varr := schema_varr(1,2);
        res NUMBER;
    BEGIN
        SELECT MIN(COLUMN_VALUE)
          INTO res
            FROM TABLE(nt);
        DBMS_OUTPUT.PUT_LINE(res);
        SELECT MAX(COLUMN_VALUE)
          INTO res
            FROM TABLE(va);
        DBMS_OUTPUT.PUT_LINE(res);
    END;
   
Task 7.13. Characteristics of PL/SQL Collection Types.
    For next types:
    - associative array (or index-by table);
    - nested table;
    - variable-size array (varray);
    answer on questions:
    1. Amount of elements (unbounded or bounded)?
    2. Subscript type?
    3. Dense or sparse?
    4. Where created?
    5. Can be object type attribute?
    6. When collection type can be ADT?
Solution:
    --1
    A = Unbounded; N = Unbounded; V = Bounded.
    --2
    A = String or integer; N = Integer; V = Integer.
    --3
    A = Either; N = Starts dense, can become sparse; V = Always dense.
    --4
    A = Only in PL/SQL block; B = Either in PL/SQL block or at schema level;
    C = Either in PL/SQL block or at schema level.
    --5
    A = No; B = Yes; C = Yes.
    --6
    Nested table and varray types can be ADT if they standalone collection types.


CHAPTER 8. "Using explicit cursors"
Task 8.1. Explicit Cursor
    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. What different in declaring and managing for implicit and explicit cursors?
    2. Which commands move pointer from cursor and where. 
    3. Declare cursor 'cur' for TAB1. Fetch cursor into variables with different 3 
    types. Also for any type use in this cursor 4 attributes: 
    - if cursor not opened, then work with cursor;
    - if cursor have row, then display values from COL1, COL2 at DBMS_OUTPUT;
    - stop loop with two ways (if count of rows > 2 or cursor do not have more rows).
    4. Use loop (2 ways) with cursor for TAB1 without explicitly opening cursor.
    5. For TAB1 write cursor where COL2 = parameter 'p'. Also declare variable 'p'
    with default 'two'. Work with cursor by 2 different ways using for parameter
    variable 'p' (in first way use named notation, in second way - positional). 
    Explain the scope of cursor parameters and parameter`s mode.
    6. For TAB1 write cursor where COL2 = 'three' and then UPDATE TAB1.COL1 using 
    cursor. Explain it.
Solution:
    --1
    Implicit cursor declared and managed by PL/SQL for all DML and PL/SQL SELECT
    statements. Explicit cursor declared and managed by the programmer.
    --2
    OPEN - move pointer to first row in cursor, you must CLOSE cursor before reopen.
    FETCH - read data from current row and move pointer to the next row.
    --3
    DECLARE
        CURSOR cur IS 
            SELECT COL1, COL2 FROM TAB1;
        cur_row cur%ROWTYPE;
        cur_num NUMBER;
        cur_col2 TAB1.COL2%TYPE;
    BEGIN 
        IF NOT cur%ISOPEN THEN
            OPEN cur;
        END IF;
            LOOP 
                FETCH cur INTO cur_row;
                EXIT WHEN cur%NOTFOUND OR cur%ROWCOUNT > 2;
                    IF cur%FOUND THEN
                        DBMS_OUTPUT.PUT_LINE
                            ('loop1 '||cur_row.COL1||' '||cur_row.COL2);
                    END IF;                
            END LOOP;
        CLOSE cur;            
        OPEN cur;
            LOOP
                FETCH cur INTO cur_num, cur_col2;                   
                DBMS_OUTPUT.PUT_LINE('loop2 '||cur_num||' '||cur_col2);
                EXIT WHEN cur%NOTFOUND;
            END LOOP;           
        CLOSE cur;
    END;
    --4
    DECLARE
        CURSOR cur IS 
            SELECT COL1, COL2 FROM TAB1;
    BEGIN        
        FOR i IN cur LOOP
            DBMS_OUTPUT.PUT_LINE(i.COL1||' '||i.COL2);
        END LOOP;
        
        FOR i IN (SELECT COL1, COL2 FROM TAB1) LOOP
            DBMS_OUTPUT.PUT_LINE(i.COL1||' '||i.COL2);
        END LOOP;
    END;
    --5
    --The scope of cursor parameters is local to the cursor, meaning that they can be
    --referenced only within the query used in the cursor declaration
    DECLARE 
        CURSOR cur(p IN VARCHAR2 DEFAULT 'one') IS --only IN parameters allowed
        --CURSOR cur(p IN VARCHAR2 DEFAULT p); --will be error
            SELECT COL1, COL2 
                FROM TAB1
                    WHERE COL2 = p;
        p VARCHAR2(100) DEFAULT 'two';
        cur_row cur%ROWTYPE;
    BEGIN        
        OPEN cur(p => p);
        --OPEN cur();
        --OPEN cur;
            LOOP
                FETCH cur INTO cur_row;
                EXIT WHEN cur%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1||' '||cur_row.COL2);
            END LOOP;
        CLOSE cur;
        
        FOR i IN cur(p) LOOP
            DBMS_OUTPUT.PUT_LINE(i.COL1||' '||i.COL2);
        END LOOP;
    END;
    --6
    DECLARE
        CURSOR cur IS
            SELECT COL1, COL2 
                FROM TAB1
                    WHERE COL2 = 'three'
        FOR UPDATE;
    BEGIN
        FOR i IN cur LOOP
            UPDATE TAB1
                SET COL1 = 3
                    WHERE CURRENT OF cur; --update will be if TAB1.ROWID = cur.ROWID
        END LOOP;
        COMMIT;
    END;


CHAPTER 9. "Handling Exceptions"
Task 9.1. Exceptions.
    1. What mean exception? List exception categories. What about TCL commands in 
    exception`s section?
    2. Write block where define exception E1, then initiate this exception and display
    at DBMS_OUTPUT text 'E1'.
    3. Explain parameters of procedure RAISE_APPLICATION_ERROR. Write block with 
    variable VV = 2 and if VV > 1 then initiate this procedure not in EXCEPTION 
    section and write EXCEPTION section where handle this exception.
    4. Write block (with exception section) where declare exception MY_ERROR_0 and 
    bind to number (in comment write acceptable range of numbers) of system exception
    ORA-01476 "divisor is equal to zero". Then initiate this error and handle.
    5. Repeat step 4, but exception declare in package.
    6. Repeat step 4, but exception declare for demonstrate pass an exception from 
    nested block to parent block.
    7. How WHEN clause works in EXCEPTION section?
Solution:
    --1
    Exception is a situation fired at abnormal behavior of PLSQL program. 
    Categories: internally defined (unnamed system exception), predefined (named 
    system exception), user-defined exception.
    Commit, rollback, savepoint allowed in exception`s section.
    --2
    DECLARE
        E1 EXCEPTION;
    BEGIN
        RAISE E1;
    EXCEPTION
        WHEN E1
          THEN DBMS_OUTPUT.PUT_LINE('E1');
    END;
    --3
    RAISE_APPLICATION_ERROR(X, Y, Z). 
    X is number in range [-20999; -20000] (remember that in some Oracle packages 
    numbers in range [-20005; -20000] assigned to system exceptions). Y is a message
    with length <= 2048 bytes (symbols over this length will be ignored). Z is a
    boolean value: TRUE - adds an error to stack, FALSE (default) - it replaces the 
    existing error.
    DECLARE
        vv NUMBER := 2;
    BEGIN
        IF vv > 1 THEN
            RAISE_APPLICATION_ERROR(-20000, 'MyText');
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -20000 THEN
                NULL;
                DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
                RAISE_APPLICATION_ERROR(-20000, 'MyText2');
            END IF;
    END;
    --4
    DECLARE
        my_error_0 EXCEPTION
        PRAGMA EXCEPTION_INIT (my_error_0, -01476); --range: -1..-1000000
        x NUMBER;
    BEGIN
        x := 1 / 0;
    EXCEPTION
        WHEN my_error_0 THEN
            DBMS_OUTPUT.PUT_LINE('THIS IS my_error_0');
    END;    
    --5
    CREATE OR REPLACE PACKAGE MY_PCK_FOR_SYSTEM_ERRORS IS
        my_error_0 EXCEPTION;
        PRAGMA EXCEPTION_INIT (my_error_0, -01476);
    END;
    DECLARE 
        x NUMBER;
    BEGIN 
        x := 1 / 0;
    EXCEPTION
        WHEN MY_PCK_FOR_SYSTEM_ERRORS.my_error_0 THEN
            DBMS_OUTPUT.PUT_LINE(SQLCODE);
    END;
    --6
    BEGIN
        DECLARE
            my_error_0 EXCEPTION
            PRAGMA EXCEPTION_INIT (my_error_0, -01476);
            x NUMBER;    
        BEGIN
            BEGIN
                x := 1 / 0;
            EXCEPTION
                WHEN my_error_0 THEN
                    DBMS_OUTPUT.PUT_LINE('NestedBlock: '||SQLCODE);
                    RAISE;
            END;
        EXCEPTION
            --redundant exceptions do not allowed in one exception block
            --WHEN ZERO_DIVIDE THEN
            --    DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE detected');
            WHEN my_error_0 THEN
                DBMS_OUTPUT.PUT_LINE('my_error_0 visible');
            RAISE;
        END;
    EXCEPTION
        WHEN OTHERS THEN
        --WHEN my_error_0 THEN --will be fail, because name "my_error_0" not visible
            DBMS_OUTPUT.PUT_LINE(SQLERRM); --ORA-01476: divisor is equal to zero
    END;
    --7
    WHEN clause writes in EXCEPTION section and starts if name of initialized 
    exception matches with name of exception from WHEN clause. Only one first matched 
    WHEN clause can be activated. For not matched names of exceptions can use 
    WHEN OTHERS clause, that must be last of all WHEN clauses. If WHEN OTHERS is not 
    specified in the EXCEPTION section and in another WHEN clauses not matched names 
    of exceptions, then exception will be passed to next block or to calling 
    environment.

Task 9.2. Functions for EXCEPTION section.
    1. Make block where in EXCEPTION section have nested block with function that 
    returns code of last initiated exception.
    Which result of this function will be: in parent block, in nested block, 
    in EXCEPTION section of nested block, in EXCEPTION section of parent block.
    2. Make block with function that returns message binded to code of error. Which 
    length of this message? Write result for this function without argument (explain
    it), with argument = 1 and with argument = 0.
    3. Make block with function without arguments that returns message of current 
    error. Which length of this message?
    4. Write procedure and write block with function that returns text about 
    programm stack and rows numbers. Write the execution`s result of this block.
    5. Write procedure and write block with function that returns text about current
    call stack and rows numbers. Write the result of this block.    
Solution:
    --1
    DECLARE
        exc EXCEPTION;
    BEGIN
        --result = 0
        DBMS_OUTPUT.PUT_LINE('1. before exception = '||SQLCODE);
        RAISE exc;
    EXCEPTION
        WHEN exc THEN 
            --result = 1
            DBMS_OUTPUT.PUT_LINE('2. for exc before INNER block = '||SQLCODE);
            --Inner block
            BEGIN
                --result = 1
                DBMS_OUTPUT.PUT_LINE('3. for exc before INNER block = '||SQLCODE);
                RAISE NO_DATA_FOUND;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    --result = 100
                    DBMS_OUTPUT.PUT_LINE('4. for INNER block = '||SQLCODE);
            END;
            --result = 1
            DBMS_OUTPUT.PUT_LINE('5. for exc after INNER block = '||SQLCODE);
    END;
    --2
    --length <= 512 byte
    BEGIN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);--Without argument SQLERRM take argument from
                                      --SQLCODE, and if SQLCODE = 0, then SQLERRM
                                      --return ORA-0000: normal, successful completion
        DBMS_OUTPUT.PUT_LINE(SQLERRM(1)); --User-Defined Exception
        DBMS_OUTPUT.PUT_LINE(SQLERRM(0)); --ORA-0000: normal, successful completion
    END;
    --3
    --length <= 2000 byte. More preferable, than SQLERRM.
    BEGIN
        RAISE NO_DATA_FOUND;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE
                (DBMS_UTILITY.FORMAT_ERROR_STACK);--ORA-01403: no data found
    END;
    --4
    --Result:
    --ORA-06512: at "HR.PRC_WITH_ERR", line 4
    --ORA-06512: at line 2
    CREATE OR REPLACE PROCEDURE PRC_WITH_ERR IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('This is my PRC_WITH_ERR');
        RAISE NO_DATA_FOUND; --ORA-06512: at "HR.PRC_WITH_ERR", line 4
    END;    
    BEGIN
        PRC_WITH_ERR; --ORA-06512: at line 2
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    END;
    --5
    --Result:
    ----- PL/SQL Call Stack -----
    -- object           line    object
    -- handle           number  name
    -- 00007FF7C8AEEF88 6       procedure HR.PRC_WITH_ERR
    -- 00007FF7CBACE178 2       anonymous block
    CREATE OR REPLACE PROCEDURE PRC_WITH_ERR IS
    BEGIN
        RAISE NO_DATA_FOUND;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK); --line 6
    END;    
    BEGIN
        PRC_WITH_ERR; --line 2
    EXCEPTION --exception not will be performed
        WHEN NO_DATA_FOUND THEN 
            DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE);
    END;

Task 9.3. Functions for EXCEPTION section. Part 2.
    Do next operations:
        CREATE OR REPLACE PROCEDURE PRC_WITH_ERR IS
        BEGIN
            RAISE NO_DATA_FOUND;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('--**************************');
                DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
                DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
            RAISE;
        END;
        BEGIN
            DBMS_OUTPUT.PUT_LINE('--**************************');
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
            PRC_WITH_ERR;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('--**************************');
                DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
                DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        END;
    Then do next:
    1. What will be the result in DBMS_OUTPUT after executing the BEGIN...END block?
Solution:
    --1
    --**************************
    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    00007FF89C6C5B80         3  anonymous block

    NULL --this is DBMS_UTILITY.FORMAT_ERROR_BACKTRACE between BEGIN and EXCEPTION
    --**************************
    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    00007FF8AC816888         7  procedure HR.PRC_WITH_ERR
    00007FF89C6C5B80         5  anonymous block

    ORA-06512: at "HR.PRC_WITH_ERR", line 3

    --**************************
    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    00007FF89C6C5B80         9  anonymous block

    ORA-06512: at "HR.PRC_WITH_ERR", line 9
    ORA-06512: at "HR.PRC_WITH_ERR", line 3
    ORA-06512: at line 5


CHAPTER 10. "Creating Procedure"
Task 10.1. Creating Procedure.
    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. What variables cannot be used in a procedure?
    2. How run procedure. Three ways.
    3. What modes of parameters we can use in procedure.
    4. Which object contain info about compiling errors. Which object contain info
    about parameters for procedures to which you have access.
    5. Create procedure which updates TAB1.COL1 with 2 input parameters:
    - first parameter must be integer DEFAULT 3 and will be used for 'SET COL1';
    - second parameter must be string DEFAULT 'three' and will be used for WHERE COL2.
    How call this procedure writing manually values for both parameters (2 notations).
    How call this procedure only with default values for both parameters (two ways).
    How call this procedure only with default value for first parameter.
    6. Create procedure with nested block where must be two DBMS_OUTPUT, but exit 
    from procedure after first DBMS_OUTPUT.
    7. How to drop procedure? Which priviligies you must have for drop procedure if 
    you owner.
Solution:
    --1
    --SUBSTITUTION and HOST variables not allowed in procedure. But we can use it
    --instead parameters when calling procedure with parameters.
    EXECUTE my_procedure(&my_param);
    --2
    EXECUTE my_procedure;
    EXEC my_procedure;
    BEGIN
        my_procedure;
    END;
    --3
    INOUTIN OUT
    --4
    SELECT * FROM USER_ERRORS
    SELECT * FROM ALL_ARGUMENTS
    --5
    CREATE OR REPLACE PROCEDURE PRC(x IN TAB1.COL1%TYPE DEFAULT 3,
                                    y TAB1.COL2%TYPE := 'three') IS
    BEGIN
        UPDATE TAB1
            SET COL1 = x
                WHERE COL2 = y;
        COMMIT;
    END;
    --manually values for both parameters
    BEGIN
        PRC(3, 'three');
        PRC(y => 'three', x => 3);
    END;
    --default values for both parameters
    BEGIN
        PRC;
        PRC();
    END;
    --default value for first parameter
    BEGIN
        PRC(y => 'three');
    END;
    --6
    CREATE OR REPLACE PROCEDURE PRC IS
    BEGIN
        BEGIN
            DBMS_OUTPUT.PUT_LINE(1);
            RETURN;
            DBMS_OUTPUT.PUT_LINE(2);
        END;
    END;    
    --7
    --if you owner you must have CREATE PROCEDURE privs
    DROP PROCEDURE PRC;
    
Task 10.2. Creating Procedure. Output, input-output parameters.
    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. Create procedure that saves value into output parameter from TAB1.COL1 where 
    COL2 = input parameter:
    - first parameter must be input string DEFAULT 'two' and will be used for COL2;
    - second parameter must be output integer and store value from COL1.
    Which DEFAULT value allow for output parameter?    
    Call this procedure with input parameter 'two' and display value from output 
    parameter: 
    - using bind variable
    - without bind variable (three notations: positional, named and mixed).
    2. Make point 1 again, but use one input-output parameter.
Solution:
    --1
    --DEFAULT value not allowed for output parameter
    CREATE OR REPLACE PROCEDURE PRC(pin IN TAB1.COL2%TYPE DEFAULT 'two',
                                    pout OUT TAB1.COL1%TYPEIS
    BEGIN
        SELECT COL1
          INTO pout
            FROM TAB1
                WHERE COL2 = pin;
        DBMS_OUTPUT.PUT_LINE('OUT = '||pout);
    END;
    --with bind
    VARIABLE vin VARCHAR2(100);
    VARIABLE vout NUMBER;
    EXECUTE :vin := 'two';
    EXEC PRC(:vin, :vout);
    PRINT vin vout;
    --without bind
    DECLARE
        res NUMBER;
    BEGIN
        --positional notation
        PRC('two', res);
        --named notation (preferable)
        PRC(pout => res);
        --mixed notation
        PRC('two', pout => res); --positional notation must be before named
        DBMS_OUTPUT.PUT_LINE(res);
    END;
    --2
    --DEFAULT value not allowed for input-output parameter
    CREATE OR REPLACE PROCEDURE PRC(p IN OUT VARCHAR2IS
    BEGIN
        SELECT COL1
          INTO p
            FROM TAB1
                WHERE COL2 = p;
        DBMS_OUTPUT.PUT_LINE('IN OUT = '||p);
    END;
    --with bind
    VARIABLE io VARCHAR2(100);
    EXECUTE :io := 'two';
    EXEC PRC(:io);
    PRINT io;
    --without bind
    DECLARE
        res VARCHAR2(100) := 'two';
    BEGIN
        PRC(res);
        res := 'two';
        PRC(p => res);
        DBMS_OUTPUT.PUT_LINE(res);
    END;
            
Task 10.3. Creating Procedure. 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. Create procedure with 1 parameter which can take TRUE or FALSE (with default
    TRUE). Also if parameter = TRUE then display 'TRUE', if FALSE then display 'FALSE'
    and otherwise display 'IS NULL'. Write block where call this procedure with:
    parameter = FALSE; parameter equal variable that have FALSE; parameter equals 
    variable assigned TRUE result of expression.    
    2. Create procedure with 1 parameter with type row from TAB1. This procedure
    must be add row from parameter to TAB1. Then make block with next operations:
    - add row with manually values;
    - add all rows from TAB1;
Solution:
    --1
    CREATE OR REPLACE PROCEDURE PRC(x BOOLEAN := TRUEIS
    BEGIN
        IF x THEN
            DBMS_OUTPUT.PUT_LINE('TRUE');
        ELSIF NOT x THEN
            DBMS_OUTPUT.PUT_LINE('FALSE');
        ELSE
            DBMS_OUTPUT.PUT_LINE('IS NULL');
        END IF;
    END;
    --
    DECLARE 
        boo BOOLEAN := FALSE;
    BEGIN
        PRC(FALSE);
        PRC(boo);
        boo := 1 = 1;
        PRC(boo);
    END;
    --2
    CREATE OR REPLACE PROCEDURE PRC(x TAB1%ROWTYPEIS    
    BEGIN
        INSERT INTO TAB1 VALUES x;
        COMMIT;
    END;
    --
    DECLARE
        rec TAB1%ROWTYPE;
    BEGIN
        --manually
        rec.COL1 := 0;
        rec.COL2 := 'zero';
        PRC(rec);
        --from TAB1
        FOR i IN (SELECT COL1, COL2 FROM TAB1) LOOP
            PRC(i);
        END LOOP;        
    END;
    
    
CHAPTER 11. "Creating Function"
Task 11.1. Creating Function. 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. What variables cannot be used in a function?
    2. How call TO_CHAR function (four ways)? 
    3. What modes of arguments we can use in function? Which modes of arguments 
    allowed: in SELECT clause and in blocks?
    4. Which object contain info about compiling errors? Which object contain info
    about arguments for functions to which you have access.
    5. Create function with one input argument, that will be return value from
    TAB1.COL1 where COL2 = input argument. Display result for COL2 = 'two'.
    6. Run function from point 5 where input argument = 'abc' and explain result:
    using anonymous block, using SELECT clause.
    7. Which precedence between stored function and function inside block with same 
    name?
    Make all actions from point 5, but create function inside block and it must be 
    returned result = result - 100. Assign result of inside function into 
    variable 'v' and display this value. Also display result for stored function.
    8. How remove function?
Solution:
    --1
    --SUBSTITUTION and HOST variables not allowed in function. But we can use it
    --instead arguments when calling function with arguments:
    SELECT TO_CHAR(&param) FROM DUAL
    --2
    EXECUTE DBMS_OUTPUT.PUT_LINE(TO_CHAR(11));
    --
    VARIABLE x VARCHAR2(20)
    EXEC :x := TO_CHAR(12)
    PRINT x;
    --
    BEGIN 
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(13));
    END;
    --
    SELECT TO_CHAR(14) FROM DUAL
    --3
    --INOUTIN OUT
    --In SELECT clause allowed only IN arguments.
    SELECT TO_CHAR(1) FROM DUAL;
    --In block and subprograms allowed all.
    DECLARE
        x VARCHAR2(10);
    BEGIN
        x := TO_CHAR(1);
        DBMS_OUTPUT.PUT_LINE(x);
    END;
    --4
    SELECT * FROM USER_ERRORS
    SELECT * FROM ALL_ARGUMENTS
    --5
    CREATE OR REPLACE FUNCTION FNC(x IN VARCHAR2RETURN NUMBER IS
        res NUMBER := 0;
    BEGIN
        SELECT COL1 
          INTO res
            FROM TAB1
                WHERE COL2 = x;
        RETURN res;
    END;
    SELECT FNC('two') FROM DUAL;
    --6
    --in block will be error
    BEGIN
        DBMS_OUTPUT.PUT_LINE(FNC('abc'));
    END;
    --in SELECT will be NULL
    SELECT FNC('abc') FROM DUAL;
    --7
    --Function inside block have precedence over stored function with same name
    DECLARE
        v NUMBER;
        FUNCTION FNC(x IN VARCHAR2RETURN NUMBER IS
            res NUMBER := 0;
        BEGIN
            SELECT COL1 
              INTO res
                FROM TAB1
                    WHERE COL2 = x;
            RETURN res - 100;
        END;           
    BEGIN   
        v := FNC('two');
        DBMS_OUTPUT.PUT_LINE(v);
        --for calling stored function need specify name of schema, for example:
        --DBMS_OUTPUT.PUT_LINE(HR.FNC('two'));
    END;
    --8
    DROP FUNCTION FNC;
    
Task 11.2. Creating Function. Part 2.
    1. Where can be used user-defined functions?
    2. What restrictions for user-defined functions that are callable from SQL 
    expressions?
    3. Where user-defined functions can`t be used?
    4. What restrictions for functions calling from SQL statements: SELECTUPDATE,
    DELETE?
Solution:
    --1
    - The SELECT list or clause of a query;
    - Conditional expressions of the WHERE and HAVING clauses;
    - The CONNECT BYSTART WITHORDER BY and GROUP BY clauses of query;
    - The VALUES clause of the INSERT statement;
    - The SET clause of the UPDATE statement.
    --2
    User-defined functions that are callable from SQL expressions must:
    - Be stored in the database (not as local function from package);
    - Accept only IN parameters with valid SQL data types, not PL/SQL-specific types
    (record, table, boolean);
    - Return valid SQL data types, not PL/SQL-specific types;
    - You must own the function or have the EXECUTE privilege.
    --3
    Functions can`t be used:
    - in CHECK constraint;
    - as DEFAULT value for a column.
    --4
    Functions called from:
    - a SELECT statement can not contain DML statements;
    - an UPDATE or DELETE statement on a table MY_TABLE cannot query or contain DML
    on the same table MY_TABLE;
    - SQL statements cannot end transactions. It is mean, that function cannot 
    contain COMMIT or ROLLBACK operations.
    
    
CHAPTER 12. "Packages"
Task 12.1. Creating Package.
    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 PROCEDURE PRC(p VARCHAR2IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PRC parameter is '||p);
        END;
        --
        CREATE OR REPLACE FUNCTION FNC(f VARCHAR2RETURN NUMBER IS
            res NUMBER;
        BEGIN
            res := LENGTH(f);
            RETURN res;
        END;
    Then do next:
    1. What may be declared in:
    - package`s specification;
    - package`s body?    
    2. Create package 'PCK' with objects:
    - procedure 'PRC' with string parameter;
    - function 'FNC' with string argument and result must be NUMBER;
    - integer variable 'v';
    - string constant 'c' with value 'const';
    - cursor 'CUR' for 'SELECT COL2 FROM TAB1' (two ways).
    Then make block where:
    - call with parameter 'one' procedure and function;
    - assign to 'v' value 11 and display this varible;
    - display value of 'c';
    - display values of cursor (two ways).
Solution:
    --1
    --in package`s specification
    Types, variables, constants, exceptions, cursors and subprograms.
    --in package`s body
    Queries for the cursors, the code for the subprograms.
    --2
    --Package specification
    CREATE OR REPLACE PACKAGE PCK IS
        PROCEDURE PRC(p VARCHAR2);
            FUNCTION FNC(f VARCHAR2RETURN NUMBER;
                v PLS_INTEGER;
                    c CONSTANT VARCHAR2(100) := 'const';
                        TYPE rowt IS RECORD(C2 TAB1.COL2%TYPE);
                        CURSOR cur RETURN rowt;
                            CURSOR cur2 IS SELECT '2'||COL2 CC2 FROM TAB1;
    END;
    --Package body
    CREATE OR REPLACE PACKAGE BODY PCK IS
        CURSOR cur RETURN rowt IS
            SELECT COL2 FROM TAB1;  
        --
        PROCEDURE PRC(p VARCHAR2IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE('PRC parameter is '||p);
        END;
        --
        FUNCTION FNC(f VARCHAR2RETURN NUMBER IS
            res NUMBER;
        BEGIN
            res := LENGTH(f);
            RETURN res;
        END;      
    END;
    --anonymous block
    DECLARE
        v_cur PCK.ROWT;
    BEGIN
        PCK.PRC('one');
        --
        DBMS_OUTPUT.PUT_LINE(PCK.FNC('one'));
        --
        PCK.V := 11;
        DBMS_OUTPUT.PUT_LINE(PCK.V);
        --
        DBMS_OUTPUT.PUT_LINE(PCK.C);
        --
        OPEN PCK.CUR;
            LOOP
                FETCH PCK.CUR INTO v_cur;
                    EXIT WHEN PCK.CUR%NOTFOUND;
                    DBMS_OUTPUT.PUT_LINE(v_cur.C2);
            END LOOP;
        CLOSE PCK.CUR;
        --
        FOR i IN PCK.CUR2 LOOP
            DBMS_OUTPUT.PUT_LINE(i.CC2);
        END LOOP;
    END;
    
Task 12.2. The visibility of a package`s components.
    1. Which parts of package visible and hidden to user?
    2. Create package with number variable 's' (default 11) in the specification and 
    number variable 'b' (default 22) in package body. Also write code in package 
    that, if we first call package in session or first call package after compiling,
    displays once in DBMS_OUTPUT text 'package'. Then in package create procedure 
    'PRC', that will be displayed in DBMS_OUTPUT default values from this variables. 
    Indicate which variable is visible outside package and inside package. Call this 
    procedure, write result.
    3. Which value will be in variables with same name in package specification,
    package body and declarative section of procedure in package body?
    4. Explain when we need recompile: package specification, package body.
    5. How to remove package spec and body together? How to remove only package body?
Solution:
    --1
    - Only the declarations in the package specification are visible.
    - Private constructs in the package body are hidden and not visible outside the 
    package body.
    - All coding is hidden in the package body.
    --2
    CREATE OR REPLACE PACKAGE PCK IS
        s NUMBER := 11; --available outside package
        PROCEDURE PRC;
    END;
    --
    CREATE OR REPLACE PACKAGE BODY PCK IS
        b NUMBER := 22; --available inside package body
        PROCEDURE PRC IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE(s);
            DBMS_OUTPUT.PUT_LINE(b);
        END;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('package');
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20009,'Error in package body');
    END;
    --
    --result 11 22. Also if first running then firstly we`ll see  text 'package'
    EXEC PCK.PRC;
    --3
    If we try use variables with same name that will be error. But if we try use
    variables with same name in declarative section in procedure inside package body,
    then no will be error, variable in procedure will have precedence over variable 
    from package specification.
    --4
    Package spec need recompile if changed objects from package body listed in 
    package specification.
    Package body need recompile if package specification contains changes, that must 
    be list in package body.
    --5
    DROP PACKAGE PCK;
    DROP PACKAGE BODY PCK;


CHAPTER 13. "Working with Packages"
Task 13.1. Working with Packages. Overloading. Part 1.
    1. In what object we can use overloading and for which objects?
    2. Demonstrate and explain overloading procedures.
    3. Demonstrate and explain overloading functions.
Solution:
    --1
    We can overload nested subprograms, package subprograms, and type methods. 
    We can use the same name for several different subprograms if their formal 
    parameters differ in name, number, order, or data type family.
    PL/SQL looks for matching numeric parameters in this order:
    1. PLS_INTEGER (or BINARY_INTEGER, an identical data type)
    2. NUMBER
    3. BINARY_FLOAT
    4. BINARY_DOUBLE
    --2
    CREATE OR REPLACE PACKAGE PCK IS
        --procedures must have:
        --1) different quantity of parameters
        PROCEDURE PRC(x NUMBER, y VARCHAR2, z DATE);
        PROCEDURE PRC(x NUMBER, y VARCHAR2);
        --2) or different types of parameters
        PROCEDURE PRC(x VARCHAR2, y NUMBER);
        --3) or different names of parameters, but must use named notation when call
        PROCEDURE PRC(x VARCHAR2);
        PROCEDURE PRC(xx VARCHAR2);
        --4) or different order with same names of parameters, but must use 
        --positional notation when call
        PROCEDURE PRC2(a NUMBER, b VARCHAR2);
        PROCEDURE PRC2(b VARCHAR2, a NUMBER);
        --But if we add next 5th procedure, will cause error when will be executed,
        --because types of parameters from one data type family not allowed.
        --PROCEDURE PRC(x VARCHAR2, y INTEGER);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PRC(x NUMBER, y VARCHAR2, z DATEIS        
        BEGIN
            DBMS_OUTPUT.PUT_LINE('xyz: '||x||y||z);
        END;
        --
        PROCEDURE PRC(x NUMBER, y VARCHAR2IS        
        BEGIN
            DBMS_OUTPUT.PUT_LINE('xy: '||x||y);
        END;
            --
            PROCEDURE PRC(x VARCHAR2, y NUMBERIS        
            BEGIN
                DBMS_OUTPUT.PUT_LINE('xy: '||x||y);    
            END;
                --
                PROCEDURE PRC(x VARCHAR2IS
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('x: '||x);
                END;        
                --
                PROCEDURE PRC(xx VARCHAR2IS
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('xx: '||xx);
                END;
                    --
                    PROCEDURE PRC2(a NUMBER, b VARCHAR2IS
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('ab: '||a||b);
                    END;
                    --
                    PROCEDURE PRC2(b VARCHAR2, a NUMBERIS
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('ba: '||b||a);
                    END;
    END;
    BEGIN
        PCK.PRC(1,'one',TO_DATE('01.02.2000','DD.MM.YYYY'));
        PCK.PRC(1,'one');
            PCK.PRC('one',1);
                PCK.PRC(x => 'one');
                PCK.PRC(xx => 'one');
                    PCK.PRC2(0, 'zero');
                    PCK.PRC2('zero', 0);
    END;
    --3
    --Functions have same rules as for procedures. And remember that RETURN TYPE not
    --influence. For example:
    CREATE OR REPLACE PACKAGE PCK IS
        --different types of arguments
        FUNCTION FNC(x VARCHAR2RETURN NUMBER;
        FUNCTION FNC(x NUMBERRETURN NUMBER;
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        FUNCTION FNC(x VARCHAR2RETURN NUMBER IS
            res NUMBER;
        BEGIN
            res := x;
            RETURN res;
        END;
        --
        FUNCTION FNC(x NUMBERRETURN NUMBER IS
            res NUMBER;
        BEGIN
            res := x;
            RETURN res;
        END;
    END;
    SELECT PCK.FNC('11') FROM DUAL;
    SELECT PCK.FNC(1) FROM DUAL;

Task 13.2. Working with Packages. Overloading. Part 2.
    1. Demonstrate overloading TO_CHAR function.
    2. Demonstrate situation: procedure and function have same name in package.
    How execute procedure and function and which result will be.
Solution:
    --1
    CREATE OR REPLACE PACKAGE PCK IS
        FUNCTION TO_CHAR(x NUMBER, y VARCHAR2RETURN VARCHAR2;
        PROCEDURE DISPLAY;
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        FUNCTION TO_CHAR(x NUMBER, y VARCHAR2RETURN VARCHAR2 IS            
        BEGIN
            RETURN x||y;
        END;
        PROCEDURE DISPLAY IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(1, 'one'));
            DBMS_OUTPUT.PUT_LINE(STANDARD.TO_CHAR('one'));
        END;
    END;
    EXEC PCK.DISPLAY;
    --2
    CREATE OR REPLACE PACKAGE PCK IS
        PROCEDURE PF(x NUMBER);
        FUNCTION PF(x NUMBERRETURN NUMBER;
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PF(x NUMBERIS
        BEGIN
            DBMS_OUTPUT.PUT_LINE(x + 22);
        END;
        FUNCTION PF(x NUMBERRETURN NUMBER IS
            res NUMBER := 0;
        BEGIN
            res := x + res + 33;
            RETURN res;
        END;
    END;
    --When we try execute PF how procedure - result will be from procedure.
    EXEC PCK.PF(1);
    --When we try execute PF how function - result will be from function.
    SELECT PCK.PF('1') FROM DUAL

Task 13.3. Forward declaration in package.
    1. For which objects in package we can use forward declaration? How we must 
    declare another objects in package?
    2. Demonstrate forward declaration in package using 1 variable, 1 function and 1 
    procedure. Explain it.
Solution:
    --1
    --We can use it for procedures and functions declared in package body. Another
    --objects such as variables, constants, types, cursors must be declared in 
    --package body before all procedures and functions.
    --2
    --For using package body`s function in package`s procedure we must declare 
    --function in package body above procedure. But we can declare function 
    --specification in package body upstairs and then we paste text of function in 
    --anywhere downstairs to package body. After this we can use this function in 
    --procedure even if function below to procedure.
    CREATE OR REPLACE PACKAGE PCK IS
        PROCEDURE PRC(x VARCHAR2);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        FUNCTION FNC(x VARCHAR2RETURN VARCHAR2;
        v VARCHAR2(1) := '+';
        --
        PROCEDURE PRC(x VARCHAR2IS
            a VARCHAR2(100);
        BEGIN
            a := FNC(x);
            DBMS_OUTPUT.PUT_LINE(a);
        END;
        --
        FUNCTION FNC(x VARCHAR2RETURN VARCHAR2 IS
            res VARCHAR2(100);
        BEGIN
            res := x||v;
            RETURN res;
        END;
    END;
    EXEC PCK.PRC('abc');
    
Task 13.4. Persistent State of packages.
    1. What mean package state?
    2. What mean stateful and stateless package?
    3. Demonstrate persistent state of package 'PCK' using global variable 'vv' in 
    this package and procedure 'PRC'.
    4. Remake point 3 and demonstrate alternative behaviour of persistent state.
Solution:
    --1
    The values of the variables, constants, and cursors that a package declares (in 
    either its specification or body) comprise its package state.
    --2
    If a PL/SQL package declares at least one variable, constant, or cursor, then 
    the package is stateful; otherwise, it is stateless.
    --3
    --Global variable in package it is variable that defined in package 
    --specification.
    CREATE OR REPLACE PACKAGE PCK IS
        vv NUMBER := 1;
        PROCEDURE PRC(x NUMBER);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PRC(x NUMBERIS
        BEGIN
            vv := x;
            DBMS_OUTPUT.PUT_LINE(vv);
        END;
    END;
    BEGIN
        --1) Package state initialized when the package is first loaded.
        --Result = 1
        DBMS_OUTPUT.PUT_LINE(PCK.vv);
        --2) Result of vv after procedure will be 100 and will be persistent (by 
        --default) for the life of the session: stored in the UGA (USER GLOBAL AREA);
        --unique for each session and can be changed by our procedure or another way
        --in this session.
        PCK.PRC(100);
        /* another way of change is: PCK.vv := 100; */
    END;
    BEGIN
        --3) and now for session vv = 100, not 1.
        DBMS_OUTPUT.PUT_LINE(PCK.vv);
    END;
    --4
    CREATE OR REPLACE PACKAGE PCK IS
        PRAGMA SERIALLY_REUSABLE;
            vv NUMBER := 1;
            PROCEDURE PRC(x NUMBER);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PRAGMA SERIALLY_REUSABLE;
            PROCEDURE PRC(x NUMBERIS
            BEGIN
                vv := x;
                DBMS_OUTPUT.PUT_LINE(vv);
            END;
    END;
    BEGIN
        --1) Value of vv = 1
        DBMS_OUTPUT.PUT_LINE(PCK.vv);
        --2) value of vv after procedure will be 100 and will be persistent only for
        --the duration of one call to the server (in parent, current, nested blocks)
        PCK.PRC(100);
        /* another way of change is: PCK.vv := 100; */
        DBMS_OUTPUT.PUT_LINE(PCK.vv);
    END;
    BEGIN
        --3) In another block vv will be again 1.
        DBMS_OUTPUT.PUT_LINE(PCK.vv);
    END;

Task 13.5. Persistent State of packages. Cursor.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(COL1 NUMBER);
        INSERT INTO TAB1(COL1) VALUES(1);
        INSERT INTO TAB1(COL1) VALUES(2);
        INSERT INTO TAB1(COL1) VALUES(3);
        INSERT INTO TAB1(COL1) VALUES(4);
        INSERT INTO TAB1(COL1) VALUES(5);
        COMMIT;
    Then do next:
    1. Demonstrate persistent state for global cursor 'cur' in package 'PCK' using 
    TAB1. Explain it.
Solution:
    --1
    CREATE OR REPLACE PACKAGE PCK IS
        CURSOR cur IS SELECT COL1 
                          FROM TAB1;
        PROCEDURE OPEN;
        PROCEDURE CLOSE;
        PROCEDURE LOOP_CUR;
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE OPEN IS
        BEGIN
            IF NOT cur%ISOPEN THEN
                OPEN cur;
            END IF;
        END;
        --
        PROCEDURE CLOSE IS
        BEGIN
            IF cur%ISOPEN THEN
                CLOSE cur;
            END IF;
        END;
        --
        PROCEDURE LOOP_CUR IS
            cur_row cur%ROWTYPE;
        BEGIN
            FOR i IN 1..2 LOOP
                FETCH cur INTO cur_row;
                IF cur%NOTFOUND THEN
                    CLOSE cur;
                    EXIT;
                END IF;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1);
            END LOOP;
        END;
    END;
    --Now if run next block we retrive 2 rows from cursor with values: 1, 2. This 
    --cursor will be stored in memory and if we run block again we retrive next
    --rows with values: 3, 4. Etc.
    BEGIN
        PCK.OPEN;
        PCK.LOOP_CUR;
    END;
    --But if we close cursor, it is mean that cursor will be removed from memory and
    --if we run previous block we begining at first row.
    BEGIN
        PCK.CLOSE;
    END;    

Task 13.6. Using pl/sql tables in packages.
    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(NULL, 'two');
        INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
        COMMIT;
    Then do next:
    1. Create package with type 'tt' as associative array and procedure 'PRC' with 
    output parameter 'param' of this type which places rows from table TAB1 into 
    parameter. The array should look like this:
    INDEX COL1 COL2 
    1     1    'one'
    2     NULL 'two'
    3     NULL 'three'
    Then create block where call this procedure and display values of COL2 from 
    output parameter.
Solution:
    --1
    CREATE OR REPLACE PACKAGE PCK IS
        TYPE tt IS TABLE OF TAB1%ROWTYPE
            INDEX BY PLS_INTEGER;
        PROCEDURE PRC(param OUT tt);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE PRC(param OUT tt) IS
            cnt NUMBER := 0;
        BEGIN
            FOR i IN (SELECT COL1, COL2 FROM TAB1) LOOP
                cnt := cnt + 1;
                param(NVL(i.COL1, cnt)) := i;
            END LOOP;
        END;
    END;
    --
    DECLARE
        res PCK.tt;
    BEGIN
        PCK.PRC(res);
        FOR i IN 1..res.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(res(i).COL2);
        END LOOP;
    END;


CHAPTER 14. "Using oracle-supplied Packages"
Task 14.1. DBMS_OUTPUT.
    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. Which minimum and maximum buffer size? Which default buffer size? What type
    of buffer size parameter and minimum and maximum values of this parameter.
    2. Place in buffer text "my" (two syntax).
    3. Place in buffer all values between 1 - 7000  using loop.
    4. Write block with 5 operations: place in buffer number 1, then switch off 
    DBMS_OUTPUT, then place in buffer number 2, then turn on DBMS_OUTPUT and finally
    place in buffer number 3. What will be in buffer?
    5. Without concatenation place in buffer at first row 'a','b'. Then using 
    concatenation in next row place 'c','d'.
    6. Place in buffer string 'a'. Place in buffer string 'b'. Then retrieve first 
    string from buffer to variable. Write result and explain.
    7. Place in buffer string 'a'. Place in buffer string 'b'. Then retrieve all
    strings from buffer to variable. Display result from variable and explain.
Solution:
    --1
    Minimum is 2 000 bytes. Maximum is unlimited. Default is 20 000.
    Type is INTEGER. Min. value is 2 000, max. value is 1 000 000.
    --2
    BEGIN
        /* 1 */ DBMS_OUTPUT.PUT('my'); DBMS_OUTPUT.NEW_LINE;
        /* 2 */ DBMS_OUTPUT.PUT_LINE('my');
    END;
    --3
    BEGIN
        DBMS_OUTPUT.ENABLE(50000); --DBMS_OUTPUT.ENABLE(-50000);
        FOR i IN 1..7000 LOOP            
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    END;
    --4
    BEGIN
        DBMS_OUTPUT.PUT_LINE(1);
        DBMS_OUTPUT.DISABLE; --this will clear buffer and switch off DBMS_OUTPUT
        DBMS_OUTPUT.PUT_LINE(2);
        DBMS_OUTPUT.ENABLE;
        DBMS_OUTPUT.PUT_LINE(3); --only this value will be placed in buffer
    END;
    --5
    BEGIN
        DBMS_OUTPUT.PUT('a'); DBMS_OUTPUT.PUT('b');
        DBMS_OUTPUT.NEW_LINE;
        DBMS_OUTPUT.PUT_LINE('c'||'d');
    END;
    --6
    DECLARE
        buffer VARCHAR2(100);
        status INTEGER;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('a');
        DBMS_OUTPUT.PUT_LINE('b');
        DBMS_OUTPUT.GET_LINE(buffer, status); --retrieves first line 'a' from buffer
        DBMS_OUTPUT.PUT_LINE('Buffer = '||buffer);
        --if success then status retrive 0, otherwise 1 (for example buffer=NULL).
        DBMS_OUTPUT.PUT_LINE('Status = '||status); 
    END;
    --7
    DECLARE
        --also this array can be TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER
        buffer DBMS_OUTPUT.CHARARR;        
        lines NUMBER := 2;
    BEGIN
       DBMS_OUTPUT.PUT_LINE('a');
       DBMS_OUTPUT.PUT_LINE('b');
       DBMS_OUTPUT.GET_LINES(buffer, lines); --first arg - OUT, second arg is IN OUT
       DBMS_OUTPUT.PUT_LINE(buffer(1));
       DBMS_OUTPUT.PUT_LINE(buffer(2));
       DBMS_OUTPUT.PUT_LINE(lines);
    END;

Task 14.2. UTL_FILE.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(5)     DEFAULT 0, 
                           COL2 VARCHAR2(100) NOT NULL);
        Create on disk (for example D) file with path 'D:\ex\file.txt' with data:
        1 one
        two
        3        
    Then do next:
    1. Which actions need to do before using UTL_FILE capabilities?
    2. Make block where read and display all rows from 'file.txt'.
    3. Make block where create 'file2.txt' using code and write all rows from TAB1 
    into 'file2.txt'. Then add row 'new' into 'file2.txt'.
    4. How to see list of directories in database? List first 7 UTL_FILE-exceptions.
Solution:
    --1
    --Open SQLPLUS.
    --Connect and paste password
    SYS AS SYSDBA
    --Then check plugable database if we have Oracle 12c.
    SHOW CON_NAME
    --and connect to plugable database
    ALTER SESSION SET CONTAINER=orclpdb
    /
    --Then create directory DIR as 'D:\ex' and grant privs
    CREATE DIRECTORY DIR AS 'D:\ex'
    /
    GRANT READWRITE ON DIRECTORY DIR TO PUBLIC
    /
    --2
    DECLARE
        v_file UTL_FILE.FILE_TYPE;
        v_dir VARCHAR2(100) := 'DIR';
        v_fname VARCHAR2(100) := 'file.txt';
        v_text VARCHAR2(32767);
    BEGIN
        --open file for reading
        v_file := UTL_FILE.FOPEN(v_dir, v_fname, 'r');
        --read file and display first line
        BEGIN
            LOOP
                UTL_FILE.GET_LINE(v_file, v_text);
                DBMS_OUTPUT.PUT_LINE(v_text);
            END LOOP;
            EXCEPTION 
                WHEN NO_DATA_FOUND THEN
                    NULL;
        END;
        --close file
        UTL_FILE.FCLOSE(v_file);
    END;
    --3
    DECLARE
        v_file UTL_FILE.FILE_TYPE;
        v_dir VARCHAR2(100) := 'DIR';
        v_fname VARCHAR2(100) := 'file2.txt';
    BEGIN
        --3.1. If file 'file2.txt' will be exists, then file will be cleared.
        --open file for writing
        v_file := UTL_FILE.FOPEN(v_dir, v_fname, 'w');
        --loading data from TAB1
        FOR i IN (SELECT * FROM TAB1) LOOP
            UTL_FILE.PUT_LINE(v_file, i.COL1||i.COL2);
        END LOOP;
        --close file
        UTL_FILE.FCLOSE(v_file);
            --3.2. Also we can add row into file without clearing.
            --open file for writing
            v_file := UTL_FILE.FOPEN(v_dir, v_fname, 'a');
            --adding new row
            UTL_FILE.PUT_LINE(v_file, 'new');
            --close file
            UTL_FILE.FCLOSE(v_file);
    END;
    --4
    SELECT * FROM ALL_DIRECTORIES
    --
    INVALID_PATH       - File location is invalid.
    INVALID_MODE       - The open_mode parameter in FOPEN is invalid.
    INVALID_FILEHANDLE - File handle is invalid.
    INVALID_OPERATION  - File could not be opened or operated on as requested.
    READ_ERROR         - Operating system error occurred during the read operation.
    WRITE_ERROR        - Operating system error occurred during the write operation.    
    INTERNAL_ERROR     - Unspecified PL/SQL error

Task 14.3. UTL_MAIL.
    1. When we use UTL_MAIL and which actions need to do before using UTL_MAIL 
    capabilities?
    2. Write block where send text email.
    3. Write block where send email with attachment image.
    4. Write block where send email with attachment text file.
Solution:
    --1
    --We use UTL_MALE for send messages.
    --It is requires the setting of the SMTP_OUT_SERVER database initialization
    --parameter.
    --Also DBA must make:
    -- 1) Install UTL_MAIL package
    -- @$ORACLE_HOME/rdbms/admin/utlmail.sql
    -- @$ORACLE_HOME/rdbms/admin/prvtmail.plb
    -- For example:
    -- @c:\app\myu\product\12.2.0\dbhome_1\rdbms\admin\utlmail.sql
    -- @c:\app\myu\product\12.2.0\dbhome_1\rdbms\admin\prvtmail.plb    
    -- 2) Define the SMTP_OUT_SERVER (init.ora)
    -- 3) The DBA grant EXECUTE ON UTL_MAIL TO PUBLIC (or USER)
    -- 4) The DBA add record in ACL (Access Control List)
    -- Using package called DBMS_NETWORK_ACL_ADMIN
    -- alter system set smtp_out_server = 'mail.xxx.ac.ae:25'
    --2
    BEGIN
        UTL_MAIL.SEND(    --Actually mail will be send from server`s address. This
                          --is named 'email relay'.
                          SENDER => Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра.',
                      RECIPIENTS => Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра.',
                            --CC optional,
                           --BCC optional,
                         SUBJECT => 'Theme',
                         MESSAGE => 'MyMessage',
                       MIME_TYPE => 'text; charset=us-ascii'
                     );
    END;
    --3
    --Create file on your computer, for example 'D:\ex\photo.jpg'
    --Make action in SQL Plus:
    CREATE DIRECTORY DIR AS 'D:\ex'
    /    
    --Then create block
    DECLARE
        FUNCTION GET_FILE(MY_DIR VARCHAR2, FNAME VARCHAR2RETURN RAW IS
            photo RAW(32767);
            --BFILENAME returns a BFILE locator that is associated 
            --with a physical LOB binary file on the server file system
            bf BFILE := BFILENAME(MY_DIR, FNAME);
        BEGIN
            DBMS_LOB.FILEOPEN(bf, DBMS_LOB.FILE_READONLY); --open BFILE for read-only
            --DBMS_LOB.SUBSTR function returns amount bytes or characters of a LOB
            --starting from an absolute offset from the beginning if the LOB
            photo:= DBMS_LOB.SUBSTR(bf);
            DBMS_LOB.CLOSE(bf);
            RETURN photo;
        END;
    BEGIN
        UTL_MAIL.SEND_ATTACH_RAW
            (
            SENDER        => Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра.',
            RECIPIENTS    => Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра.',
            SUBJECT       => 'Theme with photo',
            MESSAGE       => 'MyMessage',
            MIME_TYPE     => 'text; charset=us-ascii',
                ATTACHMENT    => GET_FILE('DIR','photo.jpg'),
                ATT_INLINE    => TRUE--TRUE will be part of email, FALSE - 
                                       --message will be only as attachment
                ATT_MIME_TYPE => 'image/jpg',
                ATT_FILENAME  => 'photo.jpg'
            );
    END;
    --4
    --Create file on your computer, for example 'D:\ex\file.txt'
    --Make action in SQL Plus:
    CREATE DIRECTORY DIR AS 'D:\ex'
    /
    --Then create block
    DECLARE
        FUNCTION GET_FILE(MY_DIR VARCHAR2, FNAME VARCHAR2RETURN VARCHAR2 IS
            txt VARCHAR2(32767);
            --BFILENAME returns a BFILE locator that is associated 
            --with a physical LOB binary file on the server file system
            bf BFILE := BFILENAME(MY_DIR, FNAME);
        BEGIN
            DBMS_LOB.FILEOPEN(bf, DBMS_LOB.FILE_READONLY); --open BFILE for read-only
            --UTL_RAW.CAST_TO_VARCHAR2 converts the RAW input string into VARCHAR2
            txt := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(bf));
            DBMS_LOB.CLOSE(bf);
            RETURN txt;
        END;
    BEGIN
        UTL_MAIL.SEND_ATTACH_VARCHAR2
            (
            SENDER        => Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра.',
            RECIPIENTS    => Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра.',
            SUBJECT       => 'Theme with text file',
            MESSAGE       => 'MyMessage',
            MIME_TYPE     => 'text; charset=us-ascii',
                ATTACHMENT    => GET_FILE('DIR','file.txt'),
                ATT_INLINE    => FALSE,
                ATT_MIME_TYPE => 'text/Plain',
                ATT_FILENAME  => 'file.txt'
            );
    END;


CHAPTER 15. "Dynamic SQL"
Task 15.1. Review dynamic SQL.
    1. Which stages pass SQL statements.
    2. What is dynamic SQL? How to use dynamic SQL (two ways)?
    3. For which situations we can use dynamic SQL?
Solution:
    --1
    Parse. Check the statement syntax, validating the statement, ensure all 
    referencing objects are correct, the privilege exists.
    Bind. Check the bind variable if the statement contains bind var.
    Execute. Execute the statement (non queries statements).
    Fetch. Retrieve the rows (queries statements).
    --2
    Dynamic SQL - it is a creation SQL statements at run time (not compile time).
    We can use dynamic SQL:
    - EXECUTE IMMEDIATE SQL;
    - DBMS_SQL.
    --3
    - For make DDL, DCL operations or session-controls statements in PL/SQL blocks.
    - If we want make varying column date or different conditions with or without
    bind variables.
    
Task 15.2. Execute immediate.
    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. Create procedure 'PRC' that delete all data from any table and display 
    quantity of deleted rows. Then delete table TAB1.
    2. Create procedure 'PRC' using execute immediate with variable 'res' that create 
    table with two parameters: 'pt' - name of table, 'pc' - content for VALUES 
    clause. What priviligies you must have?
Solution:
    --1
    CREATE OR REPLACE PROCEDURE PRC (x VARCHAR2IS
        v_rows NUMBER;
    BEGIN
        EXECUTE IMMEDIATE 'DELETE FROM '||x;
        v_rows := SQL%ROWCOUNT;
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(v_rows||' rows deleted');
    END;
    EXEC PRC('TAB1');
    --2
    --We must have CREATE TABLE privilege, not ROLE, because in dynamic SQL we create
    --table directly. Example: need login as SYS user, switch container by command:
    --'ALTER SESSION SET CONTAINER=ORCLPDB;' and run 'GRANT CREATE TABLE TO HR;'
    CREATE OR REPLACE PROCEDURE PRC (pt VARCHAR2, pc VARCHAR2IS
        res VARCHAR2(1000);
    BEGIN
        res := 'CREATE TABLE ' || pt || ' (' || pc || ')';
        DBMS_OUTPUT.PUT_LINE(res);
        EXECUTE IMMEDIATE res;
    END;
    EXEC PRC('MY_TAB','COL1 NUMBER, COL2 VARCHAR2(100)');

Task 15.3. Execute immediate. USINGINTO.
    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. What do USING? What literals can not be used? How use NULL in USING?
    2. Demonstrate USING. Create procedure 'PRC' where insert values to table with 
    parameters. Then insert 4 and 'four' into TAB1.
    3. Remake point 2 using anonymous block with variables.
    4. What do INTO?
    5. Write block where display in DBMS_OUTPUT result (two types of variables) of 
    EXECUTE IMMEDIATE which make SELECT to TAB1 with WHERE equal variable 'v'.
    6. Using EXECUTE IMMEDIATE make function that return row from 'SELECT * to TAB1
    WHERE COL1' = parameter. Write block where use this function for COL1 = 2 and
    display result.
    7. Create procedure 'PRC' that update TAB1 and demonstrate situation when 
    specify all parameter modes.
    8. Using TAB1 demonstrate situation when you can`t specify a parameter mode. 
    Explain.
Solution:
    --1
    USING is used to hold all bind arguments. The default parameter mode is IN.
    We can`t use boolean literals (TRUEFALSE, and NULL) directly in USING, but we 
    can use variable with value = NULL or TRUE or FALSE
    --2
    CREATE OR REPLACE PROCEDURE PRC(pt VARCHAR2, p1 VARCHAR2, p2 VARCHAR2IS        
    BEGIN
        --You cannot use placeholders for the names of schema objects
        EXECUTE IMMEDIATE 'INSERT INTO '||pt||' VALUES(:a, :b)' 
            USING p1, p2; --USING p1, NULL; !!! will be error, because NULL directly
        COMMIT;
    END;
    EXEC PRC('TAB1','4', 'four');
    --3
    DECLARE
        v1 VARCHAR2(100) := 'TAB1';
        v2 VARCHAR2(100) := '4';
        v3 VARCHAR2(100) := 'four';
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO '||v1||' VALUES(:a, :b)' 
            USING v2, v3;
        COMMIT;
    END;
    --4
    --INTO used for single-row queries and place result of EXECUTE IMMEDIATE into
    --variables or records. Can combine with USING.
    --5
    DECLARE
        var VARCHAR2(100);
        rec TAB1%ROWTYPE;
        v VARCHAR2(100) := '2';
    BEGIN
        --variable
        EXECUTE IMMEDIATE 'SELECT COL1 FROM TAB1 WHERE COL1 = :v'
            INTO var
                USING v;
        DBMS_OUTPUT.PUT_LINE(var);
        --record
        EXECUTE IMMEDIATE 'SELECT * FROM TAB1 WHERE COL1 = :v'
            INTO rec
                USING v;
        DBMS_OUTPUT.PUT_LINE(rec.COL1||rec.COL2);        
    END;
    --6
    CREATE OR REPLACE FUNCTION FNC (x NUMBERRETURN TAB1%ROWTYPE IS
        res TAB1%ROWTYPE;
    BEGIN
        EXECUTE IMMEDIATE 'SELECT COL1, COL2 FROM TAB1 WHERE COL1=:x'
            INTO res
                USING x;
        RETURN res;
    END;
    DECLARE
        rec TAB1%ROWTYPE;
    BEGIN
        rec := FNC(2);
        DBMS_OUTPUT.PUT_LINE(rec.COL1||rec.COL2);
    END;
    --7
    CREATE OR REPLACE PROCEDURE PRC(pin IN NUMBER
                                    pout OUT VARCHAR2
                                    pinout IN OUT NUMBERIS
    BEGIN
        UPDATE TAB1
            SET COL1 = pinout - 1
                WHERE COL1 = pin
                    RETURNING COL1, COL2 INTO pinout, pout;
        COMMIT;
    END;
    --
    DECLARE
        vin     NUMBER := 1;
        vout    TAB1.COL2%TYPE;
        vinout  NUMBER := 1;
    BEGIN
        EXECUTE IMMEDIATE 'BEGIN PRC(:i, :o, :io); END;'
            USING IN vin, OUT vout, IN OUT vinout;
        DBMS_OUTPUT.PUT_LINE(vout||' '||vinout);
    END;
    --8
    DECLARE
        vin NUMBER := 2;
        vout VARCHAR2(100);
        text VARCHAR2(1000);
    BEGIN
        text := 'DELETE TAB1 
                    WHERE COL1 = :i 
                        RETURNING COL2 INTO :o';
        EXECUTE IMMEDIATE text
            USING IN vin RETURNING INTO vout; --Can`t write OUT or IN OUT. Only IN.
            --EXECUTE IMMEDIATE with a RETURNING  
            --clause is used to execute dynamic UPDATEINSERT, or DELETE statements 
            --only. We can`t use it for subprogram from point 7.
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(vout);
    END;
    
Task 15.4. Execute immediate. Part 3.
    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, 'a');
        INSERT INTO TAB1(COL1, COL2) VALUES(2, 'b');
        INSERT INTO TAB1(COL1, COL2) VALUES(3, 'a');
        INSERT INTO TAB1(COL1, COL2) VALUES(4, 'b');
        INSERT INTO TAB1(COL1, COL2) VALUES(5, 'c');
        INSERT INTO TAB1(COL1, COL2) VALUES(6, 'c');
        COMMIT;
    Then do next:
    1. What mean REF cursor.
    2. Write block where using 3 different REF cursors (and explain types of these
    cursors):
    - at first way display values of TAB1 where TAB1.COL2 = 'a';
    - at second way display values of TAB1 where TAB1.COL2 = 'b';
    - at third way display values of TAB1 where TAB1.COL2 = 'c';
    3. Remake point 2 only for demonstrating when we can assign REF CURSOR`s variable
    to another variable and when not can? How reopen REF cursor?
    4. Make procedure 'PRC' (with parameter 'p') which displays values of TAB1.COL2 
    where COL1 = parameter and procedure must display all rows from TAB1 if 
    parameter IS NULL. Write result for parameter: NULL; 2; 7.
Solution:
    --1
    REF cursor is cursor that can be opened many times with different queries.
    --2
    DECLARE
        cur_row TAB1%ROWTYPE;
        --WEAK type. Variable with system REF CURSOR type do not checks result
        curs SYS_REFCURSOR; --WEAK cursor variable
        --STRONG type. Checks result by type after RETURN... Need create variable
        TYPE rct IS REF CURSOR RETURN TAB1%ROWTYPE;
        curt rct; --STRONG cursor variable
        --WEAK type. Do not checks result. Need create variable
        TYPE rc IS REF CURSOR--WEAK type
        cur rc; --WEAK cursor variable
    BEGIN
        --first way
        OPEN curs FOR SELECT COL1, COL2
                         FROM TAB1
                             WHERE COL2 = 'a';
            LOOP
                FETCH curs INTO cur_row;
                EXIT WHEN curs%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
            END LOOP;
        CLOSE curs;
        --second way
        OPEN curt FOR SELECT COL1, COL2
                         FROM TAB1
                             WHERE COL2 = 'b';
            LOOP
                FETCH curt INTO cur_row;
                EXIT WHEN curt%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
            END LOOP;
        CLOSE curt;
        --third way
        OPEN cur FOR SELECT COL1, COL2
                         FROM TAB1
                             WHERE COL2 = 'c';
            LOOP
                FETCH cur INTO cur_row;
                EXIT WHEN cur%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
            END LOOP;
        CLOSE cur;        
    END;
    --3
    --We can assign REF CURSOR`s variable only to REF CURSOR`s variable (STRONG
    --cursor can be assigned to STRONG cursor if they have same TYPE and RETURN type).
    DECLARE
        cur_row TAB1%ROWTYPE;
        --WEAK type. Variable with system REF CURSOR type do not checks result
        curs SYS_REFCURSOR; --WEAK cursor variable
        --STRONG type. Checks result by type after RETURN... Need create variable
        TYPE rct IS REF CURSOR RETURN TAB1%ROWTYPE;
        curt rct; --STRONG cursor variable
        --WEAK type. Do not checks result. Need create variable
        TYPE rc IS REF CURSOR--WEAK type
        cur rc; --WEAK cursor variable
        --standard cursor
        CURSOR std_cursor IS SELECT * FROM TAB1;
    BEGIN
        --NOT VALID
            --1
            --OPEN curs FOR SELECT * FROM TAB1;
            --    LOOP
            --        FETCH curt INTO cur_row;
            --        EXIT WHEN curt%NOTFOUND;
            --        DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
            --    END LOOP;
            --    cur := curs;
            --CLOSE curs;
                --2
                --OPEN std_cursor;
                --    curs := std_cursor;
                --    curt := std_cursor;
                --    cur := std_cursor;
                --CLOSE std_cursor;
        --VALID
        curs := curs;   curt := curs;   cur := curs;
        --assigning opened cursor to another cursor automatically allows you
        --work with data from opened cursor, until the opened cursor is closed
        OPEN curs FOR SELECT * FROM TAB1;
            curs := curs;   curt := curs;   cur := curs;
            LOOP 
                FETCH curt INTO cur_row;
                EXIT WHEN curt%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1);
            END LOOP;
        CLOSE curs; --!!! You can reopen REF cursor without closing
        --not valid, because area in memory binded with CURT closed by "CLOSE curs;"
        --LOOP 
        --    FETCH curt INTO cur_row;
        --    EXIT WHEN curt%NOTFOUND;
        --    DBMS_OUTPUT.PUT_LINE(cur_row.COL1);
        --END LOOP;
    END;
    --4
    CREATE OR REPLACE PROCEDURE PRC (p NUMBER := NULLIS
        TYPE rt IS REF CURSOR;
        cur RT;
        cur_row TAB1.COL2%TYPE;
        v_text VARCHAR2(100) := 'SELECT COL2 FROM TAB1';
    BEGIN
        IF p IS NULL THEN
            OPEN cur FOR v_text;
        ELSE
            v_text := v_text || ' WHERE COL1 = :pp';
            OPEN cur FOR v_text USING p;
            --OPEN cur FOR v_text || ' WHERE COL1 = :pp' USING p;
        END IF;
        LOOP
            FETCH cur INTO cur_row;
            EXIT WHEN cur%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(cur_row);
        END LOOP;
        CLOSE cur;
    END;
    BEGIN
        PRC;    --if NULL (default), then result is 'a, b, a, b, c, c'
        PRC(2); --if 2, then result is 'b'
        PRC(7); --if 7, then result is nothing, because cursor not will be looped.
    END;

Task 15.5. Execute immediate. EXECUTE IMMEDIATE FOR block. DBMS_SQL.
    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. Demonstrate execute immediate for anonymous block which contain variable 'a'
    with value 'one' and this block displays in DBMS_OUTPUT this value.
    2. Create procedure 'COMPILE_OBJECT' which compile any object: procedure, 
    function, package spec or package body.
    3. Remake point 2 using DBMS_SQL.
    4. Using DBMS_SQL create procedure 'PRC' with parameters: table name 'tn' and
    table`s values (for VALUES clause inside parentheses). Procedure will be 
    insert values to TAB1 table and displays quantity of insert`s rows. 
    Then insert row in TAB1.COL1 = 4 and TAB1.COL2 = 'four'.
Solution:
    --1
    DECLARE 
        v_text VARCHAR2(500) := 
            q'!
            DECLARE
                a VARCHAR2(100) := 'one';
            BEGIN
                DBMS_OUTPUT.PUT_LINE(a);
            END;
            !';
    BEGIN
        EXECUTE IMMEDIATE v_text;
    END;
    --2
    CREATE OR REPLACE PROCEDURE COMPILE_OBJECT(my_type VARCHAR2,
                                               my_name VARCHAR2,
                                               my_package_part VARCHAR2 := NULLIS
        v_text VARCHAR2(500) := 
            'ALTER '||my_type||' '||my_name||' COMPILE '||my_package_part;
    BEGIN
        EXECUTE IMMEDIATE v_text;    
    END;
    --3
    CREATE OR REPLACE PROCEDURE COMPILE_OBJECT(my_type VARCHAR2,
                                               my_name VARCHAR2,
                                               my_package_part VARCHAR2 := NULLIS                                   
        cur NUMBER;
        res NUMBER;
    BEGIN
        cur := DBMS_SQL.OPEN_CURSOR; --save ID of cursor in memory
        --then parsing
        DBMS_SQL.PARSE(cur,
                       'ALTER '||my_type||' '||my_name||' COMPILE '||my_package_part,
                       DBMS_SQL.NATIVE);
        --and execute
        res := DBMS_SQL.EXECUTE(cur);
        --res will be stored value = 0
    END;
    --4
    --DBMS objects and structers not allowed to replace by bind-variables.
    --Therefore we created first parameter for table name, second parameter for
    --value1 and third parameter for value2
    CREATE OR REPLACE PROCEDURE PRC(tn VARCHAR2, t1 VARCHAR2, t2 VARCHAR2IS
        text VARCHAR2(2000) := 'INSERT INTO '||tn||' VALUES(:1, :2)';
        cur NUMBER;
        res NUMBER;
    BEGIN
        cur := DBMS_SQL.OPEN_CURSOR;
        --DBMS_SQL.PARSE(cur, 
        --               'INSERT INTO '||tn||' VALUES(:1, :2)', 
        --               DBMS_SQL.NATIVE);
        DBMS_SQL.PARSE(cur, text, DBMS_SQL.NATIVE);        
        --
        DBMS_SQL.BIND_VARIABLE(cur, ':1', t1);
        DBMS_SQL.BIND_VARIABLE(cur, ':2', t2);
        --
        res := DBMS_SQL.EXECUTE(cur);
        DBMS_OUTPUT.PUT_LINE('Inserted '||res||' rows');
        COMMIT;
    END;
    EXEC PRC('TAB1', '4', 'four');
    SELECT * FROM TAB1


CHAPTER 16. "Advanced features in PL/SQL"
Task 16.1. Definer`s Rights. Invoker`s Rights.
    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. What mean Definer`s Rights and Invoker`s Rights.
    2. Create procedure 'PRC' with definer rights.
    3. Remake point 2 with invoker rights.
Solution:
    --1
    AUTHID DEFINER is default right. 
    Programs execute with the priviligies of the owner. User does not require 
    priviligies on underlying objects that the procedure accesses. User requires 
    priviliege only to execute a procedure.
    INVOKER DEFINER. Programs execute with the priviligies of the calling user.
    User requires priviligies on the underlying objects that the procedure accesses.
    --2
    CREATE OR REPLACE PROCEDURE PRC /*AUTHID DEFINER*/ IS
    BEGIN
        UPDATE TAB1
            SET COL1 = 3
                WHERE COL2 = 'three';
        DBMS_OUTPUT.PUT_LINE('Definer rights');
    END;
    --3
    CREATE OR REPLACE PROCEDURE PRC AUTHID CURRENT_USER IS
    BEGIN
        UPDATE TAB1
            SET COL1 = 3
                WHERE COL2 = 'three';
        DBMS_OUTPUT.PUT_LINE('Invoker rights');
    END;

Task 16.2. Autonomous Transactions.
    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. What mean autonomous transactions? Where we can use it and can`t use.
    2. Create procedure 'PRC' that insert value 'zero' into TAB1.COL2. Also create
    procedure 'PRC2' that firstly insert value 'four' into TAB1.COL2, secondly call
    'PRC', thirdly make 'ROLLBACK'. But result of 'PRC' must be saved. Explain it.
    3. List features of autonomous transactions and for which objects using.
Solution:
    --1
    It is independent transactions started by another main transaction.
    Autonomous transaction not allowed for packages and nested blocks. Allowed for 
    subprograms (for subprograms in packages too), anonymous head blocks or triggers.
    --2
    CREATE OR REPLACE PROCEDURE PRC IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO TAB1(COL2) VALUES('zero');
        COMMIT;
    END;
    CREATE OR REPLACE PROCEDURE PRC2 IS
    BEGIN
        INSERT INTO TAB1(COL2) VALUES('four');
        PRC; --COMMIT from this procedure influence only to insert value 'zero'
        ROLLBACK--inserted value 'four' will be ROLLBACK
    END;
    --3
    - Suspend the calling transaction until the autonomous transactions are 
    completed.
    - If an autonomous transaction attempts to access a resource held by the main 
    transaction, a deadlock can occur.
    - Autonomous transaction must be commited or rolling back if it`s DML operation.
    
Task 16.3. Passing parameters.
    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. Which parameter always passed by reference? Explain.
    2. Explain what mean passing by value and passing by reference.
    3. For which operation will be used passing only by value?
    4. Create procedure 'PRC' that contain SELECT COUNT(COL1) into variable 'x' WHERE
    COL2 = 'x' and demonstrate values of parameters using passing by value. Show 
    result in anonymoys block for COL2 = 'two' and 'z'.
    Remake this procedure using passing by reference.
Solution:
    --1
    IN parameter, because they can not be changed. Uses same memory location.
    --2
    Use for OUTIN OUT parameters. Efficient for LOB, XMLTYPE, collections etc.
        - Pass by value (default). The value of parameter copied in temporary buffer 
    and procedure work with this buffer. If successful completion of the procedure, 
    the result from buffer are copied back into the parameter variable. If not - the
    data from buffer not copied back and in EXCEPTION handler we can see original 
    value from parameter.
    Pass by value requires twice the memory for every OUTIN OUT parameter. Also
    it takes time to copy the data to the temporary buffer and back to the parameter.
        - Pass by reference (NOCOPY). Temporary buffer not creates and new parameter 
    value are place directly to the parameter. If unsuccessful completion of the 
    procedure, the parameter store new value.
    --3
    The remote procedure call (RPC) protocol.
    --4
    --Pass by value (default) - without NOCOPY
    --Pass by reference - using NOCOPY. Need remove comments.
    CREATE OR REPLACE PROCEDURE PRC (x IN OUT /*NOCOPY*/ VARCHAR2IS
    BEGIN
        SELECT COUNT(COL1)
            INTO x
                FROM TAB1
                    WHERE COL2 = x;
        IF x = 0 THEN
            RAISE NO_DATA_FOUND;
        END IF;
    END;
    DECLARE
        res VARCHAR2(100) := 'two'; --res VARCHAR2(100) := 'z';
    BEGIN
        DBMS_OUTPUT.PUT_LINE('IN = '||res);
        PRC(res);
        DBMS_OUTPUT.PUT_LINE('OUT = '||res);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('UNSUCCESSFUL = '||res);
    END;

Task 16.4. Restrictions of NOCOPY hint.
    1. List situations, when NOCOPY not working.
Solution:
    --1
    1) Actual parameter:
    - is an element of an index-by-table (!!! but parameter as index-by-table will be
    working with NOCOPY);
    - is constrained (for example, NOT NULL or scale for NUMBER);
    This restriction does not apply to size-constrained character strings. 
    This restriction does not extend to constrained elements or attributes of 
    composite types;
    - actual and formal parameter are records, where one or both records were 
    declared by using %ROWTYPE or %TYPE, and constraints on corresponding fields in 
    the records differ;
    - requires an implicit data type conversion.
    2) The subprogram is called through a database link or as an external procedure.

Task 16.5. Using the PARALLEL_ENABLE hint.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER        DEFAULT 0, 
                           COL2 VARCHAR2(100) NOT NULL);
        BEGIN
            FOR i IN 1..1000000 LOOP
                INSERT INTO TAB1(COL1, COL2) VALUES(i, 'val_'||i); 
            END LOOP;
            COMMIT;
        END;
    Then do next:
    1. Where can be used PARALLEL_ENABLE and why? For which Oracle edition available?
    2. Demonstrate it and explain using TAB1.
Solution:
    --1
    Can be used in functions for perfomance and it is mean that function can be used 
    in a parallelized query or parallelized DML statements.
    Available for enterprise edition.
    Look numbers of active CPUs: SELECT * FROM v$osstat
    --2
    CREATE OR REPLACE FUNCTION FNC (x NUMBERRETURN NUMBER PARALLEL_ENABLE IS
    BEGIN
        RETURN x * 3;
    END;
    --Oracle will be open 2 processes to execute this query.
    --Each process take subset of data.
    SELECT /*+ PARALLEL(t1, 2) */
            FNC(t1.COL1),
            t1.COL2
        FROM TAB1 t1;

Task 16.6. Using RESULT_CACHE.
    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 which edition of database we can use it?
    2. List benefits of RESULT_CACHE.
    3. List restrictions of RESULT_CACHE.
    4. Demonstrate RESULT_CACHE (two ways: before 11g and since 11g) using TAB1.
    Explain it. 
    5. Explain using RESULT_CACHE at the package.
Solution:
    --1
    For ENTERPRISE edtition.
    --2
    1) Each time a result-cached PL/SQL function is called with different parameter
    values, those parameters and their results are stored in cache. 
    2) The function result cache is stored in a shared global area (SGA) and 
    avalable to any sessions.
    3) Subsequent calls to the same function with the same parameters uses the result 
    from cache.
    4) Use with functions that are called frequently and dependent on information
    that changes infrequently.
    --3
    1) If the database object, that used to compute the value, changed, then result
    recomputed using new object. For example, if we INSERT new row into table and 
    function uses with this table, then result will be recomputed.
    2) If function execution raised an unhandled exception, the exception results 
    is not stored in the cache.
    --4
    CREATE OR REPLACE FUNCTION FNC (x VARCHAR2RETURN NUMBER 
        RESULT_CACHE /* RELIES_ON (TAB1) */
    IS
        res NUMBER;
    BEGIN
        SELECT COUNT(COL1)
          INTO res
            FROM TAB1
                WHERE COL2 = x;
        RETURN res;
    END;
    --then if we call function with parameter 'two', result will be stored in cache
    --across sessions
    SELECT FNC('two') FROM TAB1
    --and if we call this function with parameter 'two' again, then result will be 
    --appeared faster
    SELECT FNC('two') FROM TAB1
    --RELIES_ON need before 11g and mean that if TAB1 (or any table or view) 
    --changed, then recompute result of function. Since 11g result recompute 
    --automatically without RELIES_ON.
    --5
    We can write RELIES_ON only for functions at the package body. In package spec
    do not allowed write RELIES_ON.
    
Task 16.7. Using DETERMINISTIC.
    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 which version and edition of database we can use it?
    2. Demonstrate DETERMINISTIC using TAB1. Explain it. 
    3. List restrictions.
Solution:
    --1
    Since Oracle 8i ENTERPRISE edition.
    --2
    DETERMINISTIC save parameters and results of function ONLY for your session.
    RESULT_CACHE preferable. DETERMINISTIC function works with SQL queries only.
    Before using DETERMINISTIC, DBA make changes in these values:
    - parameter shared_pool_size
    - parameter result_cache_max_size
    - parameter result_cache_mode
    CREATE OR REPLACE FUNCTION FNC (x VARCHAR2RETURN NUMBER DETERMINISTIC IS   
        res NUMBER;
    BEGIN
        SELECT COUNT(COL1)
          INTO res
            FROM TAB1
                WHERE COL2 = x;
        RETURN res;
    END;
    --3
    - Do not specify DETERMINISTIC for a function whose result depends on the state
    of session variables or schema objects.
    - You must specify this keyword if you want to call function in expression
    of a FUNCTION-BASED INDEX or from the query of a MATERIALIZED VIEW that is marked
    REFRESH FAST or ENABLE QUERY REWRITE. If you subsequently change the semantics of
    the function, you must manually rebuild all dependent function-based indexes and
    materialized views.

Task 16.8. Bulk binding.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1 (COL1 NUMBER(2)     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');
        COMMIT;
    Then do next:
    1. Using bulk create procedure 'PRC' with number parameter 'p':
    - load all values from COL2 into NESTED_TABLE 'arr';
    - update TAB1.COL1 by value COL1 + parameter 'p' for each row where COL2 = value 
    from 'arr'. Display quantity of updated rows for each arr`s element.
    Run procedure 'PRC' with parameter = 10. Write and explain result for TAB1.COL1.
    2. Remake procedure 'PRC' with EXCEPTION section where display count of errors,
    info about errors: index of element, code, message.
    Recreate TAB1. Run procedure 'PRC' with parameter = 98. Write and explain result 
    for TAB1.COL1.
Solution:
    --1
    CREATE OR REPLACE PROCEDURE PRC (p NUMBERIS
        TYPE tt IS TABLE OF VARCHAR2(100);
        arr tt;
    BEGIN
        SELECT COL2
          BULK COLLECT INTO arr
            FROM TAB1;
        FORALL i IN arr.FIRST..arr.LAST SAVE EXCEPTIONS
            UPDATE TAB1
                SET COL1 = COL1 + p
                    WHERE COL2 = arr(i);
        FOR i IN 1..arr.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(i)||' rows for element '||i);
        END LOOP;
        COMMIT;
    END;
    --result: 11, 12, 13
    BEGIN
        PRC(10);
    END;
    SELECT COL1 FROM TAB1;
    --2
    CREATE OR REPLACE PROCEDURE PRC (p NUMBERIS
        TYPE tt IS TABLE OF VARCHAR2(100);
        arr tt;
        ecount NUMBER;
    BEGIN
        SELECT COL2
          BULK COLLECT INTO arr
            FROM TAB1;
        FORALL i IN arr.FIRST..arr.LAST SAVE EXCEPTIONS
            UPDATE TAB1
                SET COL1 = COL1 + p
                    WHERE COL2 = arr(i);
        FOR i IN 1..arr.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(i)||' rows for element '||i);
        END LOOP;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ecount := SQL%BULK_EXCEPTIONS.COUNT;
            DBMS_OUTPUT.PUT_LINE('COUNT '||ecount);
            FOR j IN 1..ecount LOOP
                DBMS_OUTPUT.PUT_LINE
                    ('Index '||SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);
                DBMS_OUTPUT.PUT_LINE
                    ('Err_code ' ||SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
                DBMS_OUTPUT.PUT_LINE
                    ('Err_message '||SQLERRM( -SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
            END LOOP;
    END;
    --result: 98, 2, 3
    --for rows 'two' and 'three' update not worked, because length COL1 = 2
    BEGIN
        PRC(98);
    END;
    SELECT COL1 FROM TAB1;

Task 16.9. Bulk and cursors. Returning values with bulk.
    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 block where, using bulk, load into nested table 'n' values from 
    cursor SELECT COL1 FROM TAB1. Then display values from nested table. Which result
    will be? Which number of elements will be?
    2. Write block where, using bulk, load into varray 'v' (size is 2 elements),
    first 2 rows from cursor SELECT COL1, COL2 FROM TAB1. Then display values from 
    varray. Which result will be?
    3. Create block where load all rows from TAB1 into nested table 'a'.
    And if TAB1.COL2 = COL2 from 'a' then update TAB1.COL1 by value TAB1.COL1 * COL1 
    from 'a'. Then load the updated row into nested table 'b'. Display values from
    'b'. Which result will be in 'b'?
Solution:
    --1
    --Result: 1, 2, NULL. Count of elements = 3.
    DECLARE
        TYPE tt IS TABLE OF NUMBER;
        n tt; --OR YOU CAN WRITE: n tt := tt();
        CURSOR cur IS SELECT COL1 FROM TAB1;
    BEGIN
        OPEN cur;
            FETCH cur BULK COLLECT INTO n;
            FOR i IN n.FIRST..n.LAST LOOP
                DBMS_OUTPUT.PUT_LINE(n(i));
            END LOOP;
        CLOSE cur;
    END;
    --2
    --Result: 1 one, 2 two.
    DECLARE
        TYPE tt IS VARRAY(2) OF TAB1%ROWTYPE;
        v tt; --OR YOU CAN WRITE: v tt := tt();
        CURSOR cur IS SELECT COL1, COL2 FROM TAB1;
    BEGIN
        OPEN cur;
            FETCH cur BULK COLLECT INTO v LIMIT 2;
            FOR i IN v.FIRST..v.LAST LOOP
                DBMS_OUTPUT.PUT_LINE(v(i).COL1||' '||v(i).COL2);
            END LOOP;
        CLOSE cur;
    END;
    --3
    --Result: 1 one, 4 two, NULL three
    DECLARE
        TYPE tt IS TABLE OF TAB1%ROWTYPE;
        a tt; --OR YOU CAN WRITE: a tt := tt();
        b tt; --OR YOU CAN WRITE: a tt := tt();
    BEGIN
        SELECT COL1, COL2
          BULK COLLECT INTO a
            FROM TAB1;
        --
        FORALL i IN a.FIRST..a.LAST
            UPDATE TAB1
                SET COL1 = COL1 * a(i).COL1
                    WHERE COL2 = a(i).COL2
                        RETURNING COL1, COL2 BULK COLLECT INTO b;
        COMMIT;
        --
        FOR i IN b.FIRST..b.LAST LOOP
            DBMS_OUTPUT.PUT_LINE(b(i).COL1||' '||b(i).COL2);
        END LOOP;
    END;

Task 16.10. Looping sparse collection.
    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. Create block where load TAB1.COL2 in index-by-table 'a'. 
    Then delete element with index 2 from 'a'. 
    Using loop display values from 'a'. 
    Then using bulk, update TAB1.COL1 by value 0 where TAB1.COL2 equal values 
    from 'a' (two ways).
    Then using bulk, update TAB1.COL1 by value 33 where TAB1.COL2 equal values 
    from 'a' with index 3.
Solution:
    --1
    DECLARE
        TYPE tt IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
        a tt;
        TYPE it IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
        ind it;
    BEGIN
        --
        SELECT COL2
          BULK COLLECT INTO a
            FROM TAB1;
        a.DELETE(2);
        --
        FOR i IN a.FIRST..a.LAST LOOP
            IF a.EXISTS(i) THEN
                DBMS_OUTPUT.PUT_LINE(a(i));
            END IF;
        END LOOP;
        --
        FORALL i IN INDICES OF a
            UPDATE TAB1
                SET COL1 = 0
                    WHERE COL2 = a(i);
        COMMIT;
            ind(-1) := 1;
            ind(5) := 3;
            FORALL i IN VALUES OF ind
                UPDATE TAB1
                    SET COL1 = 0
                        WHERE COL2 = a(i);
            COMMIT;
        --
        FORALL i IN INDICES OF a BETWEEN 3 AND 3
            UPDATE TAB1
                SET COL1 = 33
                    WHERE COL2 = a(i);
        COMMIT;
    END;
    SELECT * FROM TAB1;


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 trigger
    --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;
        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
    CREATE OR REPLACE TRIGGER TRG 
        BEFORE INSERT 
        ON TAB1
        REFERENCES NEW AS N OLD AS O
        FOR EACH ROW
    BEGIN
        --can`t use NEW and OLD in AFTER trigger, but in declare section they can be
        IF :N.COL1 IS NULL THEN
            :N.COL1 := SEQ.NEXTVAL--can`t assign values to OLD pseudorecord
        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 '1'. 
    Then make operation: INSERT INTO VV(COL1, COL2) VALUES(4, 'four').
    Which result will be in view and TAB1? Which message will be in OUTPUT window and
    value at the SQL%ROWCOUNT after insert?
Solution:
    --1
    INSTEAD OF
    --2
    CREATE OR REPLACE TRIGGER TRG
        INSTEAD OF INSERT
        ON VV
    BEGIN
        DBMS_OUTPUT.PUT_LINE('1');
    END;
    --Row not will be added in view and table.
    --Message in output window: "1 row inserted.". And SQL%ROWCOUNT = 1.
    INSERT INTO VV(COL1, COL2) VALUES(4, 'four');

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');
    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?
Solution:
    --1
    CREATE OR REPLACE TRIGGER
    CREATE OR REPLACE PROCEDURE
    --2
    SELECT * FROM USER_TRIGGERS;
    SELECT * FROM USER_SOURCE;
    --3
    Triggers are implicitly invoked by DML.
    Procedures are explicitly invoked.
    --4
    COMMITSAVEPOINTROLLBACK not allowed in trigger.
    COMMITSAVEPOINTROLLBACK allowed in procedure.

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.
    2. Demonstrate trigger that will be insert into table T_LOG info about last DDL 
    operation.
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG_S
        BEFORE CREATE 
        ON SCHEMA
    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
    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. Which settings recommended for fastest perfomance.
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 he 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 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 perfomance 
    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 since 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_OUPUT.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.
    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;
    --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 FUNCTION
    CREATE PROCEDURE
    CREATE PACKAGE
    CREATE TYPE
    CREATE TYPE BODY
    --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 appear 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?
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 call object.

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 give 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

Добавить комментарий


Защитный код
Обновить

Форма авторизации