Oracle – Sum Of Product Of Number

Sum of products of digits of given number can be derived by using following PLSQL block.

SET SERVEROUTPUT ON 

DECLARE
 
    p_number        INTEGER := 9753124680;
    v_number        INTEGER;
    v_digit_1       INTEGER; 
    v_digit_2       INTEGER; 
    v_product       INTEGER;
    v_product_sum   INTEGER;
     
BEGIN
     
    v_number := TO_CHAR(p_number);
    v_product_sum := 0;

    WHILE v_number <> 0 LOOP
        EXIT WHEN LENGTH(v_number) = 1;
        v_digit_1 := MOD(v_number, 10);
        v_digit_2 := SUBSTR(MOD(v_number, 100), 1, 1);
        v_product := v_digit_1 * v_digit_2;
        v_product_sum := v_product_sum + v_product;
        v_number := TRUNC(v_number / 10);
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('Sum Of Products Of Consecutive Digits = ' || v_product_sum); 
END;
/

Oracle – Sequence

In Oracle, you can create an auto-number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax

Create Sequence

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE | NOCYCLE
CACHE value | NOCACHE
ORDER value | NOORDER;
  • sequence_name: Name of the sequence.
  • initial_value: starting value from where the sequence starts. Initial_value should be greater than or equal to minimum value and less than equal to maximum value.
  • increment_value: Value by which sequence will increment itself. Increment_value can be positive or negative.
  • minimum_value: Minimum value of the sequence.
  • maximum_value: Maximum value of the sequence.
  • cycle: When sequence reaches its set_limit it starts from beginning.
  • nocycle: An exception will be thrown if sequence exceeds its max_value.
  • cache: This clause directs Oracle server to cache ā€™nā€™ values of a sequence. Its value is 20 by default.
  • nocache: It is the default behavior of the sequence.
  • order: It ensures that sequence values are generated in order. Generally, it is least usable clause in sequence definition but they are helpful in applications which use sequence as timestamp value.
  • noorder: It is the default behavior of the sequence.

Alter Sequence

ALTER SEQUENCE sequence_name -- You Can Alter any (one) of the following.. Or all...
INCREMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE n | NOCACHE
ORDER n | NOORDER;

Drop Sequence

DROP SEQUENCE sequence_name;

Sequence Usage

-- Next Value (Sequence incremented to next value)
-- ===============================================
-- sequence_name.NEXTVAL
SELECT sequence_name.NEXTVAL FROM dual;

-- Current Value (Showing current incremented value of sequence)
-- =============================================================
-- sequence_name.CURRVAL
SELECT sequence_name.CURRVAL FROM dual;

Sequence Table

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

Examples

Prerequisite

DROP TABLE emps;
CREATE TABLE emps AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

Example 1

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
NOCYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spider man' , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Overwatch'  , 10000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);
-- ORA-08004: sequence EMPS_ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Altering Max Value
-- ===================
ALTER SEQUENCE emps_id_seq
MAXVALUE 10;

-- Inserting Again
-- ================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;

Example 2

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 21 -- This Value should be greater than 20 to use cycle.
CYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spidarman'  , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain America', 10001);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain Marvel' , 10002);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Wonder Women'   , 10003);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Flash'          , 10004);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Batman'         , 10005);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Deadpool'       , 10006);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Superman'       , 10007);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ant Man'        , 10008);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Daredevil'      , 10009);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Electra'        , 10010);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Watchmen'       , 10011);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Catwomen'       , 10012);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Vibe'           , 10013);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'killer Frost'   , 5000); -- Starting Again From 1
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt'     , 5000);

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;
DROP TABLE emps;

Example 3 – Primary Key Auto Increment (Up to 11g)

-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Without Primary Key
-- ==============================
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000001');
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000002');

-- Checking 
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

Example 4 – Unique, Sequential Code

  • Making “inv_name” auto-generated.
    • Eg: “INV/01042019/000001”
  • Making primary key(id) also auto-generated.
-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Function
-- ==================
CREATE OR REPLACE FUNCTION next_inv_name_fn 
  RETURN VARCHAR2
  AS
  BEGIN
  RETURN('INV/' || TO_CHAR(SYSDATE, 'DDMMYYYY') || '/' || LPAD(invoices_id_seq.NEXTVAL, 6, 0));
  END;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.CURRVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Values
-- =================
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);

-- Checking
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

References

Oracle – Dynamic SQL

  • SQL statements that are only know until run time are called dynamic SQL statements.
  • The literal NULL is not allowed in the USING clause.
  • The documentation said that you cannot set a bind variable with a Boolean literals (TRUE, FALSE).

Example 1

CREATE OR REPLACE FUNCTION get_emp_col_fn(p_table  VARCHAR2,
                                          p_column VARCHAR2,
                                          p_emp_id INTEGER) RETURN VARCHAR2 IS
  v_out      VARCHAR2(100);
  v_sql_stmt VARCHAR2(500);
BEGIN
  IF UPPER(p_table) = 'EMPS' THEN
    v_sql_stmt := 'SELECT ' || p_column || ' FROM ' || p_table ||
                  ' WHERE employee_id=:emp_id';
    EXECUTE IMMEDIATE v_sql_stmt
      INTO v_out
      USING p_emp_id;
    RETURN v_out;
  END IF;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE('Invalid table: ' || p_table);
END;
/

-- Execution
-- ==========
SELECT get_emp_col_fn('emps', 'first_name', 100) AS get_emp_col_fn FROM dual;
SELECT get_emp_col_fn('emps', 'last_name', 100) AS get_emp_col_fn FROM dual;
SELECT get_emp_col_fn('emps', 'salary', 100) AS get_emp_col_fn FROM dual;
SELECT get_emp_col_fn('emps', 'hire_date', 100) AS get_emp_col_fn FROM dual;

Example 2

CREATE OR REPLACE PROCEDURE delete_dynamic_sql_sp(p_table_name IN VARCHAR2,
                                                  p_condition  IN VARCHAR2 DEFAULT NULL) AS
  v_where_clause VARCHAR2(100) := ' WHERE ' || p_condition;
  v_table        VARCHAR2(30);
BEGIN
  -- Checking table exists or not; If not, raise an exception...
  SELECT OBJECT_NAME
    INTO v_table
    FROM USER_OBJECTS
   WHERE OBJECT_NAME = UPPER(p_table_name)
     AND OBJECT_TYPE = 'TABLE';
  IF p_condition IS NULL THEN
    v_where_clause := NULL;
  END IF;
  EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || v_where_clause;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Table: ' || UPPER(p_table_name));
END;
/

-- Execution
-- ===========
BEGIN
delete_dynamic_sql_sp('emps', 'employee_id = 120');
END;
/

Reference

PL/SQL – Execute Immediate

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.

To process a dynamic sql statement, you can use the following Native dynamic SQL statement:

  • The PL/SQL – Execute Immediate statement with the BULK COLLECT INTO clause.
  • The OPEN-FOR, FETCH, and CLOSE statements.

Note:
The returning clause specifies the values return from DELETEEXECUTE IMMEDIATEINSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections

Example

-- Prerequisite 
-- =============
DROP TABLE exec_imdate_tb;
DROP TABLE emps;
CREATE TABLE emps AS(SELECT employee_id, first_name, last_name, hire_date, salary FROM employees WHERE 1=1 AND employee_id BETWEEN 100 AND 120);

SELECT * FROM exec_imdate_tb;
SELECT * FROM emps;

Stored Procedure

CREATE OR REPLACE PROCEDURE exec_imdate_sp AS

  TYPE emp_dml_nt IS TABLE OF emps.employee_id%TYPE;
  emp_id_nt emp_dml_nt;

  TYPE employee_ntt IS TABLE OF emps%ROWTYPE;
  nt_employees employee_ntt;

  emps_c        SYS_REFCURSOR;
  v_sql_stmt    VARCHAR2(500);
  v_plsql_block VARCHAR2(500);
  v_emp_fname   emps.first_name%TYPE;
  v_emp_sal     emps.salary%TYPE;

BEGIN
  -- DDL - Create Table
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DDL - Create Table...');
  EXECUTE IMMEDIATE 'CREATE TABLE exec_imdate_tb(id         INTEGER PRIMARY KEY, 
                         name         VARCHAR2(25),
                         credit_debit_limit NUMBER)';

  -- DDL - Alter Table
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DDL - Alter Table...');
  EXECUTE IMMEDIATE 'ALTER TABLE exec_imdate_tb MODIFY name NOT NULL';

  -- DML - Insert
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DML - Insert...');
  EXECUTE IMMEDIATE 'INSERT INTO  exec_imdate_tb(id, name, credit_debit_limit) values (:id, 
                                   :name, 
                                   :limit)'
    USING 1, 'Sathiyan', 2000000;
  EXECUTE IMMEDIATE 'INSERT INTO  exec_imdate_tb(id, name, credit_debit_limit) values (:id, 
                                   :name, 
                                   :limit)'
    USING 2, 'Doctor Homes', 203000;
  COMMIT;

  -- DML - Select 
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DML - Select...');
  -- Selet Column
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DML - Select Column...');
  v_sql_stmt := 'SELECT first_name, salary FROM emps WHERE employee_id = :id';
  EXECUTE IMMEDIATE v_sql_stmt
    INTO v_emp_fname, v_emp_sal
    USING 105;
  DBMS_OUTPUT.PUT_LINE('v_emp_fname: ' || v_emp_fname);
  DBMS_OUTPUT.PUT_LINE('v_emp_sal: ' || v_emp_sal);

  -- Select Full Row
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DML - Select With Condition...');
  nt_employees := employee_ntt();
  v_sql_stmt   := 'SELECT * FROM emps WHERE employee_id BETWEEN :id_l_limit AND :id_u_limit';

  OPEN emps_c FOR v_sql_stmt
    USING 100, 105;
  FETCH emps_c BULK COLLECT
    INTO nt_employees;

  FOR i IN 1 .. nt_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || nt_employees(i).employee_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || nt_employees(i).first_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || nt_employees(i).salary);
    DBMS_OUTPUT.PUT_LINE('=======================');
  END LOOP;
  CLOSE emps_c;

  -- DML Select All
  DBMS_OUTPUT.PUT_LINE('=======================');
  nt_employees := employee_ntt();
  DBMS_OUTPUT.PUT_LINE('DML - Select All...');
  v_sql_stmt := 'SELECT * FROM emps';
  EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT
    INTO nt_employees;

  FOR i IN 1 .. nt_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || nt_employees(i).employee_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || nt_employees(i).first_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || nt_employees(i).salary);
    DBMS_OUTPUT.PUT_LINE('=======================');
  END LOOP;

  -- DML Update
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DML - Update...');
  v_sql_stmt := 'UPDATE emps SET salary = salary+:salary_inrement WHERE salary > :salary_limit RETURNING employee_id INTO :emp_id_nt';
  EXECUTE IMMEDIATE v_sql_stmt
    USING 1000, 10000
    RETURNING BULK COLLECT
    INTO emp_id_nt;

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Updated Employee ID''s');
  FOR i IN emp_id_nt.FIRST .. emp_id_nt.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(emp_id_nt(i));
  END LOOP;

  -- DML Delete
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('DML - Delete...');
  emp_id_nt  := emp_dml_nt();
  v_sql_stmt := 'DELETE FROM emps WHERE employee_id IN (:a, :b) RETURNING employee_id INTO :emp_id_nt';
  EXECUTE IMMEDIATE v_sql_stmt
    USING 119, 120
    RETURNING BULK COLLECT
    INTO emp_id_nt;

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Deleted Employee ID''s');
  FOR i IN emp_id_nt.FIRST .. emp_id_nt.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(emp_id_nt(i));
  END LOOP;

  -- PLSQL Call
  DBMS_OUTPUT.PUT_LINE('=======================');
  DBMS_OUTPUT.PUT_LINE('PLSQL Call...');
  v_plsql_block := 'BEGIN UPDATE emps SET salary=salary+500 WHERE employee_id = :emp_id; END;';
  EXECUTE IMMEDIATE v_plsql_block
    USING 100;

END;
/

-- Execution
-- ==========
BEGIN
exec_imdate_sp;
END;
/

Output

=======================
DDL - Create Table...
=======================
DDL - Alter Table...
=======================
DML - Insert...
=======================
DML - Select...
=======================
DML - Select Column...
v_emp_fname: David
v_emp_sal: 4800
=======================
DML - Select With Condition...
ID: 100
Name: Steven
Salary: 24000
=======================
ID: 101
Name: Neena
Salary: 17000
=======================
ID: 102
Name: Lex
Salary: 17000
=======================
ID: 103
Name: Alexander
Salary: 9000
=======================
ID: 104
Name: Bruce
Salary: 6000
=======================
ID: 105
Name: David
Salary: 4800
=======================
=======================
DML - Select All...
ID: 100
Name: Steven
Salary: 24000
=======================
ID: 101
Name: Neena
Salary: 17000
=======================
ID: 102
Name: Lex
Salary: 17000
=======================
ID: 103
Name: Alexander
Salary: 9000
=======================
ID: 104
Name: Bruce
Salary: 6000
=======================
ID: 105
Name: David
Salary: 4800
=======================
ID: 106
Name: Valli
Salary: 4800
=======================
ID: 107
Name: Diana
Salary: 4200
=======================
ID: 108
Name: Nancy
Salary: 12000
=======================
ID: 109
Name: Daniel
Salary: 9000
=======================
ID: 110
Name: John
Salary: 8200
=======================
ID: 111
Name: Ismael
Salary: 7700
=======================
ID: 112
Name: Jose Manuel
Salary: 7800
=======================
ID: 113
Name: Luis
Salary: 6900
=======================
ID: 114
Name: Den
Salary: 11000
=======================
ID: 115
Name: Alexander
Salary: 3100
=======================
ID: 116
Name: Shelli
Salary: 2900
=======================
ID: 117
Name: Sigal
Salary: 2800
=======================
ID: 118
Name: Guy
Salary: 2600
=======================
ID: 119
Name: Karen
Salary: 2500
=======================
ID: 120
Name: Matthew
Salary: 8000
=======================
=======================
DML - Update...
Updated Employee ID's
100
101
102
108
114
=======================
DML - Delete...
Deleted Employee ID's
119
120
=======================
PLSQL Call...

Reference

PL/SQL – Populating Data Using Collections

Varray

Case Environment Creation

-- Creating Source Table
-- ======================
CREATE TABLE teledir_source_tb(
id NUMBER,
f_name VARCHAR2(25),
mobile NUMBER(10),
addr1 VARCHAR2(25),
addr2 VARCHAR2(25)
);

-- Inserting Data In to Source Table
-- ====================================
INSERT INTO teledir_source_tb VALUES (1, 'Sathiyan', 9998887776, 'Tambaram', 'Chennai');
INSERT INTO teledir_source_tb VALUES (2, 'Arunn', 9998887777, 'Police Colony', 'Trichy');
INSERT INTO teledir_source_tb VALUES (3, 'Sarathi', 9998887778, 'Kurinjipaadi', 'Vadalur');

-- Creating Object Type (Using Source Table Structure)
-- ====================================================
CREATE OR REPLACE TYPE teledir_ob AS OBJECT (
id NUMBER,
f_name VARCHAR2(25),
mobile NUMBER(10),
addr1 VARCHAR2(25),
addr2 VARCHAR2(25)
);

-- Creating Varray Type (Using Created Object)
-- ============================================
CREATE OR REPLACE TYPE teledir_va AS VARRAY(50) OF teledir_ob;

-- Creating Target Table(Using Created Varray)
-- =================================================
CREATE TABLE teledir_tb (
id  NUMBER PRIMARY KEY,
teledir teledir_va);

Stored Procedure For Populating Target Table

-- Stored Procedure
-- ==================
CREATE OR REPLACE PROCEDURE teledir_sp
AS

  va_teledir teledir_va := teledir_va();
	CURSOR teledir_c IS
		SELECT id, f_name, mobile, addr1, addr2 FROM teledir_source_tb;

BEGIN

    FOR i IN teledir_c LOOP
	  va_teledir.EXTEND();
	  va_teledir(va_teledir.LAST) := teledir_ob(va_teledir.LAST, i.f_name, i.mobile, i.addr1, i.addr2);
    END LOOP;
	INSERT INTO teledir_tb VALUES(1, va_teledir);
	COMMIT;
	
END;
/

-- Executing Stored Procedure
-- ===========================
BEGIN
teledir_sp;
END;

-- Whole Table Select
SELECT * FROM teledir_tb;

-- Selecting Varray Data type Column
SELECT tb.id, tb.f_name, tb.addr1, tb.addr2
  FROM TABLE ( 
    SELECT teledir                  
      FROM teledir_tb                  
     WHERE id = 1 ) tb;

Nested Table

Case Environment Creation

-- Source Table Creation
-- ======================
CREATE TABLE res_partner_address(
id 				INTEGER PRIMARY KEY,
address_type 	VARCHAR2(15),
street 			VARCHAR2(25),
city 			VARCHAR2(15),
state 			VARCHAR2(15),
zip 			NUMBER(6)
);

-- Source Table Data Insert
-- =========================
INSERT INTO res_partner_address VALUES(1, 'Invoice', '1st Street', 'Chennai', 'Tamilnadu', 600001);
INSERT INTO res_partner_address VALUES(2, 'Delivery L1', '221B Baker Street', 'Chennai', 'Tamilnadu', 600003);
INSERT INTO res_partner_address VALUES(3, 'Delivery L2', '177A Bleecker Street', 'Chennai', 'Tamilnadu', 600003);

-- Object Type Creation
-- ======================
CREATE OR REPLACE TYPE address_ob AS OBJECT (
address_type 	VARCHAR2(15),
street 			VARCHAR2(25),
city 			VARCHAR2(15),
state 			VARCHAR2(15),
zip 			NUMBER(6)
);

-- Nested Table Type Creation
-- ===========================
CREATE OR REPLACE TYPE address_nt AS TABLE OF address_ob;

-- Destination/Target Table Creation
-- ==================================
CREATE TABLE res_partner(
id 				INTEGER PRIMARY KEY,
customer_name 	VARCHAR2(25),
addresses 		address_nt)
NESTED TABLE
  addresses
STORE AS
  nested_addresses;

Stored Procedure For Populating Target Table

-- Stored Procedure
-- =================
CREATE OR REPLACE PROCEDURE partner_address_sp
AS

  v_customer_name VARCHAR2(25) := 'Sherlock Strange';
  nt_address address_nt := address_nt();
	CURSOR address_c IS
		SELECT id, address_type, street, city, state, zip FROM res_partner_address ORDER BY id;

BEGIN

    FOR i IN address_c LOOP
	  nt_address.EXTEND();
	  nt_address(nt_address.LAST) := address_ob(i.address_type, i.street, i.city, i.state, i.zip);
    END LOOP;
	INSERT INTO res_partner VALUES(1, v_customer_name, nt_address);
	COMMIT;
	
END;
/

-- Executing Stored Procedure
-- ==========================
BEGIN
partner_address_sp;
END;

-- Update Nested Table Value
-- ===========================
UPDATE TABLE (SELECT addresses
                FROM res_partner
               WHERE id = 1)
           SET city = 'London'
         WHERE address_type = 'Delivery L1';

-- Select Value From Nested Table
-- ===============================
SELECT tb.address_type, tb.street, tb.city, tb.state, tb.zip
  FROM TABLE ( 
    SELECT addresses                  
      FROM res_partner                  
     WHERE id = 1 ) tb;

Associative Array or Index By Table

It can’t be done for Associative Array as above, Since Index By Table can be used in-side PLSQL block only. We can’t create a table with a column as Index By datatype.

Reference

PL/SQL – Collection Methods

Method Name & Purpose
EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT Returns the number of elements that a collection currently contains.
LIMIT Checks the maximum size of a collection.
FIRST Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
LAST Returns the last (largest) index numbers in a collection that uses the integer subscripts.
PRIOR(n) Returns the index number that precedes index n in a collection.
NEXT(n) Returns the index number that succeeds index n.
EXTEND Appends one null element to a collection.
EXTEND(n) Appends n null elements to a collection.
EXTEND(n,i) Appends n copies of the ith element to a collection.
TRIM Removes one element from the end of a collection.
TRIM(n) Removes n elements from the end of a collection.
DELETE Removes all elements from a collection, setting COUNT to 0.
DELETE(n) Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Methods Associative Array Or Index By Table

Example

DECLARE

  TYPE names_at IS TABLE OF NUMBER INDEX BY VARCHAR2(100); 
  
  v_names names_at; 
  v_name  VARCHAR2(100); 

BEGIN 

  -- Adding elements to the table 
  v_names('Scott') := 90; 
  v_names('Smith') := 92; 
  v_names('Jones') := 95;
  v_names('Arunn') := 93;
  v_names('AArun')  := 89;
  
  DBMS_OUTPUT.PUT_LINE('First Element: '|| v_names(v_names.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last Element: '  || v_names(v_names.LAST));
  DBMS_OUTPUT.PUT_LINE('Count : '    || v_names.COUNT ||', '|| 'Always Return Number of Elements');
  DBMS_OUTPUT.PUT_LINE('First Index: '  || v_names.FIRST ||', '|| 'Always Return Smallest Index Value');
  DBMS_OUTPUT.PUT_LINE('Last Index : '  || v_names.LAST  ||', '|| 'Always Return Largest Index Value');
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Prior to First Element: '|| v_names.PRIOR('AArun'));
  DBMS_OUTPUT.PUT_LINE('Prior to Any Element(Except First): '|| v_names.PRIOR('Jones'));
  DBMS_OUTPUT.PUT_LINE('Next to Last Element: '|| v_names.NEXT('Smith'));
  DBMS_OUTPUT.PUT_LINE('Next to Any Element(Except Last): '|| v_names.NEXT('Jones'));
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting Single Element: AArun, First Index');
  v_names.DELETE('AArun');
  IF v_names.EXISTS('AArun') THEN
	DBMS_OUTPUT.PUT_LINE(v_names('AArun'));
  ELSE
	DBMS_OUTPUT.PUT_LINE('Element Deleted');
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Refer Above Values of Count, First, Last And Compare It With Below....');
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_names.COUNT);
  DBMS_OUTPUT.PUT_LINE('First Index After Delete: '||v_names.FIRST);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete(Index 1 Deleted): '||v_names.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting From Arunn to Scott');
  v_names.DELETE('Arunn', 'Scott');
  DBMS_OUTPUT.PUT_LINE('First Element After Delete: '||v_names.FIRST);
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_names.COUNT);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('You cannot use EXTEND, LIMIT and TRIM in Associative Array or Index By Table');
	
END;

Output

First Element: 89
Last Element: 92
Count : 5, Always Return Number of Elements
First Index: AArun, Always Return Smallest Index Value
Last Index : Smith, Always Return Largest Index Value
======================================
Prior to First Element: 
Prior to Any Element(Except First): Arunn
Next to Last Element: 
Next to Any Element(Except Last): Scott
======================================
Deleting Single Element: AArun, First Index
Element Deleted
======================================
Refer Above Values of Count, First, Last And Compare It With Below....
Count After Delete: 4
First Index After Delete: Arunn
Last Index After Delete(Index 1 Deleted): Smith
======================================
Deleting From Arunn to Scott
First Element After Delete: Smith
Count After Delete: 1
======================================
You cannot use EXTEND, LIMIT and TRIM in Associative Array or Index By Table

Collection Methods in Nested Table

Example

DECLARE 

  TYPE values_nt IS TABLE OF VARCHAR2(20);
  v_values values_nt := values_nt();

BEGIN 
  v_values := values_nt(108,97,98,99,100,101);
  
  DBMS_OUTPUT.PUT_LINE('First Element: '|| v_values(v_values.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last Element: '  || v_values(v_values.LAST));
  DBMS_OUTPUT.PUT_LINE('Count : '    || v_values.COUNT ||', '|| 'Always Return Number of Elements');
  DBMS_OUTPUT.PUT_LINE('First Index: '  || v_values.FIRST ||', '|| 'Always Return Smallest Index Value');
  DBMS_OUTPUT.PUT_LINE('Last Index : '  || v_values.LAST  ||', '|| 'Always Return Largest Index Value');

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Prior to First Index: '|| v_values.PRIOR(1));
  DBMS_OUTPUT.PUT_LINE('Prior to Any Index(Except First): '|| v_values.PRIOR(5));
  DBMS_OUTPUT.PUT_LINE('Next to Last Index: '|| v_values.NEXT(6));
  DBMS_OUTPUT.PUT_LINE('Next to Any Index(Except Last): '|| v_values.NEXT(1));

  DBMS_OUTPUT.PUT_LINE('Deleting Single Element: 108, First Index');
  v_values.DELETE(1);
  --  DELETE and EXISTS method does not raises SUBSCRIPT_OUTSIDE_LIMIT error
  IF v_values.EXISTS(1) THEN
    DBMS_OUTPUT.PUT_LINE(v_values(1));
  ELSIF v_values.EXISTS(7) THEN
    DBMS_OUTPUT.PUT_LINE('7th Element Exist');
  ELSE
    DBMS_OUTPUT.PUT_LINE('1st Index Element Deleted, 7th Element Not Exist');
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Refer Above Values of Count, First, Last And Compare It With Below....');
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('First Index After Delete: '||v_values.FIRST);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete(Index 1 Deleted): '||v_values.LAST);
  DBMS_OUTPUT.PUT_LINE('Limit : '||v_values.LIMIT);

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Extending by 2 Elements Copying 6th Index Value to 7th and 8th');
  v_values.EXTEND(2,6);
  DBMS_OUTPUT.PUT_LINE('Printing 6th, 7th and 8th Index Values: '||v_values(6)||', '||v_values(7)||', '||v_values(8));
  DBMS_OUTPUT.PUT_LINE('Extending by 1 Elements With Null Value. And add 999 as 9th Element');
  v_values.EXTEND();
  v_values(9) := 999;
  DBMS_OUTPUT.PUT_LINE('Printing 9th Index Value: '||v_values(9));
  DBMS_OUTPUT.PUT_LINE('Count After Extend: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Extend: '||v_values.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Trim By Default Value, Which is last index');
  v_values.TRIM();
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 1: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 1: '||v_values.LAST);
  DBMS_OUTPUT.PUT_LINE('Trim By 2 Index....');
  v_values.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 2: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 2: '||v_values.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting From Index 2 to 4');
  v_values.DELETE(2, 4);
  DBMS_OUTPUT.PUT_LINE('First Element After Delete: '||v_values.FIRST);
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_values.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete: '||v_values.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('You cannot use LIMIT in Nested Table');  
END;

Output

First Element: 108
Last Element: 101
Count : 6, Always Return Number of Elements
First Index: 1, Always Return Smallest Index Value
Last Index : 6, Always Return Largest Index Value
======================================
Prior to First Index: 
Prior to Any Index(Except First): 4
Next to Last Index: 
Next to Any Index(Except Last): 2
Deleting Single Element: 108, First Index
1st Index Element Deleted, 7th Element Not Exist
======================================
Refer Above Values of Count, First, Last And Compare It With Below....
Count After Delete: 5
First Index After Delete: 2
Last Index After Delete(Index 1 Deleted): 6
Limit : 
======================================
Extending by 2 Elements Copying 6th Index Value to 7th and 8th
Printing 6th, 7th and 8th Index Values: 101, 101, 101
Extending by 1 Elements With Null Value. And add 999 as 9th Element
Printing 9th Index Value: 999
Count After Extend: 8
Last Index After Extend: 9
======================================
Trim By Default Value, Which is last index
Count After Trim by 1: 7
Last Index After Trim by 1: 8
Trim By 2 Index....
Count After Trim by 2: 5
Last Index After Trim by 2: 6
======================================
Deleting From Index 2 to 4
First Element After Delete: 5
Count After Delete: 2
Last Index After Delete: 6
======================================
You cannot use LIMIT in Nested Table

Collection Methods in VARRAY

Example

DECLARE 

  TYPE list_vt IS VARRAY(26) OF VARCHAR2(20); 
  v_list list_vt := list_vt(); 

BEGIN 
  DBMS_OUTPUT.put_line('Varray Limit: '|| v_list.LIMIT);  
  v_list := list_vt(51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70);
  
  DBMS_OUTPUT.PUT_LINE('First Element: '|| v_list(v_list.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last Element: '  || v_list(v_list.LAST));
  DBMS_OUTPUT.PUT_LINE('Count : '    || v_list.COUNT ||', '|| 'Always Return Number of Elements');
  DBMS_OUTPUT.PUT_LINE('First Index: '  || v_list.FIRST ||', '|| 'Always Return Smallest Index Value');
  DBMS_OUTPUT.PUT_LINE('Last Index : '  || v_list.LAST  ||', '|| 'Always Return Largest Index Value');

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Prior to First Index: '|| v_list.PRIOR(1));
  DBMS_OUTPUT.PUT_LINE('Prior to Any Index(Except First): '|| v_list.PRIOR(5));
  DBMS_OUTPUT.PUT_LINE('Next to Last Index: '|| v_list.NEXT(26));
  DBMS_OUTPUT.PUT_LINE('Next to Any Index(Except Last): '|| v_list.NEXT(13));

  DBMS_OUTPUT.PUT_LINE('Since Varrays Are Dense, Deleting Single Element Is Not Possible');
  -- v_list.DELETE(1); -- Not Possible in VARRAY
  -- EXISTS method does not raises SUBSCRIPT_OUTSIDE_LIMIT error
  IF v_list.EXISTS(21) THEN
    DBMS_OUTPUT.PUT_LINE(v_list(21));
  ELSE
    DBMS_OUTPUT.PUT_LINE('21st Element Not Exist');
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Extending by 2 Elements Copying 20th Index Value to 21st and 22nd');
  v_list.EXTEND(2,20);
  
  DBMS_OUTPUT.PUT_LINE('Printing 20th, 21st and 22nd Index Values: '||v_list(20)||', '||v_list(21)||', '||v_list(22));
  DBMS_OUTPUT.PUT_LINE('Extending by 1 Elements With Null Value. And add 71 as 23rd Element');
  v_list.EXTEND();
  v_list(23) := 71;
  DBMS_OUTPUT.PUT_LINE('Printing 23rd Index Value: '||v_list(23));
  DBMS_OUTPUT.PUT_LINE('Count After Extend: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Extend: '||v_list.LAST);
  
  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Trim By Default Value, Which is last index');
  v_list.TRIM();
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 1: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 1: '||v_list.LAST);
  DBMS_OUTPUT.PUT_LINE('Trim By 2 Index....');
  v_list.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('Count After Trim by 2: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Trim by 2: '||v_list.LAST);

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Deleting From Varray');
  v_list.DELETE();
  DBMS_OUTPUT.PUT_LINE('First Element After Delete: '||v_list.FIRST);
  DBMS_OUTPUT.PUT_LINE('Count After Delete: '||v_list.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last Index After Delete: '||v_list.LAST);
  DBMS_OUTPUT.PUT_LINE('Limit : '||v_list.LIMIT);

  DBMS_OUTPUT.PUT_LINE('======================================');
  DBMS_OUTPUT.PUT_LINE('Delete - All Or Nothing');
  DBMS_OUTPUT.PUT_LINE('You cannot Extend Varray Beyond Limit. Can''t Delete Selected Index');  
END;

Output

Varray Limit: 26
First Element: 51
Last Element: 70
Count : 20, Always Return Number of Elements
First Index: 1, Always Return Smallest Index Value
Last Index : 20, Always Return Largest Index Value
======================================
Prior to First Index: 
Prior to Any Index(Except First): 4
Next to Last Index: 
Next to Any Index(Except Last): 14
Since Varrays Are Dense, Deleting Single Element Is Not Possible
21st Element Not Exist
======================================
Extending by 2 Elements Copying 20th Index Value to 21st and 22nd
Printing 20th, 21st and 22nd Index Values: 70, 70, 70
Extending by 1 Elements With Null Value. And add 71 as 23rd Element
Printing 23rd Index Value: 71
Count After Extend: 23
Last Index After Extend: 23
======================================
Trim By Default Value, Which is last index
Count After Trim by 1: 22
Last Index After Trim by 1: 22
Trim By 2 Index....
Count After Trim by 2: 20
Last Index After Trim by 2: 20
======================================
Deleting From Varray
First Element After Delete: 
Count After Delete: 0
Last Index After Delete: 
Limit : 26
======================================
Delete - All Or Nothing
You cannot Extend Varray Beyond Limit. Can't Delete Selected Index.

Oracle – SET Operators

UNION ALLReturns all the rows retrieved by the queries, including duplicate rows.
UNIONReturns all non-duplicate rows retrieved by the queries.
INTERSECTReturns rows that are retrieved by both queries.
MINUSReturns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query.

Although the column names may be different, the number of columns and the column types returned by the queries must match.

UNION

-- Returns all non-duplicate rows retrieved by both queries.
-- ====================================== 
SELECT employee_id, first_name, salary
FROM employees WHERE employee_id < 105
UNION
SELECT employee_id, first_name, salary
FROM employees WHERE employee_id BETWEEN 102 AND 115;

SELECT employee_id, first_name, salary
FROM employees WHERE employee_id < 105
UNION
SELECT employee_id, first_name, salary
FROM employees WHERE employee_id BETWEEN 107 AND 115;

-- Data Types Of Selected Column Should be same  
-- =============================================
SELECT employee_id, first_name, hire_date
FROM employees WHERE employee_id < 105
UNION
SELECT employee_id, first_name, salary
FROM employees WHERE employee_id BETWEEN 107 AND 115;

-- Error:
ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 -  "expression must have same datatype as corresponding expression"

UNION ALL

-- Returns all the rows retrieved by the queries, including duplicate rows.
-- ==================================================
SELECT employee_id, first_name, last_name, salary 
FROM employees WHERE employee_id BETWEEN 100 AND 105
UNION ALL
SELECT employee_id, first_name, last_name, salary 
FROM employees WHERE employee_id BETWEEN 105 AND 110;

-- Here employee_id 105 will be the duplicate value.

INTERSECT

-- Returns rows that are retrieved by both queries.
-- =================================================
SELECT employee_id, first_name, last_name, salary 
FROM employees WHERE employee_id BETWEEN 100 AND 105
INTERSECT
SELECT employee_id, first_name, last_name, salary 
FROM employees WHERE employee_id BETWEEN 105 AND 110;

-- Here employee_id 105 will be retrieved by both queries.

MINUS

-- Rows retrieved by the second query are subtracted from the rows retrieved by the first query.
-- ====================================================
SELECT employee_id, first_name, last_name, salary 
FROM employees WHERE employee_id BETWEEN 100 AND 110
MINUS
SELECT employee_id, first_name, last_name, salary 
FROM employees WHERE employee_id BETWEEN 105 AND 110;

MULTISET

The two input nested tables must be of the same type, and the returned nested table is of the same type as well

DECLARE

  TYPE nested_typ IS TABLE OF NUMBER;

  nt1 nested_typ := nested_typ(1,2,3,3,5,7,5);

  nt2 nested_typ := nested_typ(3,2,1,3,4,6);
  
  nt_ua nested_typ := nested_typ();
  
  nt_ud nested_typ := nested_typ();
  
  nt_ea nested_typ := nested_typ();
  
  nt_ed nested_typ := nested_typ();
  
  nt_ia nested_typ := nested_typ();
  
  nt_id nested_typ := nested_typ();
  
BEGIN

  /* Returns a nested table whose values are 
  those of the two input nested tables */
  nt_ua := nt1 MULTISET UNION ALL nt2;
  DBMS_OUTPUT.PUT_LINE('MULTISET UNION ALL');
  FOR i IN 1..nt_ua.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(nt_ua(i));
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('----------------------');
  
  nt_ud := nt1 MULTISET UNION DISTINCT nt2;
  DBMS_OUTPUT.PUT_LINE('MULTISET UNION DISTINCT');
  FOR i IN 1..nt_ud.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(nt_ud(i));
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('----------------------');
  
  /* Returns a nested table whose elements are in the 
  first nested table but not in the second nested table*/
  nt_ea := nt1 MULTISET EXCEPT ALL nt2;
  DBMS_OUTPUT.PUT_LINE('MULTISET EXCEPT ALL');
  FOR i IN 1..nt_ea.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(nt_ea(i));
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('----------------------');
  
  nt_ed := nt1 MULTISET EXCEPT DISTINCT nt2;
  DBMS_OUTPUT.PUT_LINE('MULTISET EXCEPT DINSTINCT');
  FOR i IN 1..nt_ed.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(nt_ed(i));
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('----------------------');
  
  /* Returns a nested table whose values are 
  common for those of the two input nested tables*/
  nt_ia := nt1 MULTISET INTERSECT ALL nt2;
  DBMS_OUTPUT.PUT_LINE('MULTISET INTERSECT ALL');
  FOR i IN 1..nt_ia.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(nt_ia(i));
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('----------------------');
  
  nt_id := nt1 MULTISET INTERSECT DISTINCT nt2;
  DBMS_OUTPUT.PUT_LINE('MULTISET INTERSECT DISTINCT');
  FOR i IN 1..nt_id.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(nt_id(i));
    END LOOP;

END;

SUBMULTISET

DECLARE

  TYPE nested_typ IS TABLE OF NUMBER;

  nt1 nested_typ := nested_typ(1,2,3);

  nt2 nested_typ := nested_typ(3,2,1);

  nt3 nested_typ := nested_typ(2,3,1,3);

  nt4 nested_typ := nested_typ(1,2,4);

  reponse BOOLEAN;

  typ_count NUMBER;

  PROCEDURE verify_sp(test BOOLEAN DEFAULT NULL, label IN VARCHAR2 DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS

  BEGIN

     IF test IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE(label || ' -> ' || CASE test WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);

     END IF;

     IF quantity IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE('The entered quantity is ' || TO_CHAR(quantity));

     END IF;

  END;

BEGIN

  reponse := nt1 IN (nt2,nt3,nt4); -- true, nt1 correspond to nt2

  verify_sp(test => reponse, label => 'nt1 IN (nt2,nt3,nt4)');

  reponse := nt1 SUBMULTISET OF nt3; -- true, all elements correpond

  verify_sp(test => reponse, label => 'nt1 SUBMULTISET OF nt3');

  reponse := nt1 NOT SUBMULTISET OF nt4; -- true

  verify_sp(test => reponse, label => 'nt1 NOT SUBMULTISET OF nt4');

  -- CARDINALITY returns the number of elements
  typ_count := CARDINALITY(nt3); -- number of elements of nt3

  verify_sp(quantity => typ_count);

  typ_count := CARDINALITY(SET(nt3)); -- number of distinct elements

  verify_sp(quantity => typ_count);


  reponse := 4 MEMBER OF nt1; -- false, no corresponding element

  verify_sp(test => reponse, label => '4 MEMBER OF nt1');

  reponse := nt3 IS A SET; -- false, nt3 have duplicate elements

  verify_sp(test => reponse, label => 'nt3 IS A SET' );

  reponse := nt3 IS NOT A SET; -- true, nt3 have duplicate elements

  verify_sp(test => reponse, label => 'nt3 IS NOT A SET' );

  reponse := nt1 IS EMPTY; -- false, nt1 have elements

  verify_sp(test => reponse, label => 'nt1 IS EMPTY' );

END;
/

References

Oracle – DBMS_XMLGEN

The DBMS_XMLGEN built-in constructor, accepts a query directly

 DECLARE
     v_xmltype XMLTYPE;
 BEGIN
     v_xmltype := DBMS_XMLGEN.getxmltype('SELECT employee_id
                                               , first_name
                                               , hire_date
                                               , salary
                                            FROM employees
                                           WHERE employee_id IN (100, 101, 102, 103, 104)'
                                        );
 DBMS_OUTPUT.PUT_LINE(v_xmltype.GETCLOBVAL);
 END;
 /

But it also provides procedures to change the ROWSET and ROW tags.

DECLARE
     v_xmltype XMLTYPE;
     v_ctx DBMS_XMLGEN.CTXHANDLE;
 BEGIN
     v_ctx := DBMS_XMLGEN.NEWCONTEXT('SELECT employee_id
                                               , first_name
                                               , hire_date
                                               , salary
                                            FROM employees
                                           WHERE employee_id IN (100, 101, 102, 103, 104)'
                                        );
    DBMS_XMLGEN.SETROWSETTAG(v_ctx, 'Employees'); 
    DBMS_XMLGEN.SETROWTAG(v_ctx, 'Employee');
    v_xmltype := DBMS_XMLGEN.GETXMLTYPE(v_ctx) ;
    DBMS_XMLGEN.CLOSECONTEXT(v_ctx);
    DBMS_OUTPUT.PUT_LINE(v_xmltype.GETCLOBVAL);
 END;
 /

Oracle – Cursor

It’s a Private Work Area.
SGA – System Global Area.
PGA – Programmable Global Area.

Implicit Cursor

  1. Qry returns exactly one row.
  2. DML default name “SQL” declared by PL/SQL engine.

Explicit Cursor

  1. Qry returns more than one row.
  2. DML name need to be declared.

Cursor Attributes

  1. %FOUND
  2. %NOTFOUND
  3. %ISOPEN
  4. %ROWCOUNT

Cursor Usage

  1. CURSOR FOR LOOP
  2. PARAMETERIZED CURSOR
  3. FOR UPDATE OF
  4. WHERE CURRENT OF
  5. NOWAIT

Cursor Attributes – %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

 -- %TYPE
 DECLARE
     v_employee_id     employees.employee_id%TYPE;
     v_first_name      employees.first_name%TYPE;
     v_hire_date       employees.hire_date%TYPE;
     v_salary          employees.salary%TYPE;
     v_department_id   employees.department_id%TYPE;
     v_count           NUMBER := 0;
 CURSOR emp_c IS 
  SELECT employee_id      
       , first_name      
       , hire_date      
       , salary      
       , department_id   
    FROM employees  
   WHERE department_id = 60;
 BEGIN
  DBMS_OUTPUT.PUT_LINE('----------------------------*');
  OPEN emp_c; 
  LOOP     
  FETCH emp_c     
  INTO v_employee_id, v_first_name, v_hire_date, v_salary, v_department_id;     
  EXIT WHEN emp_c%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE('#'||emp_c%ROWCOUNT);
  DBMS_OUTPUT.PUT_LINE('Employee ID: '||v_employee_id);
  DBMS_OUTPUT.PUT_LINE('First Name: '||v_first_name); 
  DBMS_OUTPUT.PUT_LINE('Hire Date: '||v_hire_date); 
  DBMS_OUTPUT.PUT_LINE('Salary: '||v_salary); 
  DBMS_OUTPUT.PUT_LINE('Department ID: '||v_department_id); 
  DBMS_OUTPUT.PUT_LINE('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');     
  --EXIT WHEN emp_c%FOUND, EXIT condition need to checked before or after is depends upon requirement.     
  v_count := v_count + 1; 
  END LOOP; 
 --CLOSE emp_c; 
 DBMS_OUTPUT.PUT_LINE('Total no of records :'||v_count); 
 IF emp_c%ISOPEN THEN   
 CLOSE emp_c;   
 DBMS_OUTPUT.PUT_LINE('Cursor Closed'); 
 ELSE   
 DBMS_OUTPUT.PUT_LINE('Cursor Already Closed'); 
 END IF;
END;
 /

%ROWTYPE

DECLARE
 CURSOR emp_c IS 
  SELECT * FROM employees WHERE department_id = 60; 
 v_alldata   emp_c%ROWTYPE; 
v_count     NUMBER := 0;

BEGIN
 DBMS_OUTPUT.PUT_LINE('--------------*');
 OPEN emp_c; 
 LOOP 
 FETCH emp_c INTO v_alldata; 
 EXIT WHEN emp_c%NOTFOUND; 
 DBMS_OUTPUT.PUT_LINE('#'||emp_c%ROWCOUNT);
 DBMS_OUTPUT.PUT_LINE('Employee ID: '||v_alldata.employee_id); 
 DBMS_OUTPUT.PUT_LINE('First Name: '||v_alldata.first_name); 
 DBMS_OUTPUT.PUT_LINE('Hire Date: '||v_alldata.hire_date); 
 DBMS_OUTPUT.PUT_LINE('Salary: '||v_alldata.salary); 
 DBMS_OUTPUT.PUT_LINE('Department ID: '||v_alldata.department_id); 
 DBMS_OUTPUT.PUT_LINE('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*'); 
 v_count := v_count + 1; 
 END LOOP; 
 --CLOSE emp_c; 
 DBMS_OUTPUT.PUT_LINE('Total no of records :'||v_count); 
 IF emp_c%ISOPEN THEN   
 CLOSE emp_c;   
 DBMS_OUTPUT.PUT_LINE('Cursor Closed'); 
 ELSE   
 DBMS_OUTPUT.PUT_LINE('Cursor Already Closed'); 
 END IF;
END;
/

%ROWCOUNT

CREATE TABLE cur_attrs_tb(ID NUMBER);
 BEGIN
   INSERT INTO cur_attrs_tb VALUES(100);
   INSERT INTO cur_attrs_tb VALUES(101);
   INSERT INTO cur_attrs_tb VALUES(102);
   INSERT INTO cur_attrs_tb VALUES(103);
   INSERT INTO cur_attrs_tb VALUES(104);
   INSERT INTO cur_attrs_tb VALUES(105);
   COMMIT;
 END;
  /
 -- %ROWCOUNT
 BEGIN
    UPDATE cur_attrs_tb
       SET ID = 300
     WHERE ID < 103;
   --COMMIT;
   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Row Get Updated');
 END;
  /
 
 BEGIN
     DELETE FROM cur_attrs_tb WHERE ID = 300;
     DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Row Get Deleted');
 END;
 /
 
 BEGIN
     INSERT INTO cur_attrs_tb VALUES(900);
     INSERT INTO cur_attrs_tb VALUES(900);
     INSERT INTO cur_attrs_tb VALUES(900);
     INSERT INTO cur_attrs_tb VALUES(900);
     DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Row Get Inserted');
     COMMIT;
 END;
 /

WHERE CURRENT OF & NOWAIT

 -- WHERE CURRENT OF & NOWAIT
 --===========================
 DECLARE
     CURSOR emp_c
     IS    
     SELECT ROWID
          , employee_id
          , first_name
          , hire_date
          , (ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)/12)) exep 
       FROM employees 
        FOR UPDATE OF salary NOWAIT;
 BEGIN
     FOR i IN emp_c
     LOOP
         IF i.exep > 25 THEN
             UPDATE employees
             SET salary = salary * 2
             WHERE CURRENT OF emp_c;
           --WHERE ROWID = i.ROWID;
         END IF;
     END LOOP;
     COMMIT;
 END;
 /

%FOUND & %NOTFOUND

 BEGIN
     UPDATE cur_attrs_tb
        SET ID = 300
      WHERE ID = 900;
      IF SQL%FOUND THEN 
       DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'Rows Get Updated'); 
      END IF; 
      IF SQL%NOTFOUND THEN
       DBMS_OUTPUT.PUT_LINE('No Rows Get Updated'); 
      END IF;
      COMMIT;
 END;
 /

CURSOR FOR LOOP

 DECLARE
   CURSOR emp_c
   IS
   SELECT employee_id
        , first_name
        , hire_date
        , salary       
        , department_id
     FROM employees
    WHERE department_id = 60;
 BEGIN
     DBMS_OUTPUT.PUT_LINE('-----------------------------');
     FOR i IN emp_c 
     LOOP     
     DBMS_OUTPUT.PUT_LINE('Employee ID   : '||i.employee_id);    
     DBMS_OUTPUT.PUT_LINE('First Name    : '||i.first_name);     
     DBMS_OUTPUT.PUT_LINE('Hire Date     : '||i.hire_date);     
     DBMS_OUTPUT.PUT_LINE('Salary        : '||i.salary);     
     DBMS_OUTPUT.PUT_LINE('Department ID : '||i.department_id); 
     DBMS_OUTPUT.PUT_LINE('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*'); 
     END LOOP; 
     FOR j IN(SELECT * FROM departments WHERE department_id IN(10,20,30,40,50)) 
     LOOP     
     DBMS_OUTPUT.PUT_LINE('Department Name :'||j.department_name);
     END LOOP;
 END;
 /  

PARAMETERIZED CURSOR

 DECLARE
     CURSOR emp_c(p_department_id NUMBER)
     IS
     SELECT employee_id
          , first_name
          , hire_date
          , salary
          , department_id 
       FROM employees
      WHERE department_id = p_department_id;
 BEGIN
     FOR i IN emp_c(90)
     LOOP
         DBMS_OUTPUT.PUT_LINE('Employee ID   : '||i.employee_id);
         DBMS_OUTPUT.PUT_LINE('First Name    : '||i.first_name);
         DBMS_OUTPUT.PUT_LINE('Hire Date     : '||i.hire_date);
         DBMS_OUTPUT.PUT_LINE('Salary        : '||i.salary);
         DBMS_OUTPUT.PUT_LINE('Department ID : '||i.department_id);
         DBMS_OUTPUT.PUT_LINE('-----------------------------');
     END LOOP;
 END;
 /  

Oracle – Bulk Exceptions

The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete

Example

 CREATE TABLE bulk_insert_tb(seq_num NUMBER NOT NULL);

 DECLARE
     TYPE seq_num_typ IS TABLE OF bulk_insert_tb.seq_num%TYPE;
     v_dat      seq_num_typ := seq_num_typ();
     v_ex_count NUMBER(4);
     abort_ex   EXCEPTION;
     PRAGMA EXCEPTION_INIT(abort_ex, -20001);
 BEGIN
     FOR i in 1..200
     LOOP
         v_dat.EXTEND;
         v_dat(v_dat.LAST) := i;
     END LOOP;
     v_dat(100) := NULL; 
     v_dat(150) := NULL; 
     BEGIN     
     FORALL i IN v_dat.FIRST..v_dat.LAST SAVE EXCEPTIONS
         INSERT INTO bulk_insert_tb VALUES(v_dat(i)); 
     COMMIT; 
     EXCEPTION     
         WHEN abort_ex THEN         
             v_ex_count := SQL%BULK_EXCEPTIONS.COUNT; 
             FOR i IN 1..v_ex_count 
             LOOP DBMS_OUTPUT.PUT_LINE('Error: ' || i ||' Array Index: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||         ' Message: ' || SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
             END LOOP; 
      END;
 END;

Reference