Different examples for training to exam Oracle Database 19c: Program with PL/SQL 1Z0-149. Part 3.
CHAPTER 17. "Creating triggers"
CHAPTER 18. "Creating Compound, DDL, and event Database Triggers"
CHAPTER 19. "PLSQL Compiler"
CHAPTER 20. "Managing PLSQL code"
CHAPTER 21. "Dependencies"
CHAPTER 22. "Another features"
CHAPTER 17. "Creating triggers"
Task 17.1. About triggers.
1. What is trigger? What is maximum size of trigger?
2. For which objects trigger can be defined?
3. List the trigger event types.
4. For which business scenarios we can use triggers?
5. List trigger types.
6. Where in dictionary we can find info about triggers?
7. For which schema object we can`t create trigger?
Solution:
--1
Trigger is a PL/SQL block that is stored in the database and automatically
executed in response to a specified event. 32k is maximum size of trigger.
--2
Table, view, schema or database (for all users).
--3
DML: DELETE, INSERT, UPDATE [OF column].
DDL: CREATE, ALTER, DROP.
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: BEFORE, AFTER, INSTEAD OF;
- Compound triggers;
- Non-DML triggers: DDL event triggers; database event triggers.
--6
SELECT * FROM USER_TRIGGERS
--7
SYS
Task 17.2. DML triggers. Part 1.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. List and explain two types of DML-trigger.
2. For TAB1 demonstrate two types of trigger when try to update (before):
- any columns;
- only column COL2.
Which triggers will be fired for next blocks:
--A
BEGIN
UPDATE TAB1 SET COL1 = 0 WHERE COL2 = 'two';
END;
--B
BEGIN
UPDATE TAB1 SET COL2 = 't' WHERE COL1 = 99;
END;
Solution:
--1
Statement-Level trigger:
- is the default for trigger;
- fires once for the triggering event;
- fires once even if no rows are affected.
Row-Level trigger:
- use the FOR EACH ROW clause when creating a trigger;
- fires once for each row affected by the triggering event;
- not fired if the triggering event not affected to any rows.
--2
--Statement-Level triggers
--Will be fired for A AND B
CREATE OR REPLACE TRIGGER TS_BU
BEFORE UPDATE
ON TAB1
BEGIN
DBMS_OUTPUT.PUT_LINE('S_level');
END;
--Will be fired for B
CREATE OR REPLACE TRIGGER TS_COL2_BU
BEFORE UPDATE OF COL2
ON TAB1
BEGIN
DBMS_OUTPUT.PUT_LINE('S-Level_COL2');
END;
--Row-Level triggers
--Will be fired for A
CREATE OR REPLACE TRIGGER TR_BU
BEFORE UPDATE
ON TAB1
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('R-Level');
END;
--Not will be fired
CREATE OR REPLACE TRIGGER TR_COL2_BU
BEFORE UPDATE OF COL2
ON TAB1
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('R-Level_COL2');
END;
Task 17.3. DML triggers. Part 2.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Write trigger for TAB1 that will be display '1' after any operation: DELETE,
UPDATE, INSERT. But if day from sysdate <= 29, then raise errors:
- if insert row then raise error with message 'Err_I';
- if update only COL2 then raise error with message 'Err_U';
- if delete row then raise error with message 'Err_D';
2. What priority for trigger on table and constraints for table?
Solution:
--1
CREATE OR REPLACE TRIGGER TRG_ADIU
AFTER DELETE OR
UPDATE OF COL2 OR
INSERT
ON TAB1
BEGIN
IF EXTRACT(DAY FROM SYSDATE) <= 29 THEN
IF INSERTING THEN
RAISE_APPLICATION_ERROR(-20001, 'Err_I');
ELSIF UPDATING THEN
RAISE_APPLICATION_ERROR(-20001, 'Err_U');
ELSIF DELETING THEN
RAISE_APPLICATION_ERROR(-20001, 'Err_D');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('1');
END IF;
END;
--2
Trigger have priority over constraint. Trigger executes first.
Task 17.4. DML triggers. New and old values.
Do next operations:
DROP TABLE TAB1;
DROP TABLE TLOG;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
CREATE TABLE TLOG(OLD_C1 NUMBER,
OLD_C2 VARCHAR2(100),
NEW_C1 NUMBER,
NEW_C2 VARCHAR2(100),
OPER VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Create trigger, that after insert, delete, update rows in TAB1, will load
old and new values and name of operation into TLOG. Indicate optional
construction in create trigger.
2. Create trigger that will be fired only if we insert into TAB1.COL1 value 4,
TAB1.COL2 value 'four' and trigger must raise error.
Solution:
--1
CREATE OR REPLACE TRIGGER TRG
AFTER INSERT OR
DELETE OR
UPDATE
ON TAB1
REFERENCING NEW AS NEW OLD AS OLD --optinal
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TLOG(NEW_C1, NEW_C2, OPER)
VALUES(:NEW.COL1, :NEW.COL2, 'INSERT');
ELSIF UPDATING THEN
INSERT INTO TLOG(OLD_C1, OLD_C2, NEW_C1, NEW_C2, OPER)
VALUES (:OLD.COL1, :OLD.COL2, :NEW.COL1, :NEW.COL2, 'UPDATE');
ELSIF DELETING THEN
INSERT INTO TLOG(OLD_C1, OLD_C2, OPER)
VALUES(:OLD.COL1, :OLD.COL2, 'DELETE');
END IF;
END;
--2
CREATE OR REPLACE TRIGGER TRG
BEFORE INSERT
ON TAB1
FOR EACH ROW
WHEN (NEW.COL1 = 4 AND NEW.COL2 = 'four') --only for row-level triggers
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Error');
END;
Task 17.5. DML triggers. Generating value.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ
START WITH 1
MINVALUE 1
MAXVALUE 100
INCREMENT BY 1;
Then do next:
1. Create trigger for TAB1 if we insert new row and COL1 = NULL, then use
sequence value. For NEW and OLD use aliases N and O.
Then insert row: COL1 = NULL, COL2 = 'v'.
When we can`t use OLD and NEW pseudorecords?
Solution:
--1
--NEW trigger variables CAN BE CHANGED in BEFORE row insert or update triggers.
--OLD values can ONLY BE READ and not changed.
CREATE OR REPLACE TRIGGER TRG
BEFORE INSERT
ON TAB1
REFERENCES NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF :N.COL1 IS NULL THEN
:N.COL1 := SEQ.NEXTVAL; --:O.COL1 := SEQ.NEXTVAL; will fail
END IF;
END;
INSERT INTO TAB1 VALUES(NULL, 'v');
COMMIT;
Task 17.6. DML triggers. Firing sequence. ALTER.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
CREATE OR REPLACE TRIGGER TRG
BEFORE UPDATE
ON TAB1
BEGIN
DBMS_OUTPUT.PUT_LINE('S_level');
END;
Then do next:
1. List trigger firing sequence.
2. How to compile trigger?
3. How to disable all triggers on a table?
4. How to enable all triggers on a table?
5. How to disable and enable only trigger TRG?
6. How to drop trigger? When we must drop trigger?
Solution:
--1
BEFORE statement trigger
BEFORE row trigger
AFTER row trigger
AFTER statement trigger
--2
ALTER TRIGGER TRG COMPILE;
--3
ALTER TABLE TAB1 DISABLE ALL TRIGGERS;
--4
ALTER TABLE TAB1 ENABLE ALL TRIGGERS;
--5
ALTER TRIGGER TRG DISABLE;
ALTER TRIGGER TRG ENABLE;
--6
--we must drop TRIGGER with same name created for another table before try to
--CREATE OR REPLACE TIGGER with this name on different table.
DROP TRIGGER TRG;
Task 17.7. DML triggers for view.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
CREATE OR REPLACE VIEW VV AS
SELECT COL1, COL2 FROM TAB1;
Then do next:
1. Which trigger can be using only for view?
2. Create trigger on view that if we insert row in view, row not will be inserted
in table, but display in dbms_output text '10'.
Then make operation: INSERT INTO VV(COL1, COL2) SELECT COL1, COL2 FROM TAB1.
Which result will be in view and TAB1? Which message will be in: DBMS_OUTPUT, in
OUTPUT window and value at the SQL%ROWCOUNT after insert?
Solution:
--1
INSTEAD OF
--2
--fired for each row of the view that gets modified, for example:
--INSTEAD OF INSERT OR UPDATE OR DELETE
CREATE OR REPLACE TRIGGER TRG
INSTEAD OF INSERT
ON VV
--FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('10');
END;
--Rows not will be added in view and table.
--DBMS_OUTPUT: 10 10 10; in output window: "3 rows inserted."; SQL%ROWCOUNT = 3.
INSERT INTO VV(COL1, COL2) SELECT COL1, COL2 FROM TAB1;
Task 17.8. DML triggers. Another features.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) PRIMARY KEY);
INSERT INTO TAB1(COL1) VALUES(1);
INSERT INTO TAB1(COL1) VALUES(2);
COMMIT;
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL1 NUMBER(5) CHECK(COL1 > 0));
Then do next:
1. Create trigger 'TRG' that will insert TAB2.COL1 = COL1 + 5 and this insert
must be COMMITED even if INSERT in TAB1 failed. If INSERT in TAB2 will fail then
insert in TAB1 must be success. Insert in TAB1: COL1 = 1, COL1 = -100.
Explain result in both tables.
Solution:
--1
CREATE OR REPLACE TRIGGER TRG
BEFORE INSERT
ON TAB1
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TAB2(COL1) VALUES(:NEW.COL1 + 5);
--TCL commands: COMMIT, ROLLBACK, SAVEPOINT allowed in triggers with
--PRAGMA AUTONOMOUS_TRANSACTION;
COMMIT; --Only commits the preceding INSERT, not the INSERT that fired
--the trigger.
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
INSERT INTO TAB1(COL1) VALUES(1);
COMMIT; --in TAB1: 1, 2. In TAB2: 6.
INSERT INTO TAB1(COL1) VALUES(-100);
COMMIT; --in TAB1: -100, 1, 2. In TAB2: 6.
CHAPTER 18. "Creating Compound, DDL, and event Database Triggers"
Task 18.1. Sequence of execution of triggers.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. For TAB1 create trigger 'TRG1' with BEFORE INSERT that will be displayed
text '1'. Then create second trigger 'TRG2' with BEFORE INSERT, but this trigger
must be displayed '2' and fired only after trigger 'TRG1'. Also create trigger
'TRG3' with BEFORE INSERT that will be displayed text '3' and fired only after
triggers 'TRG1' and 'TRG2'.
Solution:
--1
CREATE OR REPLACE TRIGGER TRG1
BEFORE INSERT
ON TAB1
BEGIN
DBMS_OUTPUT.PUT_LINE('1');
END;
CREATE OR REPLACE TRIGGER TRG2
BEFORE INSERT
ON TAB1
FOLLOWS TRG1
BEGIN
DBMS_OUTPUT.PUT_LINE('2');
END;
CREATE OR REPLACE TRIGGER TRG3
BEFORE INSERT
ON TAB1
FOLLOWS TRG1, TRG2
BEGIN
DBMS_OUTPUT.PUT_LINE('3');
END;
Task 18.2. Compound triggers.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Since which version available compound trigger?
2. For TAB1 create compound trigger 'TRG' which will be firing for inserting,
updating or deleting and that will be displayed text 'my' from common variable
'v' with string type + value from specific string`s variable 'r' that will be
indicate each part of trigger.
Insert two rows: without subquery; using subquery. For each part of trigger
write quantity of firing.
Solution:
--1
11g
--2
--TIMING must be sequential. Quantity of TIMING can be 1, 2, 3 or 4.
--TIMING`s duplicates not allowed.
CREATE OR REPLACE TRIGGER TRG
FOR INSERT OR
UPDATE OR
DELETE
ON TAB1
COMPOUND TRIGGER
v VARCHAR2(100) := 'my'; --will be available in all 4 blocks
--1
BEFORE STATEMENT IS
r VARCHAR2(100) := '1'; --will be available only in this block
BEGIN
DBMS_OUTPUT.PUT_LINE(v||r);
END BEFORE STATEMENT;
--2
BEFORE EACH ROW IS
r VARCHAR2(100) := '2';
BEGIN
DBMS_OUTPUT.PUT_LINE(v||r);
END BEFORE EACH ROW;
--3
AFTER EACH ROW IS
r VARCHAR2(100) := '3';
BEGIN
DBMS_OUTPUT.PUT_LINE(v||r);
END AFTER EACH ROW;
--4
AFTER STATEMENT IS
r VARCHAR2(100) := '4';
BEGIN
DBMS_OUTPUT.PUT_LINE(v||r);
END AFTER STATEMENT;
END;
--Parts of trigger will be firing:
--without subquery
INSERT INTO TAB1 VALUES(4, 'four'); --1 2 3 4
INSERT INTO TAB1 VALUES(5, 'five'); --1 2 3 4
--with subquery
INSERT INTO TAB1
SELECT * FROM (
SELECT 4, 'four' FROM DUAL
UNION ALL
SELECT 4, 'four' FROM DUAL
); -- 1 2 2 3 3 4
Task 18.3. Mutating table.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
DROP TABLE TAB2;
CREATE TABLE TAB2(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
Then do next:
1. Explain error of mutating table.
2. Create NONcompound trigger 'TRG' on TAB2 for demonstrating mutating table.
Solution:
--1
When we try DELETE, INSERT, UPDATE table, can be occurs error of mutating table
when a row-level trigger attempts to read or change this table from which the
trigger was fired.
--2
CREATE OR REPLACE TRIGGER TRG
BEFORE INSERT --AFTER INSERT
ON TAB2
FOR EACH ROW
DECLARE
res NUMBER;
BEGIN
SELECT COALESCE(MAX(COL1), 0) + 1
INTO res
FROM TAB2;
:NEW.COL1 := res;
END;
--FOR BEFORE INSERT:
--If we try read and then insert 1 row - not will be error
INSERT INTO TAB2(COL2) VALUES ('val');
--When we try insert and read rows simultaneously (insert operations > 1)
--that will be error of mutating table.
INSERT INTO TAB2(COL2) SELECT COL2 FROM TAB1;
--FOR AFTER INSERT errors will be always
Task 18.4. Compound triggers for mutating table.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(10, 'three');
COMMIT;
Then do next:
1. For TAB1 create BEFORE trigger that will be raise error if we insert or update
new row with COL1 not between variables 'minv' = minimum of COL1 and 'maxv' =
maximum of COL1.
Solution:
--1
DROP TRIGGER TRG;
CREATE OR REPLACE TRIGGER TRG
FOR INSERT OR UPDATE
ON TAB1
COMPOUND TRIGGER
minv NUMBER;
maxv NUMBER;
--
BEFORE STATEMENT IS
BEGIN
SELECT MIN(COL1), MAX(COL1)
INTO minv, maxv
FROM TAB1;
END BEFORE STATEMENT;
--
BEFORE EACH ROW IS
BEGIN
IF :NEW.COL1 NOT BETWEEN minv AND maxv THEN
RAISE_APPLICATION_ERROR(-20000, 'Not between '||minv||' and '||maxv);
END IF;
END BEFORE EACH ROW;
END;
--will work
INSERT INTO TAB1 (COL1, COL2) VALUES(4, 'four');
INSERT INTO TAB1 SELECT * FROM TAB1;
COMMIT;
--not will work
INSERT INTO TAB1 (COL1, COL2) VALUES(11, 'eleven');
COMMIT;
Task 18.5. Trigger for mutating table and cascade deleting.
Do next operations:
DROP TABLE TAB1;
DROP TABLE CT;
CREATE TABLE TAB1(COL1 NUMBER PRIMARY KEY,
COL2 VARCHAR2(100));
CREATE TABLE CT (C1 NUMBER,
C2 VARCHAR2(100),
CONSTRAINT TT_FK FOREIGN KEY (C1)
REFERENCES TAB1(COL1) ON DELETE CASCADE);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(3, 'three');
INSERT INTO CT(C1, C2) VALUES(1, '111');
INSERT INTO CT(C1, C2) VALUES(2, '222');
COMMIT;
Then do next:
1. Demonstrate mutating table for cascade deleting.
Solution:
--1
--Error of mutating table will be performed, when we create trigger BEFORE DELETE
--FOR EACH ROW (or AFTER DELETE) on table CT, that will be read data from CT.
CREATE OR REPLACE TRIGGER TRG
BEFORE DELETE
ON CT
FOR EACH ROW
DECLARE
res NUMBER;
BEGIN
SELECT COUNT(*)
INTO res
FROM CT;
END;
--Then try to delete row 2 from TAB1
DELETE TAB1
WHERE COL1 = 2;
Task 18.6. Comparing triggers and procedures.
1. How defined trigger and procedure?
2. Which objects contain source code for triggers and procedures?
3. How invoke trigger and procedure?
4. What about DTL?
5. Which type of triggers (DML, DDL) will be fired if remove all rows from a
table by TRUNCATE TABLE statement?
Solution:
--1
CREATE OR REPLACE TRIGGER
CREATE OR REPLACE PROCEDURE
--2
SELECT * FROM USER_TRIGGERS;
SELECT * FROM USER_SOURCE;
--3
Triggers are implicitly invoke by DML, DDL, database events.
Procedures are explicitly invoke.
--4
COMMIT, SAVEPOINT, ROLLBACK not allowed in trigger (except autonomous triggers).
COMMIT, SAVEPOINT, ROLLBACK allowed in procedure.
--5
DML triggers - not fired; DDL triggers - fired.
Task 18.7. Creating DDL triggers.
Do next operations:
DROP TABLE T_LOG;
CREATE TABLE T_LOG
(DDL_COMMAND VARCHAR2(100),
OBJECT_OWNER VARCHAR2(100),
OBJECT_NAME VARCHAR2(100),
USERNAME VARCHAR2(100),
CREATE_DATE VARCHAR2(100));
Then do next:
1. Demonstrate trigger that allow create objects between 9 and 18 hours. Create
this trigger as not enabled.
2. Demonstrate trigger that will be insert into table T_LOG info about last DDL
operation. What will be writes in T_LOG if DDL operation failed?
Solution:
--1
CREATE OR REPLACE TRIGGER TRG_S
BEFORE CREATE
ON SCHEMA
DISABLE
BEGIN
IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 9 AND 17 THEN
RAISE_APPLICATION_ERROR(-20000, 'Create time is 9-18');
END IF;
END;
--2
--if DDL fail, then row not will be inserted into T_LOG, because AFTER DDL
CREATE OR REPLACE TRIGGER TRG_D
AFTER DDL
ON SCHEMA
BEGIN
INSERT INTO T_LOG
SELECT ORA_SYSEVENT,
ORA_DICT_OBJ_OWNER,
ORA_DICT_OBJ_NAME,
USER,
SYSDATE
FROM DUAL;
END;
Task 18.8. Creating system event triggers.
Do next operations:
DROP TABLE T_LOG;
CREATE TABLE T_LOG
(EVENT_DATE VARCHAR2(100),
EVENT_NAME VARCHAR2(100));
Then do next:
1. Which user can create system event trigger?
2. Create trigger (indicate which use for schema also and only for database) that
will be insert data about date of event and name of event into T_LOG when:
- error raised;
- user enter to the database;
- user exit from database;
- database is opened;
- database is closed (for which closing?);
- a role change occurs from standby to primary or from primary to standby.
Solution:
--1
SYS
--2
--Trigger and table must be created by SYS (for table can have privs to table),
--or user must have privs to create database triggers.
--ERROR RAISED.
--Can be on database and schema.
CREATE OR REPLACE TRIGGER TRG_E
AFTER SERVERERROR
ON DATABASE
BEGIN
INSERT INTO T_LOG
VALUES (SYSDATE, ORA_SYSEVENT);
END;
--USER ENTER TO THE DATABASE.
--Can be on database and schema.
CREATE OR REPLACE TRIGGER TRG_UEN
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO T_LOG
VALUES (SYSDATE, ORA_SYSEVENT);
END;
--USER EXIT FROM DATABASE.
--Can be on database and schema.
CREATE OR REPLACE TRIGGER TRG_UEX
BEFORE LOGOFF
ON DATABASE
BEGIN
INSERT INTO T_LOG
VALUES (SYSDATE, ORA_SYSEVENT);
END;
--DATABASE IS OPENED. For SHUTDOWN or SHUTDOWN IMMEDIATE.
--Can be on database only.
CREATE OR REPLACE TRIGGER TRG_DO
AFTER STARTUP
ON DATABASE
BEGIN
INSERT INTO T_LOG
VALUES (SYSDATE, ORA_SYSEVENT);
END;
--DATABASE IS SHUT DOWN. For SHUTDOWN or SHUTDOWN IMMEDIATE.
--Can be on database only.
CREATE OR REPLACE TRIGGER TRG_DS
BEFORE SHUTDOWN
ON DATABASE
BEGIN
INSERT INTO T_LOG
VALUES (SYSDATE, ORA_SYSEVENT);
END;
--AFTER DB_ROLE_CHANGE
--Can be on database only.
CREATE OR REPLACE TRIGGER TRG_DD
AFTER DB_ROLE_CHANGE
ON DATABASE
BEGIN
INSERT INTO T_LOG
VALUES (SYSDATE, ORA_SYSEVENT);
END;
Task 18.9. Alternative execute procedure in trigger. Benefits of database-event
trigger.
Do next operations:
DROP TABLE TAB1 CASCADE CONSTRAINT;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(3, 'three');
CREATE OR REPLACE PROCEDURE PRC(res IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(res);
END;
Then do next:
1. Create BEFORE INSERT on TAB1 trigger that will be execute procedure 'PRC'
directly with parameter = 1.
2. List benefits of database-event trigger.
3. What privileges are required to manage triggers:
- for enables you to create a trigger in any schema;
- enables you to create a trigger on DATABASE;
- if your trigger refers to any objects that are not in your schema.
Solution:
--1 Without ; at the end
CREATE OR REPLACE TRIGGER TRG
BEFORE INSERT
ON TAB1
CALL PRC(1)
--2
Improved data security:
- Provide enhanced and complex secutiry checks;
- Provide enhanced and complex auditing.
Improved data integrity:
- Enforce dynamic data integrity constraints;
- Enforce complex referential integrity constraints;
- Ensure that related operations are performed together implicitly.
--3
CREATE/ALTER/DROP(ANY) TRIGGER privilege
--
ADMINISTER DATABASE TRIGGER privilege
--
EXECUTE privilege
CHAPTER 19. "PLSQL Compiler"
Task 19.1. Compiler settings.
1. What mean PLSQL_CODE_TYPE, PLSQL_OPTIMIZE_LEVEL, PLSQL_CCFLAGS, PLSQL_WARNINGS
and explain each? Since which version this settings available?
2. Write what privs must grant and where we can find info about this? Indicate
default values.
3. What settings are recommended for fastest performance?
Solution:
--1
Initialization parameters for PL/SQL compilation.
PLSQL_CODE_TYPE - specifies the compilation mode for PL/SQL library units.
PLSQL_OPTIMIZE_LEVEL - specifies the optimization level to be used to compile
PL/SQL library units.
PLSQL_CCFLAGS - controls conditional compilation of each PL/SQL library unit
independently.
PLSQL_WARNINGS - enables or disables the reporting of warning messages by the
PL/SQL compiler.
Since version 10g.
--2
--Logins as sysdba
--ALTER SESSION SET CONTAINER=ORCLPDB;
--then GRANT SELECT ON V_$PARAMETER to HR;
SELECT NAME,
VALUE
FROM V$PARAMETER
WHERE NAME IN ('plsql_code_type', --default=INTERPRETED
'plsql_optimize_level', --default=2
'plsql_ccflags', --NULL
'plsql_warnings'); --DISABLE:ALL
--3
PLSQL_CODE_TYPE = NATIVE;
PLSQL_OPTIMIZE_LEVEL = 2;
Task 19.2. PLSQL_CODE_TYPE
Do next operations:
CREATE OR REPLACE PROCEDURE PRC IS
res NUMBER;
BEGIN
FOR i IN 1..100000000 LOOP
res := res + 1;
END LOOP;
END;
Then do next:
1. List and explain values for PLSQL_CODE_TYPE.
2. Switch session`s PLSQL_CODE_TYPE to non-default value, display parameter`s
value for procedure 'PRC'. How to make procedure work with new parameter`s value?
How much faster will be executed procedure?
Solution:
--1
INTERPRETED - PL/SQL library units will be compiled to PL/SQL bytecode format.
NATIVE - PL/SQL library units will be compiled to machine code.
- Unit have value of parameter when it was compiled. After changing parameter
need recompile library unit.
- If a PL/SQL library unit is compiled native, all subsequent automatic
recompilations of that unit will use native compilation.
--2
--switch session and after switch to NATIVE need recompile procedure
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; --can be only for DBA
SELECT *
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'PRC';
--after switched session just use 'compile'
ALTER PROCEDURE PRC COMPILE;
--or recompile procedure directly
ALTER PROCEDURE PRC COMPILE PLSQL_CODE_TYPE=NATIVE;
--near 2x faster
Task 19.3. PLSQL_OPTIMIZE_LEVEL
Do next operations:
DECLARE
v_start DATE;
a NUMBER;
b NUMBER;
v_end DATE;
BEGIN
v_start := SYSDATE;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MM:SS'));
FOR i IN 1..100000000 LOOP
a := i + 1;
b := a + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MM:SS'));
v_end := SYSDATE;
DBMS_OUTPUT.PUT_LINE(ROUND((v_end - v_start)*24*60*60));
END;
Then do next:
1. List and explain values for PLSQL_OPTIMIZE_LEVEL.
2. For block use different PLSQL_OPTIMIZE_LEVEL.
Solution:
--1
0 - Maintains the evaluation order and hence the pattern of side effects,
exceptions, and package initializations of Oracle9i and earlier releases.
Also removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and
restores the earlier rules for the evaluation of integer expressions.
Although code will run somewhat faster than it did in Oracle9i, use of level
0 will forfeit most of the performance gains of PL/SQL in Oracle 10g.
1 - Applies a wide range of optimizations to PL/SQL programs including the
elimination of unnecessary computations and exceptions, but generally does
not move source code out of its original source order.
2 - include level 1 and may move source code relatively far from its original
location.
3 - since version 11g. Include level 2 and enables procedure inlining, which is
an optimization process that replaces procedure calls with a copy of the
body of the procedure to be called.
--2
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
Task 19.4. PLSQL_WARNINGS. ALTER.
Do next operations:
CREATE OR REPLACE PROCEDURE PRC (a OUT VARCHAR2) IS
BEGIN
a := '1';
END;
Then do next:
1. What mean and since version available PLSQL_WARNINGS, which prefix have?
2. List and explain categories of warnings. Which categories we can disable?
3. How to set warning levels?
4. Demonstrate activating all warnings. Recompile 'PRC'.
After recompile see in dictionary info about PLSQL_WARNINGS for 'PRC'.
Also find errors in dictionary after recompiling.
5. How to activate: warnings from 2 different categories, one error from another
categorie. List numeric ranges of errors of all categories.
6. Activate one informational numeric warning without errors, three performance
numeric warnings without errors. Which value will be in PLSQL_WARNINGS?
7. How to disable another categories disabling only one category?
Solution:
--1
Since 10g, 11g, 12c integrated PL/SQL warnings with prefix PLW. It is mean that
subprograms can be compile:
- success with compilation warnings;
- failure with compilation error and compilation warnings.
--2
--We can disable or enable any categories.
- SEVERE. Messages for conditions that may cause unexpected behavior or wrong
results, such as aliasing problems with parameters.
- PERFORMANCE. Messages for conditions that may cause performance problems, such
as passing a VARCHAR2 value to a NUMBER column in an INSERT statement.
- INFORMATIONAL. Messages for conditions that do not have an effect on
performance or correctness, but that you may want to change to make the code more
maintainable, such as unreachable code that can never be executed.
- ALL. All warnings.
--3
- Declaratively. Using the PLSQL_WARNINGS initialization parameter.
- Programmatically. Using the DBMS_WARNING package.
--4
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER PROCEDURE PRC COMPILE;
SELECT *
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'PRC'
SELECT *
FROM USER_ERRORS
WHERE NAME = 'PRC'
--5
--5000-5999 for severe, 6000-6249 for informational, 7000-7249 for performance
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:SEVERE',
'ENABLE:PERFORMANCE',
'ERROR:07001';--it is will treat as error for
--warning 7001, not only warning
--6
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:6002,ENABLE:(7002, 7003, 7004)';
--Result:
--DISABLE:ALL, ENABLE: 6002, ENABLE: 7002, ENABLE: 7003, ENABLE: 7004
SELECT * FROM v$parameter WHERE name = 'plsql_warnings'
--7
ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:INFORMATIONAL';
Task 19.5. PLSQL_WARNINGS. DBMS_WARNING.
1. How to activate one category`s warnings? Using name of category display
status of this category.
2. How to ignore one specific warning 5001? Display current status for this
warning.
3. How to activate all categories for system (not session) using one procedure?
Display current status of all this warnings.
4. Display name of category using number of warning. What happen if we write
wrong number of warning?
Solution:
--1
BEGIN
DBMS_WARNING.ADD_WARNING_SETTING_CAT('SEVERE','ENABLE','SESSION');
END;
SELECT DBMS_WARNING.GET_WARNING_SETTING_CAT('SEVERE') FROM DUAL;
--2
BEGIN
DBMS_WARNING.ADD_WARNING_SETTING_NUM(5001,'DISABLE','SESSION');
END;
SELECT DBMS_WARNING.GET_WARNING_SETTING_NUM(5001) FROM DUAL;
--3
BEGIN
DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL', 'SYSTEM');
END;
SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;
--4
--For wrong number of warning will be error "invalid warning category"
SELECT DBMS_WARNING.GET_CATEGORY(5000) FROM DUAL;
CHAPTER 20. "Managing PLSQL code"
Task 20.1. Conditional compilation.
1. Which benefits of using conditional compilation?
2. List reserved preprocessor control tokens.
3. What mean directive?
4. Which packages use for conditional compilation?
5. What cannot appear in a conditional compilation directive?
Solution:
--1
- Allows using new features in new versions of database in PLSQL code and
simultaneously allow use this code in older version without removing code.
- Helps debugging or tracing in IDE and hide that functionality in the
application while it runs at a production site.
--2
$IF, $THEN, $ELSIF, $ELSE, $END, $$, $ERROR
--3
Directive is $$.
--4
DBMS_PREPROCESSOR, DBMS_DB_VERSION
--5
In anonymous block placeholders can`t appear in a conditional compilation
directives.
BEGIN
$IF 1=1 $THEN
:my := 5; --valid
DBMS_OUTPUT.PUT_LINE(:my);
$END
$IF :my = 5 $THEN --invalid
DBMS_OUTPUT.PUT_LINE('INVALID'||:my);
$END
END;
Task 20.2. DBMS_DB_VERSION. Using conditional compilation.
1. Write block where display version and release of database. After, display
text 'v12' if version of database = 12.
2. Create function 'FNC' returning year + IN parameter (number) that
will be use RESULT_CACHE if version of database >= 11.
3. Write block where display in DBMS_OUTPUT values of PLSQL_CODE_TYPE,
PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS.
4. Create procedure 'PRC' that will be displayed text 'PRC compiled', but first
verify PLSQL_OPTIMIZE_LEVEL and if this <> 3, show warning as error with text
'lvl3' when trying compiling.
Solution:
--1
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION);
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.RELEASE);
IF DBMS_DB_VERSION.VER_LE_12 OR
DBMS_DB_VERSION.VER_LE_12_1 OR
DBMS_DB_VERSION.VER_LE_12_2 THEN
DBMS_OUTPUT.PUT_LINE('v12');
END IF;
END;
--2
CREATE OR REPLACE FUNCTION FNC (a NUMBER) RETURN NUMBER
$IF DBMS_DB_VERSION.VERSION >= 11 $THEN RESULT_CACHE $END
IS
BEGIN
RETURN EXTRACT(YEAR FROM SYSDATE) + a;
END;
--3
BEGIN
DBMS_OUTPUT.PUT_LINE($$PLSQL_CODE_TYPE);
DBMS_OUTPUT.PUT_LINE($$PLSQL_OPTIMIZE_LEVEL);
DBMS_OUTPUT.PUT_LINE($$PLSQL_WARNINGS);
END;
--4
CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
$IF $$PLSQL_OPTIMIZE_LEVEL <> 3 $THEN
$ERROR 'lvl3' $END
$END
DBMS_OUTPUT.PUT_LINE('PRC compiled');
END;
Task 20.3. PLSQL_CCFLAGS
1. What is mean PLSQL_CCFLAGS?
2. Which priority have PLSQL_CCFLAGS over another parameters? Which priority
inside PLSQL_CCFLAGS?
3. Assign to PLSQL_CCFLAGS my_val=100. Explain it. Further create function 'FNC'
(using conditional compilation) that if value of my_val = 100, then return value
of my_val, otherwise return value of PLSQL_CCFLAGS.
How to see value of PLSQL_CCFLAGS from function?
4. Assign to PLSQL_CCFLAGS directives: first for PLSQL_CCFLAGS, second with
BOOLEAN value, third directive with same name of second and number value. Check
PLSQL_CCFLAGS and also check directives and if have second directive then display
text '1', for second directive - '2'.
5. Display in DBMS_OUTPUT value of unsigned directive, for example with name
'aaBB'. What result will be? Why block completed without warnings?
Solution:
--1
PLSQL_CCFLAGS contain user-defined inquiry directives.
--2
High priority, if we use $$ it is will be run before other parameters. Inside
PLSQL_CCFLAGS - priority is right to left.
--3
--PLSQL_CCFLAGS = '<v1>:<c1>,<v2>:<c2>,...,<vn>:<cn>'
--<v1> is unquoted PL/SQL identifier and can be a reserved word or a keyword.
--<c1> is one of the following: BOOLEAN, PLS_INTEGER or NULL.
ALTER SESSION SET PLSQL_CCFLAGS = 'my_val:100';
--After this we can display value from my_val and PLSQL_CCFLAGS.
--Value from my_val will be loading from PLSQL_CCFLAGS.
BEGIN
DBMS_OUTPUT.PUT_LINE($$my_val);
DBMS_OUTPUT.PUT_LINE($$PLSQL_CCFLAGS);
END;
--! need recompile if we make new ALTER SESSION SET PLSQL_CCFLAGS = 'my_val:...';
CREATE OR REPLACE FUNCTION FNC RETURN VARCHAR2 IS
res VARCHAR2(100);
BEGIN
res := $IF $$my_val = 100 $THEN
$$my_val
$ELSE $$PLSQL_CCFLAGS
$END;
RETURN res;
END;
SELECT FNC FROM DUAL;
--
SELECT * FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'FNC';
--4
ALTER SESSION SET PLSQL_CCFLAGS = 'PLSQL_CCFLAGS:true,a:true,a:1';
BEGIN
IF $$PLSQL_CCFLAGS THEN
DBMS_OUTPUT.PUT_LINE('PLSQL_CCFLAGS have value');
ELSIF $$a=1 THEN --directives will be checked right to left, can`t check BOOL
DBMS_OUTPUT.PUT_LINE('2');
END IF;
END;
--5
--result will be NULL
--completed without warnings, because warnings raised only in subprograms
BEGIN
DBMS_OUTPUT.PUT_LINE($$aaBB);
END;
Task 20.4. PLSQL_CCFLAGS, example for testing procedure. DBMS_PREPROCESSOR.
Predefined inquiry directives.
1. Using PLSQL_CCFLAGS create procedure that will be checked one parameter from
PLSQL_CCFLAGS and will be display time of starting, finishig procedure and also
make loop between 1 and variable 'v' with default value = 100000000 from
PLSQL_CCFLAGS.
If you assign new parameter to PLSQL_CCFLAGS how to recompile procedure using
old PLSQL_CCFLAGS values in procedure?
2. Demonstrate DBMS_PREPROCESSOR. Explain it.
3. Write block with nested block with label 'my' where write DBMS_OUTPUT with
directives and explain result:
- number of the source line;
- name of the current PL/SQL block;
- owner of current PL/SQL unit;
- type of the current PL/SQL unit;
Solution:
--1
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true,val:100000000';
CREATE OR REPLACE PROCEDURE PRC IS
v NUMBER := $$val;
BEGIN
IF $$debug THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'HH24:MI:SS'));
END IF;
--
FOR i IN 1..v LOOP --or FOR i IN 1..$$val LOOP
v := i;
END LOOP;
--
IF $$debug THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'HH24:MI:SS'));
END IF;
END;
EXEC PRC;
--even if assigns 'debug:false', procedure would have 'debug:true'
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:false,val:100000000';
ALTER PROCEDURE PRC COMPILE REUSE SETTINGS;
EXEC PRC;
--2
--It will printing in DBMS_OUTPUT code of procedure with actual values for
--directives.
CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
('PROCEDURE','HR','PRC');
--3
BEGIN
BEGIN <<my>>
DBMS_OUTPUT.PUT_LINE($$PLSQL_LINE); --3
DBMS_OUTPUT.PUT_LINE($$PLSQL_UNIT); --NULL. Retrieve name of head block (not
--nested block or nested subprogram from
--declare section)
DBMS_OUTPUT.PUT_LINE($$PLSQL_UNIT_OWNER); --NULL
DBMS_OUTPUT.PUT_LINE($$PLSQL_UNIT_TYPE); --ANONYMOUS BLOCK
--retrieve type of head PL/SQL block
--(not nested block or nested
--subprogram from declare section)
END my;
END;
Task 20.5. Obfuscation.
1. What mean obfuscation?
2. How to make obfuscation?
3. List benefits of obfuscation.
Solution:
--1
Obfuscation (wrapping), of a PL/SQL unit is the process of hiding the PL/SQL
source code.
--2
Wrapping can be done with the WRAP utility and DBMS_DDL subprograms.
--3
- Others can`t see your source code.
- Your source code is not visible through the USER_SOURCE, ALL_SOURCE, or
DBA_SOURCE data dictionary views.
- SQL*Plus can process the obfuscated source files.
- The Import and Export utilities accept wrapped files. You can back up or move
wrapped procedures.
Task 20.6. DBMS_DDL.
Do next operations:
CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE('my');
END;
Then do next:
1. Remake procedure 'PRC' using wrapping from DBMS_DDL (two ways). Explain
result. How to restore normal code?
Solution:
--1
--procedure
DBMS_DDL.CREATE_WRAPPED
--function
DBMS_DDL.WRAP
--For restoring need recompile original procedure again.
--First way
BEGIN
DBMS_DDL.CREATE_WRAPPED
(
'CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE(''my'');
END;'
);
END;
--Source code will be replaced to text 'PROCEDURE PRC wrapped ...'
SELECT *
FROM USER_SOURCE
WHERE NAME = 'PRC';
--Second way
--Code from res we must compile manually and then procedure will be wrapped.
DECLARE
v VARCHAR2(4000) :='CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE(''my'');
END;';
res VARCHAR2(4000);
BEGIN
res := DBMS_DDL.WRAP(v);
DBMS_OUTPUT.PUT_LINE(res);
END;
Task 20.7. Wrapper utility.
Do next operations:
CREATE TABLE TAB1(COL1 NUMBER,
COL2 VARCHAR2(100));
CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE('my');
END;
Then do next:
1. With which objects WRAP working?
2. Which argument is mandatory? Which argument is optional?
3. Which default extension for input and output files?
4. Where spaces not allowed?
5. Demonstrate WRAP for table TAB1 and procedure 'PRC' using all mandatory and
optional arguments. Explain result, which objects will be wraped?
6. Which errors wrapper detects?
7. Which part of package can wrap?
8. Indicate YES or NO for DBMS_DDL and WRAP utility for next functionality:
- Code obfuscation;
- Dynamic obfuscation;
- Obfuscate multiple programs at a time.
Solution:
--1
TYPE BODY, PROCEDURE, FUNCTION, PACKAGE BODY
--2
--mandatory
INAME
--optional
ONAME
--3
input is .sql
output is .plb
--4
Spaces not allowed around equal sign.
--5
--Save code of procedure to file 'prc.sql' on disk D
--Open CMD Windows, switch to disk D by command:
CD /D D:\
--Then in CMD run:
WRAP INAME=PRC.SQL ONAME=PRC_RES.PLB
--Then in SQL Developer make:
DROP TABLE TAB1;
DROP PROCEDURE PRC;
--Then in SQL Developer run command:
@d:\PRC_RES.PLB
--Check code
--In file PRC_RES.PLB table not will be wrapped, procedure will be (in database
--too).
SELECT * FROM USER_SOURCE WHERE NAME = 'PRC';
--6
Wrapping does not detect syntax or semantic errors (for example, nonexistent
tables or views).
Wrapping detects only tokenization errors (for example, runaway strings).
Syntax or semantic errors are detected during PL/SQL compilation or when
executing the output file in SQL*Plus.
--7
Must wrap only the package body, not the package spec.
--8
DBMS_DDL and WRAP utility
- Code obfuscation: YES YES
- Dynamic obfuscation: YES NO
- Obfuscate multiple programs at a time: NO YES.
Task 20.8. ACCESSIBLE BY clause.
1. What do ACCESSIBLE BY?
2. For which SQL statements can use ACCESSIBLE BY?
3. What part of ACCESSIBLE BY clause is optional?
4. What happen after compiling if entity named in an accessor is not exists?
5. Where ACCESSIBLE BY clause not will be working?
Solution:
--1
The ACCESSIBLE BY clause limits access to a unit or subprogram by other units,
listed at an accessor (white) list.
--2
ALTER TYPE
CREATE TYPE
CREATE TYPE BODY
CREATE FUNCTION
CREATE PROCEDURE
CREATE PACKAGE
--3
The unit_kind specifies a FUNCTION, PACKAGE, PROCEDURE, TRIGGER, TYPE.
--4
Success. Entity named in an accessor (white) list is not required to exist.
--5
- The check will fail if the access is through static SQL, DBMS_SQL, dynamic SQL.
- Any PLSQL unit of a package specification or package body will be checked
against the accessor list of the package specification.
- RPC calls to a protected subprogram will always fail.
- Calls to a protected subprogram from a conditional compilation directive will
fail.
Task 20.9. ACCESSIBLE BY clause. Part 2.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER);
--
CREATE OR REPLACE PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC');
END;
--
CREATE OR REPLACE PROCEDURE PT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PT_procedure');
PRC;
END;
--
CREATE OR REPLACE TRIGGER PT
BEFORE INSERT
ON TAB1
BEGIN
DBMS_OUTPUT.PUT_LINE('PT_trigger');
PRC;
END;
Then do next:
1. Remake procedure PRC that will be accessible by procedure and trigger PT.
2. Remake procedure PRC that will be accessible by procedure PT and trigger
PT only from schema HR. Also demonstrate how much accessors with same name can
write.
3. Make package 'PCK' for procedure PRC and this procedure must be accessible
only by procedure PT. 3 ways.
4. Make package 'PCK' for procedure PRC and procedure PRC2 for demonstrating
what will be if in header of package spec define accessors that did not match
with accessors inside PRC in package spec and package body when we will use
procedure PT where call procedure PCK.PRC?
Solution:
--1
CREATE OR REPLACE PROCEDURE PRC ACCESSIBLE BY (PT) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC');
END;
EXEC PT;
--2
CREATE OR REPLACE PROCEDURE PRC ACCESSIBLE BY (TRIGGER HR.PT,
PROCEDURE HR.PT,
TRIGGER HR.PT,
PROCEDURE HR.PT) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC');
END;
EXEC PT; --in DBMS_OUTPUT: PT_procedure, PRC
INSERT INTO TAB1 VALUES (1);
--3
--1st way
CREATE OR REPLACE PACKAGE PCK ACCESSIBLE BY (PROCEDURE PT) IS
PROCEDURE PRC;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PCK_package PRC');
END;
END;
--2d way
CREATE OR REPLACE PACKAGE PCK IS
--for 3rd way: CREATE OR REPLACE PACKAGE PCK ACCESSIBLE BY (PROCEDURE PT) IS
PROCEDURE PRC ACCESSIBLE BY (PROCEDURE PT);
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC ACCESSIBLE BY (PROCEDURE PT) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PCK_package PRC');
END;
END;
--
CREATE OR REPLACE PROCEDURE PT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PT_procedure');
PCK.PRC;
END;
--4
--Trigger and procedure PT will be compiled with errors and will not work,
--but if we call only PCK.PRC2, then will be success
CREATE OR REPLACE PACKAGE PCK ACCESSIBLE BY (PROCEDURE PT) IS
PROCEDURE PRC ACCESSIBLE BY (TRIGGER PT);
PROCEDURE PRC2;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC ACCESSIBLE BY (TRIGGER PT) IS
BEGIN DBMS_OUTPUT.PUT_LINE('PCK_package PRC'); END;
PROCEDURE PRC2 IS
BEGIN DBMS_OUTPUT.PUT_LINE('PCK_package PRC'); END;
END;
CREATE OR REPLACE TRIGGER PT
BEFORE INSERT
ON TAB1
BEGIN
DBMS_OUTPUT.PUT_LINE('PT_trigger');
PCK.PRC;
END;
CREATE OR REPLACE PROCEDURE PT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PT_procedure');
PCK.PRC;
--PCK.PRC2; --will success, but need delete PCK.PRC from procedure PT
END;
Task 20.10. Code-based access control: granting roles to program units.
1. Demonstrate granting roles to procedure.
Solution:
--1
--Granting roles to procedure allows another schema execute this procedure with
--invoker rights without having privs to all underlying objects: tables, views...
--But owner must have this role too.
--Make this code under HR schema:
DROP TABLE TAB_HR;
CREATE TABLE TAB_HR(COL1 NUMBER);
--
CREATE OR REPLACE PROCEDURE PRC AUTHID CURRENT_USER IS
res NUMBER;
BEGIN
SELECT COUNT(*)
INTO res
FROM HR.TAB_HR; --!!! need write schema
DBMS_OUTPUT.PUT_LINE(res);
END;
--login as SYS
LOGIN SYS AS SYSDBA;
ALTER SESSION SET CONTAINER=ORCLPDB;
CREATE USER HR2 IDENTIFIED BY 123;
GRANT CREATE SESSION TO HR2;
CREATE ROLE ROLE_HR;
GRANT SELECT ON HR.TAB_HR TO ROLE_HR;
GRANT ROLE_HR TO HR;
GRANT ROLE_HR TO PROCEDURE HR.PRC; --need GRANT again, if recompiled HR.PRC
GRANT EXECUTE ON HR.PRC TO HR2;
--then connect to HR2
EXEC HR.PRC;
SELECT * FROM HR.TAB_HR
Task 20.11. Mark code as deprecated.
Do next operations:
DROP PACKAGE PCK;
DROP PROCEDURE PRC;
CREATE OR REPLACE PACKAGE PCK IS
PROCEDURE PRC;
v NUMBER := 5;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC');
END PRC;
END;
CREATE OR REPLACE PROCEDURE PROC IS
BEGIN
PCK.PRC;
DBMS_OUTPUT.PUT_LINE(PCK.V);
END;
Then do next:
1. What do pragma for deprecate, what happen if we use this pragma.
2. For which PLSQL elements we can use it?
3. Where we can use it?
4. Remake:
- make all package`s elements deprecated (2 ways);
- make PROC deprecated;
5. Explain deprecation of an overloaded procedure.
6. When the pragma has no effect? Write example.
Solution:
--1
The DEPRECATE pragma marks a PL/SQL element as deprecated. The compiler issues
warnings when we compile PLSQL unit that references to deprecated PLSQL unit.
--2
Subprograms, packages, variables, constants, types, subtypes, exceptions, cursors
--3
The DEPRECATE pragma may only appear in the declaration section of a PL/SQL item.
It must appear immediately after the declaration of an item to be deprecated.
The DEPRECATE pragma must appears in the declaration sections of a package
specification, an object specification, a top level procedure, or a top level
function immediately after the keyword IS or AS.
--4
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
--only package spec can have PRAGMA DEPRECATE
CREATE OR REPLACE PACKAGE PCK IS
PRAGMA DEPRECATE(pck,'Pck deprecated');
PROCEDURE PRC;
v NUMBER := 5;
END;
EXEC PROC;
--second way
CREATE OR REPLACE PACKAGE PCK IS
PROCEDURE PRC;
PRAGMA DEPRECATE(PRC,'PCK.PRC deprecated');
v NUMBER := 5;
PRAGMA DEPRECATE(v,'PCK.v deprecated');
END;
--
CREATE OR REPLACE PROCEDURE PROC IS
PRAGMA DEPRECATE(PROC, 'PROC deprecated');
BEGIN
PCK.PRC;
DBMS_OUTPUT.PUT_LINE(PCK.V);
END;
--5
Will be deprecated only procedures with parameters that have PRAGMA DEPRECATE
after the declaration
--6
--If an identifier is applied with a mismatched name, for example:
CREATE OR REPLACE PACKAGE PCK IS
PRAGMA DEPRECATE("pck",'Pck NOT deprecated'); --because correct name is "PCK"
PROCEDURE PRC;
v NUMBER := 5;
PRAGMA DEPRECATE(prc,'PRC NOT deprecated'); --because must write after PRC
END;
CHAPTER 21. "Dependencies"
Task 21.1. Dependencies.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
--
CREATE OR REPLACE VIEW VW AS
SELECT COL1, COL2 FROM TAB1;
--
CREATE OR REPLACE PROCEDURE PRC IS
res NUMBER;
BEGIN
SELECT COUNT(*)
INTO res
FROM VW;
DBMS_OUTPUT.PUT_LINE(res);
END;
Then do next:
1. For 'TAB1', 'VW' and 'PRC' indicate:
- direct dependencies;
- undirect dependencies;
2. For 'TAB1' and 'VW' indicate:
- dependent object;
- referenced object.
3. Which status will have 'VW' if we will drop 'TAB1'?
4. Inidicate dependent or referenced for: PACKAGE BODY, PACKAGE SPEC, SEQUENCE,
SUBPROGRAMS, SYNONYM, TABLE, TRIGGER, USER_DEFINED object, USER_DEFINED
collection, VIEW.
5. Explain status of 'VW' if we:
- add new column in TAB1;
- drop column COL1 from TAB1.
6. What mean local dependencies and remote dependencies?
7. Find direct dependencies for 'TAB1'.
8. Explain how display direct and indirect dependencies for 'TAB1'.
Solution:
--1
Direct dependence between view and table.
Undirect dependence between procedure and table.
--2
TAB1 is referenced. VW is dependent.
--3
Invalid.
--4
PACKAGE BODY - dependent only. SEQUENCE - referenced only. Other objects - both.
--5
--If changed objects in referenced object not listed on dependent object, that
--dependent object will be VALID.
Starting 11g, status of view will be VALID if we add new column, because this
column not list in view.
--
If we drop COL1 that listed in VIEW, then VIEW will be invalid.
--6
If all objects in same database it is mean local dependencies.
If referenced object in remote database then it is remote dependencies.
--7
SELECT *
FROM USER_DEPENDENCIES
WHERE REFERENCED_NAME = 'TAB1'
--8
--Run 'utldtree.sql' from $ORACLE_HOME/rdbms/admin
--copy to disk D and run in SQL DEVELOPER. Result of created objects can find in
--SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME LIKE '%TREE%'
@d:\utldtree.sql
--!!! then execute procedure without ;
EXECUTE DEPTREE_FILL(
'TABLE', --object_type
'HR', --object_owner
'TAB1' --object_name
)
--further we can write query
SELECT NESTED_LEVEL,
TYPE,
NAME
FROM DEPTREE
ORDER BY SEQ#;
Task 21.2. Dependencies. Part 2.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
--
CREATE OR REPLACE VIEW VW AS
SELECT COL1, COL2 FROM TAB1;
--
CREATE OR REPLACE VIEW VW2 AS
SELECT COL1, COL2 FROM VW;
--
CREATE OR REPLACE PROCEDURE PRC IS
res NUMBER;
BEGIN
SELECT COUNT(*)
INTO res
FROM VW2;
DBMS_OUTPUT.PUT_LINE(res);
END;
Then do next:
1. List and explain object`s status for USER_OBJECTS, DBA_OBJECTS, ALL_OBJECTS.
Which statuses can have TABLE?
2. Explain which status can be for 'PRC' if 'VW' will be INVALID. How to
recompile objects 'PRC', explain.
3. Write status for VIEW, FUNCTION and PROCEDURE if we add column in referenced
table and if we alter column in referenced table.
Solution:
--1
--For USER_OBJECTS available only VALID, INVALID.
--Table can be only VALID.
VALID - the user object succesfully compiled.
COMPILED WITH ERRORS - compiling produced errors.
INVALID - for objects changed references (only dependent object can be invalid).
UNAUTHORIZED - access priv on a referenced object was revoked (only dependent
object can be unauthorized).
--2
'PRC' will be INVALID too. All direct dependent and indirect dependent objects
will be INVALID.
EXECUTE PRC; --this mean that server try compile procedure and recompile
--everything inside this code. May be will success, may be not.
--3
--may be VALID if recompiling when calling
ADD COLUMN ALTER COLUMN
VIEW valid may valid or not
FUNCTION invalid may valid or not
PROCEDURE may valid or not may valid or not
Task 21.3. Packages and dependencies.
Do next operations:
CREATE OR REPLACE PACKAGE PCK IS
PROCEDURE PRC;
END;
CREATE OR REPLACE PACKAGE BODY PCK IS
PROCEDURE PRC IS
BEGIN
DBMS_OUTPUT.PUT_LINE('PRC');
END;
END;
CREATE OR REPLACE PROCEDURE PROC IS
BEGIN
PCK.PRC;
END;
Then do next:
1. Write status for 'PROC' if we add new procedure to 'PCK'.
Solution:
--1
VALID
Task 21.4. Synonyms and dependencies.
Do next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1(COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
CREATE TABLE TAB2(COL1 NUMBER);
INSERT INTO TAB2(COL1) VALUES(2);
CREATE OR REPLACE SYNONYM SYN FOR TAB1;
CREATE OR REPLACE VIEW VW AS SELECT * FROM SYN;
Then do next:
1. Explain status for VW if we recreate SYN for another table TAB2.
2. List circumstances when recreating synonym not make INVALID dependent non-VIEW
objects and VIEW.
3. Can we create synonyms for remote schema objects?
Solution:
--1
INVALID
--2
--Non-VIEW
- The column order, column names, and column data types of the tables need to be
identical;
- The privileges on the newly referenced table and its columns are a superset of
the set of privileges on the original table. These privileges must not be
derived through roles alone;
- The names and types of partitions and sub-partitions need to be identical;
- The tables are of the same organization type.
- Object type columns are of the exact same type.
--VIEW
--all the above and next:
- Columns and order of columns defined for primary key and unique indexes, NOT
NULL constraints, primary key and unique constraints must be identical.
- The dependent view cannot have any referential constraints.
--3
Yes, but cannot create synonyms for objects declared in PL/SQL subprograms or
packages.
Task 21.5. Recommendations to reducing invalidation.
1. List recommendations to reducing invalidation.
2. When validations occurs automatically?
3. Suppose we created package 'PCK' with a procedure that works 10 minutes.
Then in the first session we run this procedure. After 5 seconds in second session
we change this package and try to compile. What will happen in first session and
in second?
Solution:
--1
- Add new items to the end of the package (or in body write subprogram headers,
if subprograms used only in body);
- Reference each table through a view
--2
When object is referenced, it is mean when you try to call object.
--3
In first session procedure will be working 10 minutes. Second session will wait
end of procedure works and after that compile package.
Task 21.6. Remote dependencies. Create remote database and link.
1. For 12c version create new plugable database with: name = 'NEW_DB', user
name = 'MY_USER', password for user = 'MY_PASS'.
2. Display OPEN_MODE in SQLplus for NEW_DB.
3. After point 1 create user U1 with password 123 and grant to him necessary
privs.
4. After point 2 which file must correct for make connection in SQL_DEVELOPER.
5. Connect to NEW_DB via SQL_PLUS.
6. Create database link 'MY_LINK' for user from new database NEW_DB and make
SELECT * FROM DUAL.
7. How to find all links?
Solution:
--1
-- It is for 12c.
-- Use Database Configuration Assistant (DBCA).
-- Further choose MANAGE PLUGGABLE DATABASES.
-- Further choose CREATE A PLUGGABLE DATABASE. Select container database.
-- Further choose CREATE A NEW PLUGGABLE DATABASE FROM PDB SEED.
-- Write database name = NEW_DB, username = MY_USER, password for user = MY_PASS.
-- Location of our new database: C:\APP\MYU\ORADATA\ORCL\{PDB_NAME}. Push NEXT.
--2
--open SQLPLUS
--connect to SYS AS SYSDBA
SELECT NAME, OPEN_MODE FROM V$PDBS;
--3
ALTER SESSION SET CONTAINER=NEW_DB;
CREATE USER U1
IDENTIFIED BY "123";
GRANT CREATE SESSION TO U1;
GRANT CONNECT TO U1;
GRANT CREATE TABLE TO U1;
GRANT CREATE PROCEDURE TO U1;
GRANT CREATE VIEW TO U1;
GRANT UNLIMITED TABLESPACE TO U1;
--4
--Correct TNSNAMES.ORA. Just copy any pluggable database and rename name and
--SERVICE_NAME to NEW_DB.
--5
--In SQLplus
CONN
U1/123@NEW_DB;
--6
--Make operations as SYS and also need switch to old pluggable database
--(for example ORCLPDB)
SYS AS SYSDBA;
ALTER SESSION SET CONTAINER=ORCLPDB;
--DROP PUBLIC DATABASE LINK MY_LINK;
CREATE PUBLIC DATABASE LINK MY_LINK
CONNECT TO U1
IDENTIFIED BY "123"
USING 'NEW_DB';
--
SELECT * FROM DUAL@MY_LINK;
--7
SELECT * FROM ALL_DB_LINKS
Task 21.7. Remote dependencies.
Do next operations:
--IN FIRST SCHEMA
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
COMMIT;
--
CREATE OR REPLACE VIEW VW AS
SELECT COL1, COL2 FROM TAB1;
--
CREATE OR REPLACE PROCEDURE PRC IS
res NUMBER;
BEGIN
SELECT COUNT(*)
INTO res
FROM VW;
DBMS_OUTPUT.PUT_LINE(res);
END;
--IN SECOND SCHEMA
CREATE OR REPLACE PROCEDURE PRC2 IS
BEGIN
PRC@MY_LINK;
DBMS_OUTPUT.PUT_LINE('remote PRC success');
END;
Then do next:
1. If we modify column`s type for TAB1 what status will have objects: VW, PRC,
PRC2. What happening if we try execute PRC2?
2. How to check dependencies mode in dictionary?
3. Explain two dependencies modes for remote objects, indicate default. How to
switch to non-default mode?
Solution:
--1
VW - INVALID. PRC - INVALID. PRC2 - VALID.
First execute will validate VW and PRC and returns error for PRC2.
Second execute will validate PRC2 and will be success.
--2
Oracle try to check status of remote object in remote USER_OBJECTS.
SELECT *
FROM v$parameter
WHERE NAME='remote_dependencies_mode'
--3
--default
Oracle check timestamp of compilation of remote OBJECT and timestamp of
compilation of local procedure. If compilation time of local procedure older then
remote OBJECT - it is will be valid, otherwise - NOT VALID.
--
Comparing signatures between dependent and referenced objects. The signature of
procedure is:
- the name of the procedure;
- the quantity of parameters;
- the data types of the parameters;
- the modes of the parameters;
- the datatype of the return value for a function.
The signature of the remote procedure is saved in the local procedure.
--Switch to signature.
--Then need recreate procedure PRC2
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE='SIGNATURE';
--Then if we just recompile PRC (without changes) after compile PRC2, then
--executing PRC2 not raise error. But if we add for example new parameter to PRC
--and recompile again, then executing PRC2 will raise error and status PRC2 will
--be INVALID.
Task 21.8. Notices about recompilation.
1. When recompiling dependent procedures and functions is unsuccessful?
2. When recompiling dependent procedures and functions is successful?
3. How to minimize dependency failures for procedures?
Solution:
--1
- The referenced objects is dropped or renamed;
- The data type of the referenced column is changed;
- The referenced column is dropped;
- A referenced view is replaced by a view with different columns;
- The parameter list of a referenced procedure is modified.
--2
- The referenced table has new columns;
- The data type of referenced columns has not changed;
- A private table is dropped, but a public table that has the same name and
structure exists;
- The PL/SQL body of a referenced procedure has been modified and recompiled
successfully.
--3
- Declaring records with the %ROWTYPE attribute;
- Declaring variables with the %TYPE attribute;
- Querying with the SELECT * notation (use carefully, if performance is good);
- Including a columns list with INSERT statements.
CHAPTER 22. "Another features"
Task 22.1. Using DBMS_ERRLOG
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER(1) DEFAULT 0,
COL2 VARCHAR2(3) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
COMMIT;
Then do next:
1. Insert into TAB1 values and use DBMS_ERRLOG:
2 'two'
11 'eleven'
12 'twelve'
Solution:
--1
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('TAB1');
DESC err$_TAB1
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE err$_TAB1'; --clear table
INSERT INTO TAB1(COL1, COL2)
SELECT 2, 'two' FROM DUAL
UNION ALL
SELECT 11, 'eleven' FROM DUAL
UNION ALL
SELECT 12, 'twelve' FROM DUAL
LOG ERRORS REJECT LIMIT UNLIMITED;
--COMMIT;
END;
SELECT * FROM err$_TAB1
Task 22.2. Another features. Part 1.
1. Create recursive function.
2. Create function that split a string 'a;b;c;d' into elements and return varray.
Function must have two arguments: 'p_str' for string, 'p_d' for delimiter. Return
type is max 32767 elements.
Solution:
--1
CREATE OR REPLACE FUNCTION FNC_RECURS(pin NATURALN) RETURN NUMBER IS
BEGIN
IF pin = 0 THEN
RETURN 1;
ELSE
RETURN pin * FNC_RECURS(pin - 1);
END IF;
END;
--2
CREATE OR REPLACE FUNCTION FNC_SPLIT(p_str VARCHAR2 DEFAULT NULL,
p_d VARCHAR2 DEFAULT NULL)
RETURN SYS.ODCIVARCHAR2LIST IS
v_str VARCHAR2(32767) := p_str || p_d;
v_arr SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
WHILE INSTR(v_str, p_d) > 1 LOOP
v_arr.EXTEND;
v_arr(v_arr.LAST) := SUBSTR(v_str, 1, INSTR(v_str, p_d) - 1);
v_str := SUBSTR(v_str, INSTR(v_str, p_d) + 1);
END LOOP;
RETURN v_arr;
END;
Task 22.3. Another features. 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. What compilation parameter allows PRAGMA INLINE to be used?
2. Demonstrate PRAGMA INLINE and explain benefit.
3. What is about PRAGMA INLINE for overloaded subprogram?
Solution:
--1
PLSQL_OPTIMIZE_LEVEL must be 2 (default) or 3.
--2
--PRAGMA INLINE replace call of subprogram by code of subprogram and it may
--improve performance. If subprogram inlining slows the performance, then use the
--PL/SQL hierarchical profiler to identify subprograms and disable inlining by:
--PRAGMA INLINE (subprogram, 'NO'). Also 'NO' overrides 'YES' for same units.
CREATE OR REPLACE FUNCTION FNC(pin NUMBER) RETURN NUMBER IS
BEGIN
RETURN pin * 100;
END FNC;
--
CREATE OR REPLACE PROCEDURE PRC IS
PRAGMA INLINE(FNC, 'YES');
res NUMBER;
BEGIN
FOR i in 1..100000000 LOOP
res := FNC(i);
END LOOP;
END PRC;
--
BEGIN
PRC;
END;
--3
If subprogram is overloaded, then the preceding pragma applies to every
subprogram with that name.