oracle-databaseplsqloracle11ginitializationnested-table

Initialize a PLSQL record type initialize a nested table of record type


I am using Oracle 11g

getting error

no function with name 'T_TAB' exists in this scope

declare
TYPE t_tab IS RECORD
(
col1 number,
col2 number
);
TYPE t_tab_arr IS TABLE OF t_tab;
l_tab1 t_tab_arr := t_tab_arr (t_tab(1,2),t_tab(2,3));
BEGIN
NULL;
END;
/

Solution

  • In Oracle 18, your code works (db<>fiddle).

    In Oracle 11g, there is no constructor method for PL/SQL RECORD data types (like there is for SQL OBJECT data types); instead, you need to declare the RECORD values and assign values to their fields before putting them into the collection:

    DECLARE
      TYPE t_tab IS RECORD(
        col1 number,
        col2 number
      );
      TYPE t_tab_arr IS TABLE OF t_tab;
      
      l_t1 T_TAB;
      l_t2 T_TAB;
      l_tab1 T_TAB_ARR;
    BEGIN
      l_t1.col1 := 1;
      l_t1.col2 := 2;
      l_t2.col1 := 3;
      l_t2.col2 := 4;
      l_tab1 := T_TAB_ARR( l_t1, l_t2 );
      
      FOR i IN 1 .. l_tab1.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE( i || ': (' || l_tab1(i).col1 || ', ' || l_tab1(i).col2 ||')' );
      END LOOP;
    END;
    /
    

    Which outputs:

    1: (1, 2)
    2: (3, 4)
    

    db<>fiddle here