ORA-22905 when calling ODCI Table functions from PL/SQL

Back in 2015 I experimented using ODCITable* functions to dynamically parse delimited text (see blog post here.)

Now blog reader Daniel Cabanillas points out, that it raises an error when used in PL/SQL. Silly me - I had only tried it out in SQL (my favorite language) and had completely missed that it failed in PL/SQL.

Lucky for me, the classic workaround of dynamic SQL works here too 😁


Look in the old blog post for details about the inner workings of the function I created with the ODCI Table interface. Here's a recap piece of SQL showing it works:

select *
  from table(
          delimited_col_row.parser(
             '1:Adam:M,2:Belinda:F,3:Carl:M'
           , 'ID:NUMBER,NAME:VARCHAR2(10),GENDER:VARCHAR2(1)'
           , ':'
           , ','
          )
       )
 order by gender, name
/

        ID NAME       G
---------- ---------- -
         2 Belinda    F
         1 Adam       M
         3 Carl       M

If I try to do it in PL/SQL (here shown with a simple anonymous block, but the same happens in a procedure or function), I get an error:

begin
   for rec in (
      select *
        from table(
                delimited_col_row.parser(
                   '1:Adam:M,2:Belinda:F,3:Carl:M'
                 , 'ID:NUMBER,NAME:VARCHAR2(10),GENDER:VARCHAR2(1)'
                 , ':'
                 , ','
                )
             )
       order by gender, name
   ) loop
      dbms_output.put_line(rec.name);
   end loop;
end;
/

        from table(
             *
ERROR at line 4:
ORA-06550: line 4, column 14:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 3, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 14, column 28:
PLS-00364: loop index variable 'REC' use is invalid
ORA-06550: line 14, column 7:
PL/SQL: Statement ignored

Most often you see the ORA-22905 error when you use the table() function on a collection type that's only defined in PL/SQL, not as a schema object. (Those restrictions have been lifted somewhat in newer database versions, but that's another story.)

My function delimited_col_row.parser is special in that the return datatype is not defined until hard-parse time. The SQL parser understands this, but it appears the PL/SQL parser does not like this - it looks like the PL/SQL parser believes this to be an error, that the datatype must be a nested table type created in the schema.

As has often been the workaround-of-choice for such cases (though more and more seldom as the PL/SQL parser becomes more and more equal to the SQL parser), I can make it work if I use dynamic SQL instead of static SQL in the PL/SQL:

declare
   type t_rec is record (
      id       number
    , name     varchar2(10)
    , gender   varchar2(1)
   );
   type t_tab is table of t_rec index by binary_integer;
   t  t_tab;
begin
   execute immediate q'[
      select *
        from table(
                delimited_col_row.parser(
                   '1:Adam:M,2:Belinda:F,3:Carl:M'
                 , 'ID:NUMBER,NAME:VARCHAR2(10),GENDER:VARCHAR2(1)'
                 , ':'
                 , ','
                )
             )
       order by gender, name
   ]'
   bulk collect into t;
   for i in t.first..t.last loop
      dbms_output.put_line(t(i).name);
   end loop;
end;
/

Belinda
Adam
Carl

PL/SQL procedure successfully completed.

It works, because dynamic SQL is parsed entirely by the SQL parser at run-time. The PL/SQL parser is bypassed and never realizes that I have executed something that it thinks is invalid 😉

One might argue it's a bug in the PL/SQL parser. Technically I'd say it is, since it ought to handle what the SQL parser can handle. But I think there'd be very little focus on fixing it, as using the ODCI interface in this manner is most likely less and less used in the future, where Polymorphic Table Functions seems to be the future choice for situations like this.

Anyway, if you need it - you now know it takes dynamic SQL 🤓

---

UPDATE 2019-01-17:

A comment suggested using XML on top of the dynamic string with the ODCI call as an alternative to EXECUTE IMMEDIATE - primarily I think to avoid a large collection variable in memory for larger datasets, and to enable filtering unwanted rows and columns.

Well, dynamic SQL can be performed natively in PL/SQL with ref cursor variables too, as another alternative to EXECUTE IMMEDIATE. Which I do believe will be less costly than invoking dbms_xmlgen and xmltable.

Here's one way to do that (note I got an error in SQL Developer 18.2, probably because of the colons, so I used SQL*Plus with SET DEFINE OFF):

declare
   t_cur    sys_refcursor;
   v_id     number;
   v_name   varchar2(10);
   v_gender varchar2(10);
begin
   open t_cur for q'[
      select *
        from table(
                delimited_col_row.parser(
                   '1:Adam:M,2:Belinda:F,3:Carl:M'
                 , 'ID:NUMBER,NAME:VARCHAR2(10),GENDER:VARCHAR2(1)'
                 , ':'
                 , ','
                )
             )
       order by gender, name
   ]';
   loop
      fetch t_cur into v_id, v_name, v_gender;
      exit when t_cur%notfound;
      dbms_output.put_line(v_name);
   end loop;
   close t_cur;
end;
/

Belinda
Adam
Carl

PL/SQL procedure successfully completed.

Here I used a weakly typed ref cursor, opened the cursor for a dynamic string, and then used a standard loop to fetch over the rows returned from the cursor. With this method I am no longer using collection variables that might use up all my server memory. On the other hand I am now doing row-by-row fetching, which also is known as slow-by-slow - this can be remedied by using bulk collect from my cursor variable into a collection, just with a limit of for example a 100 rows - then looping and keep fetching 100 rows at a time until the cursor returns no more rows. I'll leave that as an exercise for the reader - there are plenty of examples of this on the net (search for Steven Feuerstein, for example.)

The other issue about being able to filter columns and rows... Well, the whole point of the ODCI table function is, that the result can be treated as any other table in the SELECT statement. So I can do the filtering directly inside the query where I call the ODCI function. For example I can select just the NAME column for only those rows with GENDER='M' easily:

declare
   t_cur    sys_refcursor;
   v_name   varchar2(10);
begin
   open t_cur for q'[
      select name
        from table(
                delimited_col_row.parser(
                   '1:Adam:M,2:Belinda:F,3:Carl:M'
                 , 'ID:NUMBER,NAME:VARCHAR2(10),GENDER:VARCHAR2(1)'
                 , ':'
                 , ','
                )
             )
       where gender = 'M'
       order by name desc
   ]';
   loop
      fetch t_cur into v_name;
      exit when t_cur%notfound;
      dbms_output.put_line(v_name);
   end loop;
   close t_cur;
end;
/

Carl
Adam

PL/SQL procedure successfully completed.

And if I needed to join the result to any other tables, or perform aggregations, or do anything else that SQL can do - I'd simply do it right there, since the output of the TABLE function is a row source that can be treated by SQL as any other table.

I hope that makes sense :-)