Plsql Uninitialized collections

Question

I have the following types defined into a spec of a package

type column_info is record (col_name varchar2(20), col_value varchar2(1000));
type c_info is varray(10) of column_info;
type table_info is record (table_name varchar2(20), col_info c_info);

In declaration part package body I have

t_info table_info;

Inside of a procedure in the body of package I have

t_info:=null;
t_info.table_name:='gl_temp_report1';
t_info.col_info(1).col_name:='table_idx';
t_info.col_info.extend;
t_info.col_info(2).col_name:='table_row';
t_info.col_info.extend;
t_info.col_info(3).col_name:='table_row_detail';

Even package compile succesfully , at runtime I get the exception ORA-06531: Reference to uninitialized collection .
How I initialize col_info collection ? I tried to initialize t_info.col_info() but I get "There is no function" like this one . TIA, Aurel

Solution 1

You should initialize all collections (including nested) properly before accessing them.
They are atomically nulls before initialization.

t_info := table_info('gl_temp_report1', c_info());

You also must call extend before assigning a value for each varray element (or extend once with extend(3)).
Or do it all in one statement:

t_info := table_info('gl_temp_report1', c_info('table_idx','table_row','table_row_detail'));

Solution 2

To perform initialization you'll need to add an initialization block to the package body, in a manner similar to the following:

CREATE OR REPLACE PACKAGE BODY your_package IS
  t_info  table_info;

  -- Whatever other procedure definitions, etc, are needed

BEGIN  -- package initialization
  t_info.table_name:='gl_temp_report1';
  t_info.col_info := c_info();
  t_info.col_info.extend;
  t_info.col_info(1).col_name:='table_idx';
  t_info.col_info.extend;
  t_info.col_info(2).col_name:='table_row';
  t_info.col_info.extend;
  t_info.col_info(3).col_name:='table_row_detail';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception!');  -- Add whatever error handling is needed
END your_package;

Share and enjoy.

Comments

[user1540471]

I get "no function with name 'TABLE_INFO' exists in this scope

[Egor Skriptunoff]

@user1540471 - I'm sorry, I have forgotten that records, unlike objects, do not have convenient constructor. Each field of record should be initialized separately. t_info.table_name:='gl_temp_report1'; and t_info.col_info:=c_info();

[Jon Heller]

This does not work, there are syntax errors and these constructors do not work with RECORDs.