How to query the return value of RECORD Type of a Function in oracle-database

in oracle-database i have problem with query on function returned value when return type of function is record type

CREATE OR REPLACE PACKAGE pkg1 IS
  TYPE LST_REC IS RECORD(
    L_1          NUMBER,
    L_2          NUMBER
  );
  FUNCTION func1(p_par NUMBER) RETURN LST_REC;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg1 IS
FUNCTION func1(p_par NUMBER) RETURN LST_REC IS
    v_row LST_REC
    BEGIN
        v_row.l_1:=p_par;
        v_row.l_1:=p_par+1;
            RETURN v_row;
    END;          
END;
/

and when i run query

SELECT pkg1.func1(10).l_1 FROM dual;

raise below error

ORA-00902: invalid datatype

can help me to solve this problem?

Solution

As @Littlefoot showed you can use a schema-level SQL object type, rather than a PL/SQL record type; but if you know you will only ever want one object returned then you don't have to use a table type as well:

CREATE OR REPLACE TYPE lst_obj IS OBJECT (l_1 NUMBER, l_2 NUMBER);
/

CREATE OR REPLACE PACKAGE pkg1
IS
    FUNCTION func1 (p_par NUMBER) RETURN lst_obj;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg1
IS
    FUNCTION func1 (p_par NUMBER)
    RETURN lst_obj
    IS
        v_row  lst_obj := lst_obj(null, null);
    BEGIN
        v_row.l_1:=p_par;
        v_row.l_2:=p_par+1;
        RETURN v_row;
    END;
END;
/

or more simply in this example:

CREATE OR REPLACE PACKAGE BODY pkg1
IS
    FUNCTION func1 (p_par NUMBER)
    RETURN lst_obj
    IS
    BEGIN
        RETURN lst_obj(p_par, p_par+1);
    END;
END;
/

But referring to the object fields is a bit more awkward:

SELECT pkg1.func1(10).l_1, pkg1.func1(10).l_2 FROM DUAL;

PKG1.FUNC1(1).L_1	PKG1.FUNC1(1).L_2
10	11

which may execute the function more than once, particularly if it isn't declared as deterministic (which it could be in this very simple example); and you can use a subquery (CTE or inline view) to only call it once:

SELECT t.o.l_1, t.o.l_2
FROM (
  SELECT pkg1.func1(10) AS o FROM DUAL
) t

O.L_1	O.L_2
10	11

fiddle

So using a table type may still be preferable and easier to work with overall.

You can only refer to a PL/SQL record type in a PL/SQL context, which is why your original query errors. If you only need to refer to the function from PL/SQL then you can use your original code (with typos corrected) and assign its return value to a PL/SQL variable, e.g.:

DECLARE
    v_row pkg1.LST_REC;
BEGIN
    v_row := pkg1.func1(10);
    dbms_output.put_line('Got ' || v_row.l_1 || ' and ' || v_row.l_2);
END;
/

Got 10 and 11

fiddle

But only from PL/SQL. If you need to call it from SQL and can't change the original function then you can add a wrapper function that converts the PL/SQL record type to a SQL object type.