2 Replies Latest reply on Sep 10, 2010 9:55 PM by Matperry-Oracle

    SEM_MATCH in Cursor causing error, ORA-22905

    796687
      Hello:

      I am trying to create a cursor based upon a SEM_MATCH query, but am stopped by the error message, "ORA-22905: cannot access rows from a non-nested table item", on line-6. Here is the PL-SQL that I'm trying:

      DECLARE
      TYPE T_Cursor IS REF CURSOR;
      semCursor T_Cursor;

      BEGIN
      OPEN semCursor FOR SELECT PC, PS, Perspective
      FROM TABLE(
      SEM_MATCH('{?PC rdfs:subClassOf <http://informatics.visus.jnj.com/PSP#Perspective_Component> .
      ?PC <http://informatics.visus.jnj.com/PSP#hasPerspective> ?PS .
      ?PS <http://informatics.visus.jnj.com/annotation/ano#hasName> ?Perspective .}',
      SEM_Models('VKNT2_ANO', 'VKNT2_PSP'), null, null, null));

      LOOP
      null;
      END LOOP;
      END;

      Right now, I am not doing anything with the cursor. I need to get it working first. Any help would be appreciated. Thanks.
        • 1. Re: SEM_MATCH in Cursor causing error, ORA-22905
          Matperry-Oracle
          Hi,

          The fundamental problem here is that the SEM_MATCH table function returns SYS.ANYTYPE, which means that the return columns of a SEM_MATCH query are not known at parse time. To open a REF CURSOR, the return columns of the query must be known at parse time.

          A workaround, which may or may not be feasible depending on your application, is to explicitly cast the SEM_MATCH result to a pre-defined type. This means that you would basically have to create a type for each query in your application. The type should include all 8 return columns for each variable in your graph pattern. An example is shown below:

          create or replace type
          sMatchType as
          object
          ( x varchar2(4000),
          xVid number,
          xPrefix varchar2(4000),
          xSuffix varchar2(4000),
          xVtype varchar2(3),
          xClob CLOB,
          xLitType varchar2(4000),
          xLitLang varchar2(4000),
          mbox varchar2(4000),
          mboxVid number,
          mboxPrefix varchar2(4000),
          mboxSuffix varchar2(4000),
          mboxVtype varchar2(3),
          mboxClob CLOB,
          mboxLitType varchar2(4000),
          mboxLitLang varchar2(4000),
          name varchar2(4000),
          nameVid number,
          namePrefix varchar2(4000),
          nameSuffix varchar2(4000),
          nameVtype varchar2(3),
          nameClob CLOB,
          nameLitType varchar2(4000),
          nameLitLang varchar2(4000));
          /

          create or replace type sMatchTabType is table of sMatchType;
          /

          declare
          type c_type is REF CURSOR;
          cur c_type;
          begin

          open cur for
          select x,mbox,name from table( cast (sem_match(
          '{?x <mbox> ?mbox OPTIONAL { ?x <name> ?name }
          }',sem_models('m1'),null,null,null) as sMatchTabType ));

          close cur;

          end;
          /
          show errors;

          Hope this helps,
          - Matt

          Edited by: matperry on Sep 9, 2010 2:06 PM
          1 person found this helpful
          • 2. Re: SEM_MATCH in Cursor causing error, ORA-22905
            Matperry-Oracle
            Another, less painful option would be to enclose your SEM_MATCH query with quotes to make it dynamic SQL instead of static SQL. This way, we can avoid the parse time ANYTYPE problems, so you don't need to create those types or do any explicit casting. Please see the example below.

            declare
            type c_type is REF CURSOR;
            cur c_type;
            x varchar2(4000);
            mbox varchar2(4000);
            name varchar2(4000);
            begin

            open cur for
            'select x,mbox,name from table(sem_match(
            ''{?x <mbox> ?mbox OPTIONAL { ?x <name> ?name }
            }'',sem_models(''m1''),null,null,null))';

            loop
            fetch cur into x,mbox,name;
            exit when cur%NOTFOUND;
            dbms_output.put_line('x [' || x || '] mbox [' || mbox || '] [' || name || ']');
            end loop;

            close cur;

            end;
            /
            show errors;

            Thanks,
            Matt