ORA-00904: "PREV_VAL_R": invalid identifier-CodePudding

I am trying to bulk collect values into a collection. I'm getting ORA-00904: "PREV_VAL_R" invalid identifier. The code is simplified.

PROCEDURE MY_HELPER() IS
----------------------------------------------------------------
    TYPE PREV_VAL_R IS RECORD( 
            DESC1     VARCHAR2(250), 
            MONTH_VAL VARCHAR2(250),
            MONTH_ALL VARCHAR2(250));
    TYPE PREV_VAL_T IS TABLE OF  PREV_VAL_R;   
                
BEGIN
    SELECT PREV_VAL_R(DESC1, MONTH_VAL, MONTH_ALL)
    BULK COLLECT INTO PREV_VAL_T
    FROM MY_TABLE;
END MY_HELPER;

What am I doing wrong? Thanks!

CodePudding user response：

You are calling PREV_VAL_R() as an object constructor, but that is a PL/SQL record type, not something known at SQL level.

You can bulk collect the columns straight into your table of records; though it needs to be an instance of that collection type, not the type itself, e.g.:

    PROCEDURE MY_HELPER() IS
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    TYPE PREV_VAL_R IS RECORD( 
            DESC1     VARCHAR2(250), 
            MONTH_VAL VARCHAR2(250),
            MONTH_ALL VARCHAR(250));
    TYPE PREV_VAL_T IS TABLE OF  PREV_VAL_R; 
    L_PREV_VAL_T PREV_VAL_T;
                
    BEGIN
        SELECT DESC1, MONTH_VAL, MONTH_ALL
        BULK COLLECT INTO L_PREV_VAL_T
        FROM MY_TABLE;
    END MY_HELPER;

I've added L_PREV_VAL_T PREV_VAL_T; to declare the instance of the collection, and the the INTO PREV_VAL_T to INTO L_PREV_VAL_T to target that instance. (You can/should use a more meaningful name, of course...)

db<>fiddle

CodePudding user response：

Type prev_val_r is a plain old PL/SQL record type, not an object type with a constructor. You can just list the items, without any constructor.

Also, prev_val_t is a type, so you still need a variable of that type. I've named mine t in the example below:

create or replace procedure my_helper
as
    type prev_val_r is record(
         desc1     varchar2(250)
        ,month_val varchar2(250)
        ,month_all varchar2(250));

    type prev_val_t is table of prev_val_r;
    
    t prev_val_t;
begin
    select 'x', 'y', 'z' bulk collect into t from dual;
end my_helper;

21c's Qualified Expressions for PL/SQL record types are similar to object constructors, so you could write for example

r prev_val_r := prev_val_r('x', 'y', 'z');

but they are for PL/SQL only and not SQL. Presumably the PL/SQL compiler builds the code for you internally so it's just syntactic sugar, and that's why Oracle are careful to avoid calling it a constructor.