Different examples for training to exam Oracle Database 19c: Program with PL/SQL 1Z0-149. Part 2.
CHAPTER 10. "Creating Procedure"
CHAPTER 11. "Creating Function"
CHAPTER 12. "Packages"
CHAPTER 13. "Working with Packages"
CHAPTER 14. "Using oracle-supplied Packages"
CHAPTER 15. "Dynamic SQL"
CHAPTER 16. "Advanced features in PL/SQL"
CHAPTER 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 to run procedure? Three ways.
3. What modes of parameters we can use in procedure.
4. Which object contain info about compiling errors. Which object contain info
about parameters for procedures to which you have access.
5. Create procedure which updates TAB1.COL1 with 2 input parameters:
- first parameter must be integer DEFAULT 3 and will be used for 'SET COL1';
- second parameter must be string DEFAULT 'three' and will be used for WHERE COL2.
How call this procedure writing manually values for both parameters (2 notations).
How call this procedure only with default values for both parameters (two ways).
How call this procedure only with default value for first parameter.
6. Create procedure with nested block where must be two DBMS_OUTPUT, but exit
from procedure after first DBMS_OUTPUT.
7. How to drop procedure? Which privileges 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
IN, OUT, IN OUT
--4
SELECT * FROM USER_ERRORS
SELECT * FROM ALL_ARGUMENTS
--5
CREATE OR REPLACE PROCEDURE PRC(x IN TAB1.COL1%TYPE DEFAULT 3,
y TAB1.COL2%TYPE := 'three') IS
BEGIN
UPDATE TAB1
SET COL1 = x
WHERE COL2 = y;
COMMIT;
END;
--manually values for both parameters
BEGIN
PRC(3, 'three');
PRC(y => 'three', x => 3);
END;
--default values for both parameters
BEGIN
PRC;
PRC();
END;
--default value for first parameter
BEGIN
PRC(y => 'three');
END;
--6
CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE(1);
RETURN;
DBMS_OUTPUT.PUT_LINE(2);
END;
END;
--7
--if you owner you must have CREATE PROCEDURE privs
DROP PROCEDURE PRC;
Task 10.2. Creating Procedure. Output, input-output parameters.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Create procedure that saves value into output parameter from TAB1.COL1 where
COL2 = input parameter:
- first parameter must be input string DEFAULT 'two' and will be used for COL2;
- second parameter must be output integer and store value from COL1.
Which DEFAULT value allow for output parameter?
Call this procedure with input parameter 'two' and display value from output
parameter:
- using bind variable
- without bind variable (three notations: positional, named and mixed).
2. Make point 1 again, but use one input-output parameter.
Solution:
--1
--DEFAULT value not allowed for output parameter
CREATE OR REPLACE PROCEDURE PRC(pin IN TAB1.COL2%TYPE DEFAULT 'two',
pout OUT TAB1.COL1%TYPE) IS
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 VARCHAR2) IS
BEGIN
SELECT COL1
INTO p
FROM TAB1
WHERE COL2 = p;
DBMS_OUTPUT.PUT_LINE('IN OUT = '||p);
END;
--with bind
VARIABLE io VARCHAR2(100);
EXECUTE :io := 'two';
EXEC PRC(:io);
PRINT io;
--without bind
DECLARE
res VARCHAR2(100) := 'two';
BEGIN
PRC(res);
res := 'two';
PRC(p => res);
DBMS_OUTPUT.PUT_LINE(res);
END;
Task 10.3. Creating Procedure. Part 2.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Create procedure with 1 parameter which can take TRUE or FALSE (with default
TRUE). Also if parameter = TRUE then display 'TRUE', if FALSE then display 'FALSE'
and otherwise display 'IS NULL'. Write block where call this procedure with:
parameter = FALSE; parameter equal variable that have FALSE; parameter equals
variable assigned TRUE result of expression.
2. Create procedure with 1 parameter with type row from TAB1. This procedure
must be add row from parameter to TAB1. Then make block with next operations:
- add row with manually values;
- add all rows from TAB1;
Solution:
--1
CREATE OR REPLACE PROCEDURE PRC(x BOOLEAN := TRUE) IS
BEGIN
IF x THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSIF NOT x THEN
DBMS_OUTPUT.PUT_LINE('FALSE');
ELSE
DBMS_OUTPUT.PUT_LINE('IS NULL');
END IF;
END;
--
DECLARE
boo BOOLEAN := FALSE;
BEGIN
PRC(FALSE);
PRC(boo);
boo := 1 = 1;
PRC(boo);
END;
--2
CREATE OR REPLACE PROCEDURE PRC(x TAB1%ROWTYPE) IS
BEGIN
INSERT INTO TAB1 VALUES x;
COMMIT;
END;
--
DECLARE
rec TAB1%ROWTYPE;
BEGIN
--manually
rec.COL1 := 0;
rec.COL2 := 'zero';
PRC(rec);
--from TAB1
FOR i IN (SELECT COL1, COL2 FROM TAB1) LOOP
PRC(i);
END LOOP;
END;
CHAPTER 11. "Creating Function"
Task 11.1. Creating Function. Part 1.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. What variables cannot be used in a function?
2. How call TO_CHAR function (four ways)?
3. What modes of arguments we can use in function? Which modes of arguments
allowed: in SELECT clause and in blocks?
4. Which object contain info about compiling errors? Which object contain info
about arguments for functions to which you have access.
5. Create function with one input argument, that will be return value from
TAB1.COL1 where COL2 = input argument. Display result for COL2 = 'two'.
6. Run function from point 5 where input argument = 'abc' and explain result:
using anonymous block, using SELECT clause.
7. Which precedence between stored function and function inside block with same
name?
Make all actions from point 5, but create function inside block and it must be
returned result = result - 100. Assign result of inside function into
variable 'v' and display this value. Also display result for stored function.
8. How remove function?
Solution:
--1
--SUBSTITUTION and HOST variables not allowed in function. But we can use it
--instead of arguments when calling function with arguments:
SELECT TO_CHAR(¶m) 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
--IN, OUT, IN OUT
--In SELECT clause allowed only IN arguments.
SELECT TO_CHAR(1) FROM DUAL;
--In block and subprograms allowed all.
DECLARE
x VARCHAR2(10);
BEGIN
x := TO_CHAR(1);
DBMS_OUTPUT.PUT_LINE(x);
END;
--4
SELECT * FROM USER_ERRORS
SELECT * FROM ALL_ARGUMENTS
--5
CREATE OR REPLACE FUNCTION FNC(x IN VARCHAR2) RETURN 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 VARCHAR2) RETURN 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 are 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: SELECT, UPDATE,
DELETE?
Solution:
--1
- The SELECT list or clause of a query;
- Conditional expressions of the WHERE and HAVING clauses;
- The CONNECT BY, START WITH, ORDER 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 VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC parameter is '||p);
END;
--
CREATE OR REPLACE FUNCTION FNC(f VARCHAR2) RETURN NUMBER IS
res NUMBER;
BEGIN
res := LENGTH(f);
RETURN res;
END;
Then do next:
1. What may be declared in:
- package`s specification;
- package`s body?
2. Create package 'PCK' with objects:
- procedure 'PRC' with string parameter;
- function 'FNC' with string argument and result must be NUMBER;
- integer variable 'v';
- string constant 'c' with value 'const';
- cursor 'CUR' for 'SELECT COL2 FROM TAB1' (two ways).
Then make block where:
- call with parameter 'one' procedure and function;
- assign to 'v' value 11 and display this varible;
- display value of 'c';
- display values of cursor (two ways).
Solution:
--1
--in package`s specification
Types, variables, constants, exceptions, cursors and subprograms.
--in package`s body
Queries for the cursors, the code for the subprograms.
--2
--Package specification
CREATE OR REPLACE PACKAGE PCK IS
PROCEDURE PRC(p VARCHAR2);
FUNCTION FNC(f VARCHAR2) RETURN NUMBER;
v PLS_INTEGER;
c CONSTANT VARCHAR2(100) := 'const';
TYPE rowt IS RECORD(C2 TAB1.COL2%TYPE);
CURSOR cur RETURN rowt;
CURSOR cur2 IS SELECT '2'||COL2 CC2 FROM TAB1;
END;
--Package body
CREATE OR REPLACE PACKAGE BODY PCK IS
CURSOR cur RETURN rowt IS
SELECT COL2 FROM TAB1;
--
PROCEDURE PRC(p VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC parameter is '||p);
END;
--
FUNCTION FNC(f VARCHAR2) RETURN NUMBER IS
res NUMBER;
BEGIN
res := LENGTH(f);
RETURN res;
END;
END;
--anonymous block
DECLARE
v_cur PCK.ROWT;
BEGIN
PCK.PRC('one');
--
DBMS_OUTPUT.PUT_LINE(PCK.FNC('one'));
--
PCK.V := 11;
DBMS_OUTPUT.PUT_LINE(PCK.V);
--
DBMS_OUTPUT.PUT_LINE(PCK.C);
--
OPEN PCK.CUR;
LOOP
FETCH PCK.CUR INTO v_cur;
EXIT WHEN PCK.CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_cur.C2);
END LOOP;
CLOSE PCK.CUR;
--
FOR i IN PCK.CUR2 LOOP
DBMS_OUTPUT.PUT_LINE(i.CC2);
END LOOP;
END;
Task 12.2. The visibility of a package`s components.
1. Which parts of package visible and hidden to user?
2. Create package with number variable 's' (default 11) in the specification and
number variable 'b' (default 22) in package body. Also write code in package
that, if we first call package in session or first call package after compiling,
displays once in DBMS_OUTPUT text 'package'. Then in package create procedure
'PRC', that will be displayed in DBMS_OUTPUT default values from this variables.
Indicate which variable is visible outside package and inside package. Call this
procedure, write result.
3. Which value will be in variables with same name in package specification,
package body and declarative section of procedure in package body?
4. Explain when we need recompile: package specification, package body.
5. How to remove package spec and body together? How to remove only package body?
Solution:
--1
- Only the declarations in the package specification are visible.
- Private constructs in the package body are hidden and not visible outside the
package body.
- All coding is hidden in the package body.
--2
CREATE OR REPLACE PACKAGE PCK IS
s NUMBER := 11; --available outside package
PROCEDURE PRC;
END;
--
CREATE OR REPLACE PACKAGE BODY PCK IS
b NUMBER := 22; --available inside package body
PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE(s);
DBMS_OUTPUT.PUT_LINE(b);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('package');
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20009,'Error in package body');
END;
--
--result 11 22. Also if first running then firstly we`ll see text 'package'
EXEC PCK.PRC;
--3
If declare variables with same name in spec, body and refer to this variables
from package body, then will be compilation with error. But if also declare
variable with same name in declarative section of procedure inside package body
and use it, then not will be error. Variable in procedure will have precedence
over variable from package spec and body.
--
CREATE OR REPLACE PACKAGE PCK IS
v NUMBER := 1;
--v NUMBER := 10; only one declaration for 'V' is permitted
v2 NUMBER := v;
PROCEDURE PRC;
END PCK;
CREATE OR REPLACE PACKAGE BODY PCK IS
--v NUMBER := v; compile with errors
v NUMBER := 2;
v NUMBER := 3; --allowed only if we do not refer to variable v in body,
--otherwise only one declaration for 'V' is permitted
PROCEDURE PRC IS
v NUMBER := 4; --have precedence over spec and body, if delete
--this declaration, then will be compile error
BEGIN
DBMS_OUTPUT.PUT_LINE(v);
END;
END PCK;
BEGIN
DBMS_OUTPUT.PUT_LINE(PCK.V); --1
DBMS_OUTPUT.PUT_LINE(PCK.V2); --1
PCK.PRC; --4
END;
--4
Package spec need recompile if changed objects from package body listed in
package specification.
Package body need recompile if package specification contains changes, that must
be list in package body.
--5
DROP PACKAGE PCK;
DROP PACKAGE BODY PCK;
CHAPTER 13. "Working with Packages"
Task 13.1. Working with Packages. Overloading. Part 1.
1. In what object we can use overloading and for which objects?
2. Demonstrate and explain overloading procedures.
3. Demonstrate and explain overloading functions.
Solution:
--1
We can overload nested subprograms, package subprograms, and type methods.
We can use the same name for several different subprograms if their formal
parameters differ in name, number, order, or data type family.
PL/SQL looks for matching numeric parameters in this order:
1. PLS_INTEGER (or BINARY_INTEGER, an identical data type)
2. NUMBER
3. BINARY_FLOAT
4. BINARY_DOUBLE
--2
CREATE OR REPLACE PACKAGE PCK IS
--procedures must have:
--1) different quantity of parameters
PROCEDURE PRC(x NUMBER, y VARCHAR2, z DATE);
PROCEDURE PRC(x NUMBER, y VARCHAR2);
--2) or different types of parameters
PROCEDURE PRC(x VARCHAR2, y NUMBER);
--3) or different names of parameters, but must use named notation when call
PROCEDURE PRC(x VARCHAR2);
PROCEDURE PRC(xx VARCHAR2);
--4) or different order with same names of parameters, but must use
--positional notation when call
PROCEDURE PRC2(a NUMBER, b VARCHAR2);
PROCEDURE PRC2(b VARCHAR2, a NUMBER);
--But if we add next 5th procedure, will cause error when will be executed,
--because types of parameters from one data type family not allowed.
--PROCEDURE PRC(x VARCHAR2, y INTEGER);
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC(x NUMBER, y VARCHAR2, z DATE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('xyz: '||x||y||z);
END;
--
PROCEDURE PRC(x NUMBER, y VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('xy: '||x||y);
END;
--
PROCEDURE PRC(x VARCHAR2, y NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('xy: '||x||y);
END;
--
PROCEDURE PRC(x VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('x: '||x);
END;
--
PROCEDURE PRC(xx VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('xx: '||xx);
END;
--
PROCEDURE PRC2(a NUMBER, b VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ab: '||a||b);
END;
--
PROCEDURE PRC2(b VARCHAR2, a NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ba: '||b||a);
END;
END;
BEGIN
PCK.PRC(1,'one',TO_DATE('01.02.2000','DD.MM.YYYY'));
PCK.PRC(1,'one');
PCK.PRC('one',1);
PCK.PRC(x => 'one');
PCK.PRC(xx => 'one');
PCK.PRC2(0, 'zero');
PCK.PRC2('zero', 0);
END;
--3
--Functions have same rules as for procedures. And remember that RETURN TYPE not
--influence. For example:
CREATE OR REPLACE PACKAGE PCK IS
--different types of arguments
FUNCTION FNC(x VARCHAR2) RETURN NUMBER;
FUNCTION FNC(x NUMBER) RETURN NUMBER;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
FUNCTION FNC(x VARCHAR2) RETURN NUMBER IS
res NUMBER;
BEGIN
res := x;
RETURN res;
END;
--
FUNCTION FNC(x NUMBER) RETURN NUMBER IS
res NUMBER;
BEGIN
res := x;
RETURN res;
END;
END;
SELECT PCK.FNC('11') FROM DUAL;
SELECT PCK.FNC(1) FROM DUAL;
Task 13.2. Working with Packages. Overloading. Part 2.
1. Demonstrate overloading TO_CHAR function.
2. Demonstrate situation: procedure and function have same name in package.
How to execute procedure and function and which result will be?
Solution:
--1
CREATE OR REPLACE PACKAGE PCK IS
FUNCTION TO_CHAR(x NUMBER, y VARCHAR2) RETURN VARCHAR2;
PROCEDURE DISPLAY;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
FUNCTION TO_CHAR(x NUMBER, y VARCHAR2) RETURN 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 NUMBER) RETURN NUMBER;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PF(x NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(x + 22);
END;
FUNCTION PF(x NUMBER) RETURN 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 VARCHAR2) RETURN VARCHAR2;
v VARCHAR2(1) := '+';
--
PROCEDURE PRC(x VARCHAR2) IS
a VARCHAR2(100);
BEGIN
a := FNC(x);
DBMS_OUTPUT.PUT_LINE(a);
END;
--
FUNCTION FNC(x VARCHAR2) RETURN VARCHAR2 IS
res VARCHAR2(100);
BEGIN
res := x||v;
RETURN res;
END;
END;
EXEC PCK.PRC('abc');
Task 13.4. Persistent State of packages.
1. What mean package state?
2. What mean stateful and stateless package?
3. Demonstrate persistent state of package 'PCK' using global variable 'vv' in
this package and procedure 'PRC'.
4. Remake point 3 and demonstrate alternative behaviour of persistent state.
Solution:
--1
The values of the variables, constants, and cursors that a package declares (in
either its specification or body) comprise its package state.
--2
If a PL/SQL package declares at least one variable, constant, or cursor, then
the package is stateful; otherwise, it is stateless.
--3
--Global variable in package it is variable that defined in package
--specification.
CREATE OR REPLACE PACKAGE PCK IS
vv NUMBER := 1;
PROCEDURE PRC(x NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC(x NUMBER) IS
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 NUMBER) IS
BEGIN
vv := x;
DBMS_OUTPUT.PUT_LINE(vv);
END;
END;
BEGIN
--1) Value of vv = 1
DBMS_OUTPUT.PUT_LINE(PCK.vv);
--2) value of vv after procedure will be 100 and will be persistent only for
--the duration of one call to the server (in parent, current, nested blocks)
PCK.PRC(100);
/* another way of change is: PCK.vv := 100; */
DBMS_OUTPUT.PUT_LINE(PCK.vv);
END;
BEGIN
--3) In another block vv will be again 1.
DBMS_OUTPUT.PUT_LINE(PCK.vv);
END;
Task 13.5. Persistent State of packages. Cursor.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER);
INSERT INTO TAB1(COL1) VALUES(1);
INSERT INTO TAB1(COL1) VALUES(2);
INSERT INTO TAB1(COL1) VALUES(3);
INSERT INTO TAB1(COL1) VALUES(4);
INSERT INTO TAB1(COL1) VALUES(5);
COMMIT;
Then do next:
1. Demonstrate persistent state for global cursor 'cur' in package 'PCK' using
TAB1. Explain it.
Solution:
--1
CREATE OR REPLACE PACKAGE PCK IS
CURSOR cur IS SELECT COL1
FROM TAB1;
PROCEDURE OPEN;
PROCEDURE CLOSE;
PROCEDURE LOOP_CUR;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE OPEN IS
BEGIN
IF NOT cur%ISOPEN THEN
OPEN cur;
END IF;
END;
--
PROCEDURE CLOSE IS
BEGIN
IF cur%ISOPEN THEN
CLOSE cur;
END IF;
END;
--
PROCEDURE LOOP_CUR IS
cur_row cur%ROWTYPE;
BEGIN
FOR i IN 1..2 LOOP
FETCH cur INTO cur_row;
IF cur%NOTFOUND THEN
CLOSE cur;
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(cur_row.COL1);
END LOOP;
END;
END;
--Now if run next block we retrive 2 rows from cursor with values: 1, 2. This
--cursor will be stored in memory and if we run block again we retrive next
--rows with values: 3, 4. Etc.
BEGIN
PCK.OPEN;
PCK.LOOP_CUR;
END;
--But if we close cursor, it is mean that cursor will be removed from memory and
--if we run previous block we beginning at first row.
BEGIN
PCK.CLOSE;
END;
Task 13.6. Using pl/sql tables in packages.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Create package with type 'tt' as associative array and procedure 'PRC' with
output parameter 'param' of this type which places rows from table TAB1 into
parameter. The array should look like this:
INDEX COL1 COL2
1 1 'one'
2 NULL 'two'
3 NULL 'three'
Then create block where call this procedure and display values of COL2 from
output parameter.
Solution:
--1
CREATE OR REPLACE PACKAGE PCK IS
TYPE tt IS TABLE OF TAB1%ROWTYPE
INDEX BY PLS_INTEGER;
PROCEDURE PRC(param OUT tt);
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC(param OUT tt) IS
cnt NUMBER := 0;
BEGIN
FOR i IN (SELECT COL1, COL2 FROM TAB1) LOOP
cnt := cnt + 1;
param(NVL(i.COL1, cnt)) := i;
END LOOP;
END;
END;
--
DECLARE
res PCK.tt;
BEGIN
PCK.PRC(res);
FOR i IN 1..res.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(res(i).COL2);
END LOOP;
END;
CHAPTER 14. "Using oracle-supplied Packages"
Task 14.1. DBMS_OUTPUT.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Which minimum and maximum buffer size? Which default buffer size? What type
of buffer size parameter and minimum and maximum values of this parameter.
2. Place in buffer text "my" (two syntax).
3. Place in buffer all values between 1 - 7000 using loop.
4. Write block with 5 operations: place in buffer number 1, then switch off
DBMS_OUTPUT, then place in buffer number 2, then turn on DBMS_OUTPUT and finally
place in buffer number 3. What will be in buffer?
5. Without concatenation place in buffer at first row 'a','b'. Then using
concatenation in next row place 'c','d'.
6. Place in buffer string 'a'. Place in buffer string 'b'. Then retrieve first
string from buffer to variable. Write result and explain.
7. Place in buffer string 'a'. Place in buffer string 'b'. Then retrieve all
strings from buffer to variable. Display result from variable and explain.
Solution:
--1
Minimum is 2 000 bytes. Maximum is unlimited. Default is 20 000.
Type is INTEGER. Min. value is 2 000, max. value is 1 000 000.
--2
BEGIN
/* 1 */ DBMS_OUTPUT.PUT('my'); DBMS_OUTPUT.NEW_LINE;
/* 2 */ DBMS_OUTPUT.PUT_LINE('my');
END;
--3
BEGIN
DBMS_OUTPUT.ENABLE(50000); --DBMS_OUTPUT.ENABLE(-50000);
FOR i IN 1..7000 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
--4
BEGIN
DBMS_OUTPUT.PUT_LINE(1);
DBMS_OUTPUT.DISABLE; --this will clear buffer and switch off DBMS_OUTPUT
DBMS_OUTPUT.PUT_LINE(2);
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(3); --only this value will be placed in buffer
END;
--5
BEGIN
DBMS_OUTPUT.PUT('a'); DBMS_OUTPUT.PUT('b');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('c'||'d');
END;
--6
DECLARE
buffer VARCHAR2(100);
status INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('a');
DBMS_OUTPUT.PUT_LINE('b');
DBMS_OUTPUT.GET_LINE(buffer, status); --retrieves first line 'a' from buffer
DBMS_OUTPUT.PUT_LINE('Buffer = '||buffer);
--if success then status retrive 0, otherwise 1 (for example buffer=NULL).
DBMS_OUTPUT.PUT_LINE('Status = '||status);
END;
--7
DECLARE
--also this array can be TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER
buffer DBMS_OUTPUT.CHARARR;
lines NUMBER := 2;
BEGIN
DBMS_OUTPUT.PUT_LINE('a');
DBMS_OUTPUT.PUT_LINE('b');
DBMS_OUTPUT.GET_LINES(buffer, lines); --first arg - OUT, second arg is IN OUT
DBMS_OUTPUT.PUT_LINE(buffer(1));
DBMS_OUTPUT.PUT_LINE(buffer(2));
DBMS_OUTPUT.PUT_LINE(lines);
END;
Task 14.2. UTL_FILE.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
Create on disk (for example D) file with path 'D:\ex\file.txt' with data:
1 one
two
3
Then do next:
1. Which actions need to do before using UTL_FILE capabilities?
2. Make block where read and display all rows from 'file.txt'.
3. Make block where create 'file2.txt' using code and write all rows from TAB1
into 'file2.txt'. Then add row 'new' into 'file2.txt'.
4. How to see list of directories in database? List first 7 UTL_FILE-exceptions.
Solution:
--1
--Open SQLPLUS.
--Connect and paste password
SYS AS SYSDBA
--Then check plugable database if we have Oracle 12c.
SHOW CON_NAME
--and connect to plugable database
ALTER SESSION SET CONTAINER=orclpdb
/
--Then create directory DIR as 'D:\ex' and grant privs
CREATE DIRECTORY DIR AS 'D:\ex'
/
GRANT READ, WRITE 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 =>
RECIPIENTS =>
--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 VARCHAR2) RETURN 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 =>
RECIPIENTS =>
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_FILENAME => 'photo.jpg',
ATT_MIME_TYPE => 'image/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 VARCHAR2) RETURN 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 =>
RECIPIENTS =>
SUBJECT => 'Theme with text file',
MESSAGE => 'MyMessage',
MIME_TYPE => 'text; charset=us-ascii',
ATTACHMENT => GET_FILE('DIR','file.txt'),
ATT_INLINE => FALSE,
ATT_FILENAME => 'file.txt',
ATT_MIME_TYPE => 'text/Plain'
);
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. Checks the statement syntax, validating the statement, ensure all
referencing objects are correct, the privilege exists.
Bind. Checks the bind variables if the statement contains bind variables.
Execute. Executes the statement (non queries statements).
Fetch. Retrieves 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 all data in the table TAB1.
2. Create procedure 'PRC' using execute immediate with variable 'res' that create
table with two parameters: 'pt' - name of table, 'pc' - content for VALUES
clause. What privileges you must have?
Solution:
--1
CREATE OR REPLACE PROCEDURE PRC (x VARCHAR2) IS
v_rows NUMBER;
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM '||x;
v_rows := SQL%ROWCOUNT;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_rows||' rows deleted');
END;
EXEC PRC('TAB1');
--2
--We must have CREATE TABLE privilege, not ROLE, because in dynamic SQL we create
--table directly. Example: need login as SYS user, switch container by command:
--'ALTER SESSION SET CONTAINER=ORCLPDB;' and run 'GRANT CREATE TABLE TO HR;'
CREATE OR REPLACE PROCEDURE PRC (pt VARCHAR2, pc VARCHAR2) IS
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. USING. INTO.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. What do USING? What literals can not be used? How use NULL in USING?
2. Demonstrate USING. Create procedure 'PRC' where insert values to table with
parameters. Then insert 4 and 'four' into TAB1.
3. Remake point 2 using anonymous block with variables.
4. What do INTO?
5. Write block where display in DBMS_OUTPUT result (two types of variables) of
EXECUTE IMMEDIATE which make SELECT to TAB1 with WHERE equal variable 'v'.
6. Using EXECUTE IMMEDIATE make function that return row from 'SELECT * to TAB1
WHERE COL1' = parameter. Write block where use this function for COL1 = 2 and
display result.
7. Create procedure 'PRC' that update TAB1 and demonstrate situation when
specify all parameter modes.
8. Using TAB1 demonstrate situation when you can`t specify a parameter mode.
Explain.
Solution:
--1
USING is used to hold all bind arguments. The default parameter mode is IN.
We can`t use boolean literals (TRUE, FALSE, and NULL) directly in USING, but we
can use variable with value = NULL or TRUE or FALSE
--2
CREATE OR REPLACE PROCEDURE PRC(pt VARCHAR2, p1 VARCHAR2, p2 VARCHAR2) IS
BEGIN
--You cannot use placeholders for the names of schema objects
EXECUTE IMMEDIATE 'INSERT INTO '||pt||' VALUES(:a, :b)'
USING p1, p2; --USING p1, NULL; !!! will be error, because NULL directly
COMMIT;
END;
EXEC PRC('TAB1','4', 'four');
--3
DECLARE
v1 VARCHAR2(100) := 'TAB1';
v2 VARCHAR2(100) := '4';
v3 VARCHAR2(100) := 'four';
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO '||v1||' VALUES(:a, :b)'
USING v2, v3;
COMMIT;
END;
--4
--INTO used for single-row queries and place result of EXECUTE IMMEDIATE into
--variables or records. Can combine with USING.
--5
DECLARE
var VARCHAR2(100);
rec TAB1%ROWTYPE;
v VARCHAR2(100) := '2';
BEGIN
--variable
EXECUTE IMMEDIATE 'SELECT COL1 FROM TAB1 WHERE COL1 = :v'
INTO var
USING v;
DBMS_OUTPUT.PUT_LINE(var);
--record
EXECUTE IMMEDIATE 'SELECT * FROM TAB1 WHERE COL1 = :v'
INTO rec
USING v;
DBMS_OUTPUT.PUT_LINE(rec.COL1||rec.COL2);
END;
--6
CREATE OR REPLACE FUNCTION FNC (x NUMBER) RETURN TAB1%ROWTYPE IS
res TAB1%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'SELECT COL1, COL2 FROM TAB1 WHERE COL1=:x'
INTO res
USING x;
RETURN res;
END;
DECLARE
rec TAB1%ROWTYPE;
BEGIN
rec := FNC(2);
DBMS_OUTPUT.PUT_LINE(rec.COL1||rec.COL2);
END;
--7
CREATE OR REPLACE PROCEDURE PRC(pin IN NUMBER,
pout OUT VARCHAR2,
pinout IN OUT NUMBER) IS
BEGIN
UPDATE TAB1
SET COL1 = pinout - 1
WHERE COL1 = pin
RETURNING COL1, COL2 INTO pinout, pout;
COMMIT;
END;
--
DECLARE
vin NUMBER := 1;
vout TAB1.COL2%TYPE;
vinout NUMBER := 1;
BEGIN
EXECUTE IMMEDIATE 'BEGIN PRC(:i, :o, :io); END;'
USING IN vin, OUT vout, IN OUT vinout;
DBMS_OUTPUT.PUT_LINE(vout||' '||vinout);
END;
--8
DECLARE
vin NUMBER := 2;
vout VARCHAR2(100);
text VARCHAR2(1000);
BEGIN
text := 'DELETE TAB1
WHERE COL1 = :i
RETURNING COL2 INTO :o';
EXECUTE IMMEDIATE text
USING IN vin RETURNING INTO vout; --Can`t write OUT or IN OUT. Only IN.
--EXECUTE IMMEDIATE with a RETURNING
--clause is used to execute dynamic UPDATE, INSERT, or DELETE statements
--only. We can`t use it for subprogram from point 7.
COMMIT;
DBMS_OUTPUT.PUT_LINE(vout);
END;
Task 15.4. Execute immediate. Part 3.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'a');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'b');
INSERT INTO TAB1(COL1, COL2) VALUES(3, 'a');
INSERT INTO TAB1(COL1, COL2) VALUES(4, 'b');
INSERT INTO TAB1(COL1, COL2) VALUES(5, 'c');
INSERT INTO TAB1(COL1, COL2) VALUES(6, 'c');
COMMIT;
Then do next:
1. What mean REF cursor.
2. Write block where using 3 different REF cursors (and explain types of these
cursors):
- at first way display values of TAB1 where TAB1.COL2 = 'a';
- at second way display values of TAB1 where TAB1.COL2 = 'b';
- at third way display values of TAB1 where TAB1.COL2 = 'c';
3. Remake point 2 only for demonstrating when we can assign REF CURSOR`s variable
to another variable and when not can? How reopen REF cursor?
4. Make procedure 'PRC' (with parameter 'p') which displays values of TAB1.COL2
where COL1 = parameter and procedure must display all rows from TAB1 if
parameter IS NULL. Write result for parameter: NULL; 2; 7.
Solution:
--1
REF cursor is cursor that can be opened many times with different queries.
--2
DECLARE
cur_row TAB1%ROWTYPE;
--WEAK type. Variable with system REF CURSOR type do not checks result
curs SYS_REFCURSOR; --WEAK cursor variable
--STRONG type. Checks result by type after RETURN... Need create variable
TYPE rct IS REF CURSOR RETURN TAB1%ROWTYPE;
curt rct; --STRONG cursor variable
--WEAK type. Do not checks result. Need create variable
TYPE rc IS REF CURSOR; --WEAK type
cur rc; --WEAK cursor variable
BEGIN
--first way
OPEN curs FOR SELECT COL1, COL2
FROM TAB1
WHERE COL2 = 'a';
LOOP
FETCH curs INTO cur_row;
EXIT WHEN curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
END LOOP;
CLOSE curs;
--second way
OPEN curt FOR SELECT COL1, COL2
FROM TAB1
WHERE COL2 = 'b';
LOOP
FETCH curt INTO cur_row;
EXIT WHEN curt%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
END LOOP;
CLOSE curt;
--third way
OPEN cur FOR SELECT COL1, COL2
FROM TAB1
WHERE COL2 = 'c';
LOOP
FETCH cur INTO cur_row;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
END LOOP;
CLOSE cur;
END;
--3
--We can assign REF CURSOR`s variable only to REF CURSOR`s variable (STRONG
--cursor can be assigned to STRONG cursor if they have same TYPE and RETURN type).
DECLARE
cur_row TAB1%ROWTYPE;
--WEAK type. Variable with system REF CURSOR type do not checks result
curs SYS_REFCURSOR; --WEAK cursor variable
--STRONG type. Checks result by type after RETURN... Need create variable
TYPE rct IS REF CURSOR RETURN TAB1%ROWTYPE;
curt rct; --STRONG cursor variable
--WEAK type. Do not checks result. Need create variable
TYPE rc IS REF CURSOR; --WEAK type
cur rc; --WEAK cursor variable
--standard cursor
CURSOR std_cursor IS SELECT * FROM TAB1;
BEGIN
--NOT VALID
--1
--OPEN curs FOR SELECT * FROM TAB1;
-- LOOP
-- FETCH curt INTO cur_row;
-- EXIT WHEN curt%NOTFOUND;
-- DBMS_OUTPUT.PUT_LINE(cur_row.COL1||cur_row.COL2);
-- END LOOP;
-- cur := curs;
--CLOSE curs;
--2
--OPEN std_cursor;
-- curs := std_cursor;
-- curt := std_cursor;
-- cur := std_cursor;
--CLOSE std_cursor;
--VALID
curs := curs; curt := curs; cur := curs;
--assigning opened cursor to another cursor automatically allows you
--work with data from opened cursor, until the opened cursor is closed
OPEN curs FOR SELECT * FROM TAB1;
curs := curs; curt := curs; cur := curs;
LOOP
FETCH curt INTO cur_row;
EXIT WHEN curt%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_row.COL1);
END LOOP;
CLOSE curs; --!!! You can reopen REF cursor without closing. Close recommended
--not valid, because area in memory binded with CURT closed by "CLOSE curs;"
--LOOP
-- FETCH curt INTO cur_row;
-- EXIT WHEN curt%NOTFOUND;
-- DBMS_OUTPUT.PUT_LINE(cur_row.COL1);
--END LOOP;
END;
--4
CREATE OR REPLACE PROCEDURE PRC (p NUMBER := NULL) IS
TYPE rt IS REF CURSOR;
cur RT;
cur_row TAB1.COL2%TYPE;
v_text VARCHAR2(100) := 'SELECT COL2 FROM TAB1';
BEGIN
IF p IS NULL THEN
OPEN cur FOR v_text;
ELSE
v_text := v_text || ' WHERE COL1 = :pp';
OPEN cur FOR v_text USING p;
--OPEN cur FOR v_text || ' WHERE COL1 = :pp' USING p;
END IF;
LOOP
FETCH cur INTO cur_row;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_row);
END LOOP;
CLOSE cur;
END;
BEGIN
PRC; --if NULL (default), then result is 'a, b, a, b, c, c'
PRC(2); --if 2, then result is 'b'
PRC(7); --if 7, then result is nothing, because cursor not will be looped.
END;
Task 15.5. Execute immediate. EXECUTE IMMEDIATE FOR block. DBMS_SQL.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Demonstrate execute immediate for anonymous block which contain variable 'a'
with value 'one' and this block displays in DBMS_OUTPUT this value.
2. Create procedure 'COMPILE_OBJECT' which compile any object: procedure,
function, package spec or package body.
3. Remake point 2 using DBMS_SQL.
4. Using DBMS_SQL create procedure 'PRC' with parameters: table name 'tn' and
table`s values (for VALUES clause inside parentheses). Procedure will be
insert values to TAB1 table and displays quantity of insert`s rows.
Then insert row in TAB1.COL1 = 4 and TAB1.COL2 = 'four'.
Solution:
--1
DECLARE
v_text VARCHAR2(500) :=
q'!
DECLARE
a VARCHAR2(100) := 'one';
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
END;
!';
BEGIN
EXECUTE IMMEDIATE v_text;
END;
--2
CREATE OR REPLACE PROCEDURE COMPILE_OBJECT(my_type VARCHAR2,
my_name VARCHAR2,
my_package_part VARCHAR2 := NULL) IS
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 := NULL) IS
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
DBMS_SQL.CLOSE_CURSOR(cur);
END;
--4
--DBMS objects and structures 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 VARCHAR2) IS
text VARCHAR2(2000) := 'INSERT INTO '||tn||' VALUES(:1, :2)';
cur NUMBER;
res NUMBER;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
--DBMS_SQL.PARSE(cur,
-- 'INSERT INTO '||tn||' VALUES(:1, :2)',
-- DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(cur, text, DBMS_SQL.NATIVE);
--
DBMS_SQL.BIND_VARIABLE(cur, ':1', t1);
DBMS_SQL.BIND_VARIABLE(cur, ':2', t2);
--
res := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Inserted '||res||' rows');
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 privileges of the owner. User does not require
privileges on underlying objects that the procedure accesses. User requires
privilege only to execute a procedure.
INVOKER DEFINER. Programs execute with the privileges of the calling user.
User requires privileges on the underlying objects that the procedure accesses.
--2
CREATE OR REPLACE PROCEDURE PRC /*AUTHID DEFINER*/ IS
BEGIN
UPDATE TAB1
SET COL1 = 3
WHERE COL2 = 'three';
DBMS_OUTPUT.PUT_LINE('Definer rights');
END;
--3
CREATE OR REPLACE PROCEDURE PRC AUTHID CURRENT_USER IS
BEGIN
UPDATE TAB1
SET COL1 = 3
WHERE COL2 = 'three';
DBMS_OUTPUT.PUT_LINE('Invoker rights');
END;
Task 16.2. Autonomous Transactions.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. What mean autonomous transactions? Where we can use it and can`t use.
2. Create procedure 'PRC' that insert value 'zero' into TAB1.COL2. Also create
procedure 'PRC2' that firstly insert value 'four' into TAB1.COL2, secondly call
'PRC', thirdly make 'ROLLBACK'. But result of 'PRC' must be saved. Explain it.
3. List features of autonomous transactions and for which objects using.
Solution:
--1
It is independent transactions started by another main transaction.
Autonomous transaction not allowed for packages and nested blocks. Allowed for
subprograms (for subprograms in packages too), anonymous head blocks or triggers.
--2
CREATE OR REPLACE PROCEDURE PRC IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TAB1(COL2) VALUES('zero');
COMMIT;
END;
CREATE OR REPLACE PROCEDURE PRC2 IS
BEGIN
INSERT INTO TAB1(COL2) VALUES('four');
PRC; --COMMIT from this procedure influence only to insert value 'zero'
ROLLBACK; --inserted value 'four' will be ROLLBACK
END;
--3
- Suspend the calling transaction until the autonomous transactions are
completed.
- If an autonomous transaction attempts to access a resource held by the main
transaction, a deadlock can occur.
- Autonomous transaction must be commited or rolling back if it`s DML operation.
Task 16.3. Passing parameters.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Which parameter always passed by reference? Explain.
2. Explain what mean passing by value and passing by reference.
3. For which operation will be used passing only by value?
4. Create procedure 'PRC' that contain SELECT COUNT(COL1) into variable 'x' WHERE
COL2 = 'x' and demonstrate values of parameters using passing by value. Show
result in anonymoys block for COL2 = 'two' and 'z'.
Remake this procedure using passing by reference.
Solution:
--1
IN parameter, because it can not be changed. Uses same memory location.
--2
Use for OUT, IN 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 OUT, IN OUT parameter. Also
it takes time to copy the data to the temporary buffer and back to the parameter.
- Pass by reference (NOCOPY). Temporary buffer not creates and new parameter
value are place directly to the parameter. If unsuccessful completion of the
procedure, the parameter store new value.
--3
The remote procedure call (RPC) protocol.
--4
--Pass by value (default) - without NOCOPY
--Pass by reference - using NOCOPY. Need remove comments.
CREATE OR REPLACE PROCEDURE PRC (x IN OUT /*NOCOPY*/ VARCHAR2) IS
BEGIN
SELECT COUNT(COL1)
INTO x
FROM TAB1
WHERE COL2 = x;
IF x = 0 THEN
RAISE NO_DATA_FOUND;
END IF;
END;
DECLARE
res VARCHAR2(100) := 'two'; --res VARCHAR2(100) := 'z';
BEGIN
DBMS_OUTPUT.PUT_LINE('IN = '||res);
PRC(res);
DBMS_OUTPUT.PUT_LINE('OUT = '||res);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('UNSUCCESSFUL = '||res);
END;
Task 16.4. Restrictions of NOCOPY hint.
1. List situations, when NOCOPY not working.
Solution:
--1
1) Actual parameter:
- is an element of an index-by-table (!!! but parameter as index-by-table will be
working with NOCOPY);
- is constrained (for example, NOT NULL or scale for NUMBER);
This restriction does not apply to size-constrained character strings.
This restriction does not extend to constrained elements or attributes of
composite types;
- actual and formal parameter are records, where one or both records were
declared by using %ROWTYPE or %TYPE, and constraints on corresponding fields in
the records differ;
- requires an implicit data type conversion.
2) The subprogram is called through a database link or as an external procedure.
Task 16.5. Using the PARALLEL_ENABLE hint.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TAB1(COL1, COL2) VALUES(i, 'val_'||i);
END LOOP;
COMMIT;
END;
Then do next:
1. Where can be used PARALLEL_ENABLE and why? For which Oracle edition available?
2. Demonstrate it and explain using TAB1.
Solution:
--1
Can be used in functions for performance 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 NUMBER) RETURN NUMBER PARALLEL_ENABLE IS
BEGIN
RETURN x * 3;
END;
--Oracle will open 2 processes to execute this query.
--Each process take subset of data.
SELECT /*+ PARALLEL(t1, 2) */
FNC(t1.COL1),
t1.COL2
FROM TAB1 t1;
Task 16.6. Using RESULT_CACHE.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. For which edition of database we can use it?
2. List benefits of RESULT_CACHE.
3. List restrictions of RESULT_CACHE.
4. Demonstrate RESULT_CACHE (two ways: before 11g and since 11g) using TAB1.
Explain it.
5. Explain using RESULT_CACHE at the package.
Solution:
--1
For ENTERPRISE edtition.
--2
1) Each time a result-cached PL/SQL function is called with different parameter
values, those parameters and their results are stored in cache.
2) The function result cache is stored in a shared global area (SGA) and
avalable to any sessions.
3) Subsequent calls to the same function with the same parameters uses the result
from cache.
4) Use with functions that are called frequently and dependent on information
that changes infrequently.
--3
1) If the database object, that used to compute the value, changed, then result
recomputed using new object. For example, if we INSERT new row into table and
function uses with this table, then result will be recomputed.
2) If function execution raised an unhandled exception, the exception results
is not stored in the cache.
--4
CREATE OR REPLACE FUNCTION FNC (x VARCHAR2) RETURN NUMBER
RESULT_CACHE /* RELIES_ON (TAB1) */
IS
res NUMBER;
BEGIN
SELECT COUNT(COL1)
INTO res
FROM TAB1
WHERE COL2 = x;
RETURN res;
END;
--then if we call function with parameter 'two', result will be stored in cache
--across sessions
SELECT FNC('two') FROM TAB1
--and if we call this function with parameter 'two' again, then result will be
--appeared faster
SELECT FNC('two') FROM TAB1
--RELIES_ON need before 11g and mean that if TAB1 (or any table or view)
--changed, then recompute result of function. Since 11g result recompute
--automatically without RELIES_ON.
--5
We can write RELIES_ON only for functions at the package body. In package spec
do not allowed write RELIES_ON.
Task 16.7. Using DETERMINISTIC.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. For which version and edition of database we can use it?
2. Demonstrate DETERMINISTIC using TAB1. Explain it.
3. List restrictions.
Solution:
--1
Since Oracle 8i ENTERPRISE edition.
--2
DETERMINISTIC save parameters and results of function ONLY for your session.
RESULT_CACHE preferable. DETERMINISTIC function works with SQL queries only.
Before using DETERMINISTIC, DBA make changes in these values:
- parameter shared_pool_size
- parameter result_cache_max_size
- parameter result_cache_mode
CREATE OR REPLACE FUNCTION FNC (x VARCHAR2) RETURN NUMBER DETERMINISTIC IS
res NUMBER;
BEGIN
SELECT COUNT(COL1)
INTO res
FROM TAB1
WHERE COL2 = x;
RETURN res;
END;
--3
- Do not specify DETERMINISTIC for a function whose result depends on the state
of session variables or schema objects.
- You must specify this keyword if you want to call function in expression
of a FUNCTION-BASED INDEX or from the query of a MATERIALIZED VIEW that is marked
REFRESH FAST or ENABLE QUERY REWRITE. If you subsequently change the semantics of
the function, you must manually rebuild all dependent function-based indexes and
materialized views.
Task 16.8. Bulk binding.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(2) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(3, 'three');
COMMIT;
Then do next:
1. Using bulk create procedure 'PRC' with number parameter 'p':
- load all values from COL2 into NESTED_TABLE 'arr';
- update TAB1.COL1 by value COL1 + parameter 'p' for each row where COL2 = value
from 'arr'. Display quantity of updated rows for each arr`s element.
Run procedure 'PRC' with parameter = 10. Write and explain result for TAB1.COL1.
2. Remake procedure 'PRC' with EXCEPTION section where display count of errors,
info about errors: index of element, code, message.
Recreate TAB1. Run procedure 'PRC' with parameter = 98. Write and explain result
for TAB1.COL1.
Solution:
--1
CREATE OR REPLACE PROCEDURE PRC (p NUMBER) IS
TYPE tt IS TABLE OF VARCHAR2(100);
arr tt;
BEGIN
SELECT COL2
BULK COLLECT INTO arr
FROM TAB1;
FORALL i IN arr.FIRST..arr.LAST SAVE EXCEPTIONS
UPDATE TAB1
SET COL1 = COL1 + p
WHERE COL2 = arr(i);
FOR i IN 1..arr.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(i)||' rows for element '||i);
END LOOP;
COMMIT;
END;
--result: 11, 12, 13
BEGIN
PRC(10);
END;
SELECT COL1 FROM TAB1;
--2
CREATE OR REPLACE PROCEDURE PRC (p NUMBER) IS
TYPE tt IS TABLE OF VARCHAR2(100);
arr tt;
ecount NUMBER;
BEGIN
SELECT COL2
BULK COLLECT INTO arr
FROM TAB1;
FORALL i IN arr.FIRST..arr.LAST SAVE EXCEPTIONS
UPDATE TAB1
SET COL1 = COL1 + p
WHERE COL2 = arr(i);
FOR i IN 1..arr.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(i)||' rows for element '||i);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ecount := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('COUNT '||ecount);
FOR j IN 1..ecount LOOP
DBMS_OUTPUT.PUT_LINE
('Index '||SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE
('Err_code ' ||SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
DBMS_OUTPUT.PUT_LINE
('Err_message '||SQLERRM( -SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
END LOOP;
END;
--result: 99, 2, 3
--for rows 'two' and 'three' update not worked, because length COL1 = 2
BEGIN
PRC(98);
END;
SELECT COL1 FROM TAB1;
Task 16.9. Bulk and cursors. Returning values with bulk.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Write block where, using bulk, load into nested table 'n' values from
cursor SELECT COL1 FROM TAB1. Then display values from nested table. Which result
will be? Which number of elements will be?
2. Write block where, using bulk, load into varray 'v' (size is 2 elements),
first 2 rows from cursor SELECT COL1, COL2 FROM TAB1. Then display values from
varray. Which result will be?
3. Create block where load all rows from TAB1 into nested table 'a'.
And if TAB1.COL2 = COL2 from 'a' then update TAB1.COL1 by value TAB1.COL1 * COL1
from 'a'. Also load the updated row into nested table 'b'. Display values from
'b'. Which result will be in 'b'?
Solution:
--1
--Result: 1, 2, NULL. Count of elements = 3.
DECLARE
TYPE tt IS TABLE OF NUMBER;
n tt; --OR YOU CAN WRITE: n tt := tt();
CURSOR cur IS SELECT COL1 FROM TAB1;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO n;
FOR i IN n.FIRST..n.LAST LOOP
DBMS_OUTPUT.PUT_LINE(n(i));
END LOOP;
CLOSE cur;
END;
--2
--Result: 1 one, 2 two.
DECLARE
TYPE tt IS VARRAY(2) OF TAB1%ROWTYPE;
v tt; --OR YOU CAN WRITE: v tt := tt();
CURSOR cur IS SELECT COL1, COL2 FROM TAB1;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO v LIMIT 2;
FOR i IN v.FIRST..v.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v(i).COL1||' '||v(i).COL2);
END LOOP;
CLOSE cur;
END;
--3
--Result: 1 one, 4 two, NULL three
DECLARE
TYPE tt IS TABLE OF TAB1%ROWTYPE;
a tt; --OR YOU CAN WRITE: a tt := tt();
b tt; --OR YOU CAN WRITE: b tt := tt();
BEGIN
SELECT COL1, COL2
BULK COLLECT INTO a
FROM TAB1;
--
FORALL i IN a.FIRST..a.LAST
UPDATE TAB1
SET COL1 = COL1 * a(i).COL1
WHERE COL2 = a(i).COL2
RETURNING COL1, COL2 BULK COLLECT INTO b;
COMMIT;
--
FOR i IN b.FIRST..b.LAST LOOP
DBMS_OUTPUT.PUT_LINE(b(i).COL1||' '||b(i).COL2);
END LOOP;
END;
Task 16.10. Looping sparse collection.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Create block where load TAB1.COL2 in index-by-table 'a'.
Then delete element with index 2 from 'a'.
Using loop display values from 'a' (two ways).
Then using bulk, update TAB1.COL1 by value 0 where TAB1.COL2 equal values
from 'a' (two ways).
Then using bulk, update TAB1.COL1 by value 33 where TAB1.COL2 equal values
from 'a' with index 3.
Solution:
--1
DECLARE
TYPE tt IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
a tt;
TYPE it IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
ind it;
idx NUMBER;
BEGIN
--
SELECT COL2
BULK COLLECT INTO a
FROM TAB1;
a.DELETE(2);
--
FOR i IN a.FIRST..a.LAST LOOP
IF a.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(a(i));
END IF;
END LOOP;
idx := a.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(a(idx));
idx := a.NEXT(idx);
END LOOP;
--
FORALL i IN INDICES OF a
UPDATE TAB1
SET COL1 = 0
WHERE COL2 = a(i);
COMMIT;
ind(-1) := 1;
ind(5) := 3;
FORALL i IN VALUES OF ind
UPDATE TAB1
SET COL1 = 0
WHERE COL2 = a(i);
COMMIT;
--
FORALL i IN INDICES OF a BETWEEN 3 AND 3
UPDATE TAB1
SET COL1 = 33
WHERE COL2 = a(i);
COMMIT;
END;
SELECT * FROM TAB1;