Training to exam Oracle Database 11g: Program with PL/SQL 1Z0-144 (EN)

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
    [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 create script at external file and run this in SQL Developer.    
    2. How run this script in SQLPlus.
    3. How 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.
    2. 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;
    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. 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 "".
    --2
    DECLARE 
        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'); 
    BEGIN 
        DBMS_OUTPUT.PUT_LINE('Var1='||var1);
        DBMS_OUTPUT.PUT_LINE('Var2='||var2);
        DBMS_OUTPUT.PUT_LINE('Var3='||"var 3");
    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
    /* 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.
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
    
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
    --outer block
    BEGIN <<outer>>
        DECLARE
            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;
    END outer;


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 presedence 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);
    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:
    - 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
    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
        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
        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.
    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 3 where display all steps, but if step = 2 then go to
    next step (also do not display step 2). Explain it.
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>>
            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
    BEGIN
        FOR i IN 1..3 LOOP
            CONTINUE WHEN i = 2; --In this loop all actions on the step = 2
                                 --after CONTINUE will not be performed
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    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?    
    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?
Solution:
    --1
    Can declare in declarative part of any block, subprogram or package.
    --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.
    
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.
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;
    
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(2) := 22;
        v_v(3) := 33;         
        v_v(4) := 44;    
    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(2) := 22;
        v_v(3) := 33;
        v_v(4) := 44;
        --EXISTS(n). Returns TRUE if n-element exists in collection.
        IF v_v.EXISTS(0) THEN
            DBMS_OUTPUT.PUT_LINE('EXISTS='||v_v(0));
        ELSE
            DBMS_OUTPUT.PUT_LINE('EXISTS=FALSE');
        END IF;
        --COUNT. If collection empty, then 0.
        DBMS_OUTPUT.PUT_LINE('COUNT='||v_v.COUNT); --result=4
        --FIRST. If collection empty, then NULL.
        DBMS_OUTPUT.PUT_LINE('FIRST='||v_v.FIRST); --result=1
        --LAST. If collection empty, then NULL.
        DBMS_OUTPUT.PUT_LINE('LAST='||v_v.LAST); --result=4
        --PRIOR(n). If not have preceding element, then NULL.
        DBMS_OUTPUT.PUT_LINE('PRIOR='||v_v.PRIOR(3)); --result=2
        --NEXT(n). If not have succeeding element, then NULL.
        DBMS_OUTPUT.PUT_LINE('NEXT='||v_v.NEXT(3)); --result=4
        --DELETE(x, y). X must be <= Y.
        v_v.DELETE(1, 2); --DELETE elements with INDEX 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;
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('two'), v_v('three')
    DELETE('two') - will be deleted only v_v('two'). If not found, then error not be.
    DELETE - will be deleted all elements
    
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. Create NESTED TABLES with scalar and non-scalar values. Add to this NESTED 
    TABLES 4 new values: for scalar without using loop, for non-scalar - using loop.
    Then, using loop, display all values from this nested tables.
    For any variable: delete fourth element, then delete 2-3 elements, then delete 
    all elements.
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
    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';
        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(4);    --delete 4th element   
        v_s.DELETE(2, 3); --delete 2-3 elements
        v_s.DELETE;       --delete all elements
        
        --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.5. 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.6. 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.
    --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-3 elements
        v_s.DELETE;  --delete all elements
        
        --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.7. 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. Demonstrate which method uses 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 := var(1, NULL);
    BEGIN
        --method LIMIT return max quantity of elements in VARRAY
        DBMS_OUTPUT.PUT_LINE(v.LIMIT);
        --but COUNT method return quantity of elements with values or with NULL.
        DBMS_OUTPUT.PUT_LINE(v.COUNT);
    END;
    
Task 7.8. 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. Number of elements (unbounded or bounded)?
    2. Subscript type?
    3. Dense or sparse?
    4. Where created?
    5. Can be object type attribute?
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.


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 opening cursor.
    5. For TAB1 write cursor (2 ways) where COL2 = parameter 'two'.
    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. 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
    DECLARE 
        CURSOR cur(p VARCHAR2IS
            SELECT COL1, COL2 
                FROM TAB1
                    WHERE COL2 = p;
        cur_row cur%ROWTYPE;
    BEGIN        
        OPEN cur('two');
            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('two') LOOP
            DBMS_OUTPUT.PUT_LINE(cur_row.COL1||' '||cur_row.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?
    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 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.
    --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 exseptions). Y is a message
    with length <= 2049 bytes (symbols over this length will be ignored). Z is a
    boolean value: TRUE - adds an error to stack, FALSE - 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;
                RAISE_APPLICATION_ERROR(-20000, 'MyText');
            END IF;
    END;
    --4
    DECLARE
        my_error_0 EXCEPTION
        PRAGMA EXCEPTION_INIT (my_error_0, -01476);
        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
    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||'; y = 0');
                RAISE;
        END;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE NOT detected');
        WHEN my_error_0 THEN
            DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE detected');
    END;    
    --7
    WHEN clause writes in EXCEPTION section and starts if name of initialized 
    exception mathes 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;


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 types of parameters we can use in procedure.
    4. Which object contain info about compiling errors.
    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 where must be two DBMS_OUTPUT, but exit from procedure after 
    first DBMS_OUTPUT.    
    7. How 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
    --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
        DBMS_OUTPUT.PUT_LINE(1);
        RETURN;
        DBMS_OUTPUT.PUT_LINE(2);
    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.
    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);
    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 Fucntion"
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 types of arguments we can use in function? How run function with this
    arguments?
    4. Which object contain info about compiling errors?
    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    
    --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 string;
    - 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 NUMBER;
                    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.
    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');
    END;
    --
    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 use overloading for procedures and functions in package.
    --2
    CREATE OR REPLACE PACKAGE PKG 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);    
        --But if we add next 4th procedure, will cause error when will be executed,
        --because types of parameters from one family not allowed.
        --PROCEDURE PRC(x VARCHAR2, y INTEGER);
    END;
    CREATE OR REPLACE PACKAGE BODY PKG 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;
    END;
    EXEC PKG.PRC(1,'one',TO_DATE('01.02.2000','DD.MM.YYYY'));
    EXEC PKG.PRC(1,'one');
    EXEC PKG.PRC('one',1);
    --3
    CREATE OR REPLACE PACKAGE PKG IS
        --functions must have:
        --1) different quantity of arguments
        FUNCTION FNC(x VARCHAR2, y NUMBERRETURN VARCHAR2;
        FUNCTION FNC(x VARCHAR2RETURN NUMBER;
        --2) or different types of arguments
        FUNCTION FNC(x NUMBERRETURN NUMBER;    
        --But if we add next 4th function, will cause error when will be executed,
        --because types of arguments from one family not allowed.
        --FUNCTION FNC(x INTEGER);
    END;
    CREATE OR REPLACE PACKAGE BODY PKG IS
        FUNCTION FNC(x VARCHAR2, y NUMBERRETURN VARCHAR2 IS
            res VARCHAR2(100);
        BEGIN
            res := x || y;
            RETURN res;
        END;
        --
        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 PKG.FNC('one', 1) FROM DUAL;
    SELECT PKG.FNC('11') FROM DUAL;
    SELECT PKG.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 persistant state of package 'PCK' using global variable 'vv' in 
    this package and procedure 'PRC'.
    4. Change procedure from point 3 where persistent state must be available only
    for this procedure.
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
        --in this block.
        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 LOAD_TAB(param OUT tt);
    END;
    CREATE OR REPLACE PACKAGE BODY PCK IS
        PROCEDURE LOAD_TAB(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.LOAD_TAB(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 'v' 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, 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?
    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 EXECUTE 
    IMMEDIATE which make SELECT to TAB1 with WHERE equal changeable 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.
Solution:
    --1
    USING is used to hold all bind arguments. The default parameter mode is IN.
    --2
    CREATE OR REPLACE PROCEDURE PRC(pt VARCHAR2, p1 VARCHAR2, p2 VARCHAR2IS        
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO '||pt||' VALUES(:a, :b)' 
            USING p1, p2;
        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;
    
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');
        COMMIT;
    Then do next:
    1. What mean REF cursor.
    2. Write block where using REF cursor at first display values of TAB1 where
    TAB1.COL2 = 'a' and then values of TAB1 where TAB1.COL2 = 'b'.
    3. 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; 5.
Solution:
    --1
    REF cursor is cursor that can be opened many times with different queries.
    --2
    DECLARE
        TYPE rt IS REF CURSOR;
        cur RT;
        cur_row TAB1%ROWTYPE;
    BEGIN
        OPEN cur FOR SELECT COL1, COL2
                         FROM TAB1
                             WHERE COL2 = 'a';
            LOOP
                FETCH cur INTO cur_row;
                EXIT WHEN cur%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
            END LOOP;
        CLOSE cur;
        OPEN cur FOR SELECT COL1, COL2
                         FROM TAB1
                             WHERE COL2 = 'b';
            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
    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;
        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'
        PRC(2); --if 2, then result is 'b'
        PRC(5); --if 5, then result is nothing.
    END;

Task 15.5. Execute immediate. EXECURE 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, 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?
    2. Create procedure 'PRC' that insert value 'zero' into TAB1.COL2. Then 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.
Solution:
    --1
    It is independent transactions started by another main transaction.
    --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.
    - Autonomous transaction not allowed for packages and nested blocks. Allowed for 
    subprograms, anonymous head blocks or triggers.
    
Task 16.2. Pass 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. Which feature enable you to pass parameters 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 (PRC) 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';
    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.3. 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.4. 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 speed 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(COL1),
            COL2
        FROM TAB1 t1;

Task 16.5. 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.
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.
    
Task 16.6. 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.
    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.7. 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'.
    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);
        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);
        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.8. 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. Whish 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. Whish 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. 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;
    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;
    END;
    --3
    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.9. 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. Load TAB1.COL2 in index-by-table 'a'. 
    Then delete element with index 2 from 'a'. 
    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 nt IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
        ind nt;
    BEGIN
        --
        SELECT COL2
          BULK COLLECT INTO a
            FROM TAB1;
        a.DELETE(2);
        --
        DBMS_OUTPUT.PUT_LINE(a(1)||' '||a(3));
        --
        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?
    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?
Solution:
    --1
    Trigger is a PL/SQL block that is stored in the database and automatically 
    executed in response to a specified event.
    --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

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:
    - 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.
    
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')
    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'.
Solution:
    --1
    CREATE OR REPLACE TRIGGER TRG 
        BEFORE INSERT 
        ON TAB1
        REFERENCES NEW AS N OLD AS O
        FOR EACH ROW
    BEGIN
        IF :N.COL1 IS NULL THEN
            :N.COL1 := SEQ.NEXTVAL;
        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?
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
    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');
        COMMIT;
        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'.
Solution:
    --1
    INSTEAD OF
    --2
    CREATE OR REPLACE TRIGGER TRG
        INSTEAD OF INSERT
        ON VV
    BEGIN
        DBMS_OUTPUT.PUT_LINE('1');
    END;
    

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'.    
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;
    
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 and 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: 1 2 2 3 3 4
    INSERT INTO TAB1 VALUES(4, 'four');
    INSERT INTO TAB1 VALUES(5, 'five');

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 
       ON TAB2
       FOR EACH ROW
    DECLARE
        res NUMBER;
    BEGIN
        SELECT NVL(MAX(COL1), 0) + 1
          INTO res
            FROM TAB2;
        :NEW.COL1 := res;
    END;
    --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 not fully
    --completed) that will be error of mutating table.
    INSERT INTO TAB2(COL2) SELECT COL2 FROM TAB1;
    
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 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 18 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 be create system event trigger.
    2. Create trigger (indicate which use for schema also and only for databse) 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?);
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;

Task 18.9. Alternative execute procedure in trigger. Benefits of database-event 
    trigger.
    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(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
    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';
    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.
    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 activate warnings from 2 different categories and one error from another
    categorie. List numeric ranges of errors of all categories.
    6. 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 condtitions 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_WARNINGS 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,
                                                      --not warning    
    --6
    ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:INFORMATIONAL';
    
Task 19.5. PLSQL_WARNINGS. DBMS_WARNINGS.
    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?
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, $ELSE, $ELSIF, $END, $$, $ERROR
    --3
    Directive is $$.
    --4
    DBMS_PREPROCESSOR, DBMS_DB_VERSION
    
Task 20.2. DBMS_DB_VERSION
    1. Write block where display version and release of database. After, using 
    display text 'v12' if version of database since 12.
    2. Create function 'FNC' displaying result of 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
        res NUMBER;
    BEGIN
        SELECT EXTRACT(YEAR FROM SYSDATE)
          INTO res
            FROM DUAL;
        RETURN res + 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 display value
    of my_val, otherwise display 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>'
    --<vi> 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.
    1. Using PLSQL_CCFLAGS create procedure that if one parameter from PLSQL_CCFLAGS
    will be display time of starting and finishig procedure and using variable 'v' 
    with default value = 100000000 from PLSQL_CCFLAGS.
    2. Demonstrate DBMS_PREPROCESSOR. Explain it.
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
            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');

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
    --function
    DBMS_DDL.WRAP;
    --procedure
    DBMS_DDL.CREATE_WRAPPED
    --2
    --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
    TYPETYPE BODYPROCEDUREFUNCTIONPACKAGEPACKAGE 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
    Syntactic, but not semantic.
    --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.

    
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 'PRC'.
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 is 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.
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.
    
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 3 create user U1 with password 123 and grant to him necessary
    privs.
    4. After point 4 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 less 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 data types of the parameters;
    - the modes of the parameters;
    - the number of 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

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


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

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