Table Type in Oracle PL SQL Example

PL SQL table types are actually collections, like an array. With the help of PL SQL collections, you can process bulk data efficiently. In this post, I will teach you very basic and most frequently used commands for PL SQL table type collections. Below is the table type in Oracle PL SQL example.

First you need to declare it in the declare section of PL SQL block. Here is the syntax with example:

Table Type in Oracle PL SQL Example

1. Declare PL SQL Table Types

Type any_table_type is table of emp%rowtype index by binary_integer;
emp_rec any_table_type;

The above commands will declare the table type any_table_type of emp table row type and then initialize with emp_rec. After declaration, you can manually assign the values to it. Below is the example:

2. Assign Values To PL SQL Table Types

set serveroutput on;
declare
Type any_table_type is table of emp%rowtype 
    index by binary_integer;
emp_rec any_table_type;
begin
emp_rec(1).ename := 'abc'; /* assigning values */
emp_rec(2).ename := 'xyz';
dbms_output.put_line(emp_rec(1).ename); /* accessing values */
dbms_output.put_line(emp_rec(2).ename);
end;

Because PL SQL tables are arrays like, so the values should be assign by giving array element number, as shown above. Now below is the example to how to Delete from PL SQL table type collections.

3. Deleting Elements From PL SQL Table Types

set serveroutput on;
declare
Type any_table_type is table of emp%rowtype index by binary_integer;
emp_rec any_table_type;
begin
emp_rec(1).ename := 'abc';
emp_rec(2).ename := 'xyz';
emp_rec.delete(1); /* will delete first element */
for i in emp_rec.first .. emp_rec.last loop /* loop through the array */
dbms_output.put_line(emp_rec(i).ename);
end loop;
end;

The above example will delete the first element and will print only the second. When you will execute the above block, the output will be:

xyz
PL/SQL procedure successfully completed.

To delete all the elements from PL SQL table type, below is the example:

emp_rec.delete;

4. Populating PL SQL Table Types Using Bulk Collect

Below is the table type in oracle PL SQL example to fetch the data from emp table and then it will populate the PL SQL table type using Bulk Collect and then it will update the emp table using FORALL. Here is the example:

set serveroutput on;

DECLARE
   CURSOR c
   IS
      SELECT * FROM emp;

   TYPE any_table_type IS TABLE OF emp%ROWTYPE
                             INDEX BY BINARY_INTEGER;

   emp_rec   any_table_type;
BEGIN
   OPEN c;

   FETCH c
   BULK COLLECT INTO emp_rec;

   CLOSE c;

   FORALL i IN emp_rec.FIRST .. emp_rec.LAST
      UPDATE emp
         SET comm = emp_rec (i).sal * 10 / 100
       WHERE empno = emp_rec (i).empno;

   COMMIT;
END;

You can also check my PL SQL Procedure Tool to generate the script for your development.

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

One comment

  1. SET SERVEROUTPUT ON
    SET VERIFY OFF

    DECLARE
      TYPE list IS TABLE OF VARCHAR2(100);
      lv_input1 VARCHAR2(100);
      lv_input2 VARCHAR2(100);
      lv_input3 VARCHAR2(100);
       
      lv_input list := list(lv_input1, lv_input2, lv_input3);

       
    -- Enter your solution here.
    BEGIN
       lv_input(1) := '&1';
       lv_input(2) := '&2';
       lv_input(3) := '&3';
       
      dbms_output.put_line(lv_input.COUNT);
       
      FOR i IN 1..lv_input.COUNT LOOP
        dbms_output.put_line(lv_input(i));
      END LOOP;
    END;
    /

    Maybe you can help me get this to print the the contents of the lv_input
    Apparently I cant get the user input to populate lv_input. I will work if I put in actuall values. Just not with user input.

Comments are closed.