Monday, January 14, 2019

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 :-)

6 comments:

  1. Hello Kim,

    I remember of course your previous post, as there are so, so very few examples for using the ODCITable functions ... so your post was a "piece of cake", as usual :)

    Just out of curiosity, trying to run your first PL/SQL anonymous block, with the static SELECT
    in LiveSQL does work :)
    It is not clear to me whether it is a matter of the Oracle version or just a side effect
    of the LiveSQL environment, which "relies" on using dynamic SQL anyway, behind the scenes.

    It also works using a procedure or a view.

    But, in PL/SQL, since you should define a record type to hold the results, part of the
    "dynamic-ness" of the "parser" function is lost anyway.

    Cheers & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Hi, Iudith

      I think I'll assume it is because LiveSQL is doing stuff dynamically, since LiveSQL is DB version 18.3 and I get an error when I run it on my 18.3 virtualbox developer image.

      And yes, you are absolutely right that using it in PL/SQL kind of defeats the purpose of the parser function here. But on the other hand, if you think of the ODCITable function as something similar to using the XMLTable or JSONTable functions, it is OK that in one specific PL/SQL usage you specify the columns *for that usage* in the call and at the same time specify a matching record type (or if it had worked, relied on the implicit record type of the FOR loop). Then in another PL/SQL usage specified *other* columns and matching type. So the "dynamicness" is more that it is a "tool" function that dynamically adapts to parameters given - not that each individual usage must handle dynamic output.

      Cheerio
      /Kim

      Delete
  2. Hello,

    I think there is another option of using ODCI Table functions in PLSQL Code: dbms_xmlgen.getxml.
    It is more convenient than EXECUTE IMMEDIATE, especially when you have a big dataset returned. You can convert the result of dbms_xmlgen.getxml to XML and and apply XMLTABLE over it to extract/use only the data you need from /ROWSET/ROW pairs. And all can be done in a single query.
    It is a very costly chain of operations but it works as a proof of concept.
    Code:
    --
    WITH xml_clob AS(
    SELECT dbms_xmlgen.getxml(q'[put the SQL Query of the ODCI table function here]') xmltyp
    FROM dual
    ),
    xml_data AS (
    SELECT XMLTYPE(xc.xmltyp) xmltyp
    FROM xml_clob xc
    )
    SELECT extracted.column_name_x
    FROM xml_data,
    XMLTABLE( '/ROWSET/ROW'
    PASSING xml_data.xmltyp
    COLUMNS column_name_x NUMBER PATH 'put your path here'
    --you can query as many columns you want from the dataset.
    ) extracted
    --

    HTH,
    MarianC

    ReplyDelete
    Replies
    1. Hi, Marian

      Thanks for the idea - yes, that should work. Though as you point out, quite costly.

      I'm not quite buying the idea that using XML should be more convenient than EXECUTE IMMEDIATE? Granted, for big datasets returned, my simple example of bulk collecting into a collection variable will need a lot of memory - which can be very inconvenient ;-)

      But EXECUTE IMMEDIATE is not the only way to do dynamic SQL natively in PL/SQL - you can also use ref cursor variables. I'm going to update the post with an example of this - then there would be no big memory usage issues.

      Cheerio
      /Kim

      Delete
  3. I have comma delimited data in a CLOB field and I am have issues getting it to work, could you provide a sample of how this would look. (SQL Newbie)

    "Craig", "Joshnson", "49", "Kenosha"
    "Kim", "Joseph", "55", "Melrose"
    "Robert", "Bernhardt", "36", "Middleton"

    ReplyDelete
    Replies
    1. With my parser function, it would be something like this:


      with craig_tab as (
      select
      '"Craig", "Joshnson", "49", "Kenosha"
      "Kim", "Joseph", "55", "Melrose"
      "Robert", "Bernhardt", "36", "Middleton"'
      as craig_clob
      from dual
      )
      select parsed_tab.*
      from craig_tab,
      table(
      delimited_col_row.parser(
      craig_clob
      , 'FIRST_NAME,VARCHAR2(20)
      LAST_NAME,VARCHAR2(20)
      AGE,VARCHAR2(10)
      TOWN,VARCHAR2(20)'
      , ','
      , chr(10)
      )
      ) parsed_tab;


      But it is not a good solution for this, as my function does not handle if there are commas inside of the quoted fields. It also cannot handle if your newlines are CR-LF instead of just LF. It also doesn't remove the quotes.

      If you are on database version 12.2 or newer (might also work on 12.1.0.2.0), you could transform the comma delimited data to JSON like this:


      with craig_tab as (
      select
      '"Craig", "Joshnson", "49", "Kenosha"
      "Kim", "Joseph", "55", "Melrose"
      "Robert", "Bernhardt", "36", "Middleton"'
      as craig_clob
      from dual
      )
      select craig_json.*
      from craig_tab, json_table(
      '[['||replace(craig_clob, '"'||chr(10)||'"', '"],["')||']]'
      , '$[*]'
      columns (
      first_name varchar2(20) path '$[0]'
      , last_name varchar2(20) path '$[1]'
      , age number path '$[2]'
      , town varchar2(20) path '$[3]'
      )
      ) craig_json;


      If you are using CR-LF instead of just LF, then just use chr(13)||chr(10) instead of just chr(10).

      I hope that helps you to get further. I recommend using the JSON method for your specific type of data. Otherwise you might get inspiration from my presentation that I gave you the link to in my reply to your other comment ;-)

      Cheerio
      /Kim

      Delete