CODEWITHSUNDEEP
oracle-databasecollectionsplsqluser-defined-typesinitialization
user1540471
user1540471

Reputation: 431

Plsql Uninitialized collections

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

Upvotes: 2

Views: 18238

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 21

You can create t_info as shown: 

declare
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);
  t_info table_info;
begin
  t_info.table_name := null;
  t_info.col_info := c_info();
  for i in 1..10 loop
    t_info.col_info.extend;
    t_info.col_info(i).col_name := null;
    t_info.col_info(i).col_value := null;
  end loop;
end;
/

Cheers!

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 3

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23727

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'));

Upvotes: 3

Related Questions