CODEWITHSUNDEEP
sqloracle-databaseplsqltypesoracle12c
Philippe Veilleux-Girard
Philippe Veilleux-Girard

Reputation: 75

PLS-00642 and ORA-22905 on Oracle 12c when accessing to a function that return a custom type declared in the DECLARE section of a pl/sql block

In my production environment, I want to test a new technique to optimize a request, but I can't create anything, like packages or types. So I want to be able to test this technique without creating anything.

In an answer to this post (Does Oracle 12 have problems with local collection types in SQL?), wernfried-domscheit answered that Oracle 12c support local collection types in SQL.

Documentation Database New Features Guide says:

PL/SQL-Specific Data Types Allowed Across the PL/SQL-to-SQL Interface

The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection's data type had to be declared at the schema level.)

So I tried to implement this but it failed at compilation with errors PLS-00642 and ORA-22905.

I did a little example of my problem :

DECLARE
    TYPE simple_type IS RECORD
    (
        no             NUMBER (6),
        description    VARCHAR2 (50)
    );

    line                     SYS_REFCURSOR;
    line_no                  NUMBER (6);
    line_description         VARCHAR2 (50);

    TYPE table_simple_type IS TABLE OF simple_type;

    ret_table_simple_type    table_simple_type;

    FUNCTION calc_table_simple_type
        RETURN table_simple_type
    IS
        ret                 table_simple_type;
        data_simple_type    simple_type;
    BEGIN
        ret := table_simple_type ();

        FOR i IN 1 .. 5
        LOOP
            data_simple_type.no := i;
            data_simple_type.description := 'Test';
            ret.EXTEND ();
            ret (ret.COUNT) := data_simple_type;
        END LOOP;

        RETURN ret;
    END;
BEGIN
    ret_table_simple_type := calc_table_simple_type ();

    OPEN line FOR SELECT no, description FROM TABLE (ret_table_simple_type);

    LOOP
        FETCH line INTO line_no, line_description;

        EXIT WHEN line%NOTFOUND;
        DBMS_OUTPUT.Put_Line (line_no || ' ' || line_description);
    END LOOP;
END;

It throws at compilation:

Error at line 2
ORA-06550: Line 37, column 54 :
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: Line 37, column 47 :
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: Line 37, column 19 :
PL/SQL: SQL Statement ignored

Why do I get this error if it is now supported in Oracle 12c? And how can I avoid the problem to test my two functions without creating anything?

I can't call the function calc_table_simple_type () in the SELECT or else I get the error :

PLS-00231: function 'CALC_TABLE_SIMPLE_TYPE' may not be used in SQL

I can't declare simple_type as an Object because I get :

PLS-00540: object not supported in this context.

In the final version of the code, simple_type will be an Object and the select will call the function directly because they will be in a package, but for now I can't do that without testing it first in the production environment because my other environment are not really representative (I know it's not ideal but that's my situation)

Here's the complete output of SELECT * FROM V$VERSION

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
PL/SQL Release 12.1.0.2.0 - Production  0
CORE    12.1.0.2.0  Production  0
TNS for Solaris: Version 12.1.0.2.0 - Production    0
NLSRTL Version 12.1.0.2.0 - Production  0

Upvotes: 2

Views: 1025

Answers (1)

Ucello
Ucello

Reputation: 276

If you really have to use types, can you reuse any existing type from the database that has 2 columns?

SELECT TYPE_NAME
FROM   USER_TYPES
WHERE  TYPECODE  = 'OBJECT' and attributes='2';

Presumably, you'll want to find the type that has NUMBER and a VARCHAR for your needs.

Upvotes: 1

Related Questions