2 Replies Latest reply on Dec 17, 2008 11:31 PM by 24743

    Using SEM_MATCH in cursors

    24743
      Hi

      I get "PL/SQL: ORA-22905: cannot access rows from a non-nested table item" if I try something like

      CREATE PROCEDURE p_test
      AS
      CURSOR cur
      IS
      SELECT s, p, o
      FROM TABLE (SEM_MATCH ('{?s ?p ?o}', SEM_MODELS ('test'), NULL, NULL, NULL));
      BEGIN
      NULL;
      END;

      Any hints?

      bw - Christian
        • 1. Re: Using SEM_MATCH in cursors
          alwu-Oracle
          Hi,

          The following is from my scratch pad. It should work. You may want to adjust it to suit your needs.


          create or replace procedure rdf_plsql as
          TYPE cursor_t IS REF CURSOR;
          mycursor cursor_t;

          TYPE rec_t IS record (
          s varchar2(4000),
          s$RDFVTYP varchar2(3),
          p varchar2(4000),
          p$RDFVTYP varchar2(3),
          o varchar2(4000),
          o$RDFVTYP varchar2(3),
          o$RDFCLOB CLOB,
          o$RDFLTYP varchar2(4000),
          o$RDFLANG varchar2(4000)
          );

          rec rec_t;

          prop varchar2(1000);
          BEGIN
          OPEN mycursor FOR
          'select x, x$RDFVTYP, p, p$RDFVTYP, y, y$RDFVTYP, y$RDFCLOB,
          y$RDFLTYP, y$RDFLANG from table(
          SDO_RDF_MATCH(''(?x ?p ?y)'',
          SDO_RDF_Models(''MDB''),
          null,
          null,
          null
          ))';
          LOOP
          FETCH mycursor INTO rec;
          EXIT WHEN mycursor%NOTFOUND;

          DBMS_OUTPUT.PUT_LINE('row ' || rec.s || ' , ' || rec.o);
          END LOOP;
          END;
          /
          show errors;

          Thanks.
          • 2. Re: Using SEM_MATCH in cursors
            24743
            Thank's - it works.

            Maybe it's more intuitive to create a view with dynamic SQL and work with that while processing the data.

            bw - Christian