An Introduction - Collections and Ref Cursors

View All Scripts Login to Run Script

Script Name An Introduction - Collections and Ref Cursors
Description This script gives an introduction of how to use a nested table collection and store results of a dynamic query into this nested table collection and how to pass this result into another procedure as argument with the help of ref cursors.
Area PL/SQL General
Contributor Arnab P
Created Saturday August 01, 2020

Statement 1

This creates the package specification. The most important thing to note here is the global type declarations. The use of collections is a 3 step process as below:- 1. Create a user defined record type 2. Create a collection type in the next command 3. Instantiate this collection type in the package body

Create the Package Specifications

create or replace package pkg_table_record_counts    
is    
   
    type record_type_t is record   
    (   
        table_name all_tables.table_name%type,   
        record_count number   
    );   
       
    type table_rec_count_coll is table of record_type_t;   
       
    procedure pr_table_record_counts(p_ref_cursor out sys_refcursor);   
   
end pkg_table_record_counts;

Package created.

Statement 2

The package body consists of instantiating the collection type object and using it. Also note that the output argument of the procedure is a ref cursor variable which is used to collect the data held in the collection object and passing it out to the calling program.

Create the Package Body

create or replace package body pkg_table_record_counts   
is    
   
    procedure pr_table_record_counts(p_ref_cursor out sys_refcursor)   
    is    
       
       
    nested_table_rec_counts table_rec_count_coll := table_rec_count_coll();   
       
    cursor c_rec    
    is    
    select    
    table_name    
    from all_tables t1    
    where t1.owner = 'HR'   
    order by t1.table_name;   
       
    l_coll_index pls_integer := 1;   
    l_sql_string varchar2(4000);   
    l_rec_count number;   
   
    begin    
       
        for r_rec in c_rec    
        loop   
           
        l_sql_string := 'select count(*) from hr.' || r_rec.table_name;   
        execute immediate l_sql_string into l_rec_count;   
           
        nested_table_rec_counts.extend;   
        nested_table_rec_counts(l_coll_index).table_name := r_rec.table_name;   
        nested_table_rec_counts(l_coll_index).record_count := l_rec_count;   
           
           
        l_coll_index := l_coll_index + 1;   
           
        end loop;   
       
    open p_ref_cursor for select * from table(nested_table_rec_counts);    
       
    end pr_table_record_counts;   
   
end pkg_table_record_counts;

Package Body created.

Statement 3

This is an anonymous PL/SQL block that calls the procedure. This block declares and stores the results in a ref cursor variable and then uses a loop to print out the results of the ref cursor (which in turn is the content of the collection object) into the output console. Also note how the ref cursor variable helps to hold the collection contents into a table format result set and pass the output to the calling function.

Anonymous Calling PL/SQL Block

declare     
    
x_ref_cursor SYS_REFCURSOR;    
   
l_table_name varchar2(100);   
l_record_count number;    
    
begin    
    
dbms_output.put_line(rpad('Table Name', 20, ' ') || 'Record Count');   
    
pkg_table_record_counts.pr_table_record_counts(p_ref_cursor => x_ref_cursor);    
   
   
loop   
   fetch x_ref_cursor into l_table_name, l_record_count;   
   exit when x_ref_cursor%NOTFOUND;   
   dbms_output.put_line(rpad(l_table_name, 20, ' ') || l_record_count);   
      
end loop;   
   
   
end;

Statement processed.
Table Name          Record Count
COUNTRIES           25
DEPARTMENTS         27
EMPLOYEES           107
JOBS                19
JOB_HISTORY         11
LOCATIONS           23
REGIONS             4

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database