5 Replies Latest reply: Aug 20, 2012 3:46 PM by Matperry-Oracle RSS

    ORA-01789 - When using TABLE operator and SEM_MATCH

    scatmull
      Client version: 11.1.0.7 on Windows 7 (32-bit)
      Server version: 11.2.0.3 on Windows 7 (64-bit)

      Hi, I'm prototyping a very simple RDF model and I'm getting an "ORA-01789: query block has incorrect number of result columns" when using the SEM_MATCH with the TABLE operator.

      I can execute this:
      select id, a.triple.GET_TRIPLE() from drik.rdf_data a
      and the first row back is:
      (<http://www.ihc.com/drik/term#2>, <http://www.w3.org/2000/01/rdf-schema#subClassOf>, <http://www.ihc.com/drik/term#1>)
      Now I want to use the TABLE operator like this:
      select
        c
      from 
        table(sem_match('{ <http://www.ihc.com/drik/term#2> <http://www.w3.org/2000/01/rdf-schema#subClassOf> ?c . }', 
              sem_models('Prototype'),
              sem_rulebases('RDFS'),
              sem_aliases(sem_alias('rdfs','http://www.w3.org/2000/01/rdf-schema/')),
              null
              ))
      But executing this gives the ORA-07189. Even trying SELECT * gives the same error.

      I am expecting to get back:
      <http://www.ihc.com/drik/term#1>
      How should I approach troubleshooting this?

      Thanks, Steve
        • 1. Re: ORA-01789 - When using TABLE operator and SEM_MATCH
          Matperry-Oracle
          Hi,

          Can you please post the result of this query executed as SYSDBA:

          SELECT *
          FROM MDSYS.RDF_PARAMETER;

          Thanks,
          Matt
          • 2. Re: ORA-01789 - When using TABLE operator and SEM_MATCH
            scatmull
            set lin 2000
            SELECT *
            FROM MDSYS.RDF_PARAMETER;
            
            
            NAMESPACE                      ATTRIBUTE                      VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            DESCRIPTION                                                                                         
            ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
            COMPONENT                      RDFCTX                         INSTALLED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        Semantic (Text) Search component                                                                    
            COMPONENT                      RDFOLS                         INSTALLED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        RDF Optional component for OLS support                                                              
            COMPONENT                      RDFVPD                         INSTALLED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        RDF Optional component for VPD support                                                              
            MDSYS                          SEM_VERSION                    11203                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            VALID                                                                                               
            
            4 rows selected.
            • 3. Re: ORA-01789 - When using TABLE operator and SEM_MATCH
              Matperry-Oracle
              Everything looks valid from the RDF_PARAMETER table.

              I am unable to reproduce the error on a similar query locally. Can you please post a small script that reproduces the problem, so that I can try to reproduce it locally?

              Thanks,
              Matt
              • 4. Re: ORA-01789 - When using TABLE operator and SEM_MATCH
                scatmull
                CREATE TABLE DRIK.RDF_DATA
                (
                  ID      NUMBER,
                  TRIPLE  MDSYS.SDO_RDF_TRIPLE_S
                )
                COLUMN TRIPLE NOT SUBSTITUTABLE AT ALL LEVELS
                TABLESPACE RDF_USERS
                ;
                
                -- Staging Table
                CREATE TABLE DRIK.RDF_STAGING
                (
                  RDF$STC_SUB        VARCHAR2(4000 BYTE)        NOT NULL,
                  RDF$STC_PRED      VARCHAR2(4000 BYTE)        NOT NULL,
                  RDF$STC_OBJ        VARCHAR2(4000 BYTE)        NOT NULL,
                  RDF$STC_SUB_EXT    VARCHAR2(64 BYTE),
                  RDF$STC_PRED_EXT  VARCHAR2(64 BYTE),
                  RDF$STC_OBJ_EXT    VARCHAR2(64 BYTE),
                  RDF$STC_CANON_EXT  VARCHAR2(64 BYTE)
                )
                TABLESPACE RDF_USERS
                ;
                
                begin  SEM_APIS.CREATE_RDF_MODEL('Prototype', 'RDF_DATA', 'TRIPLE', 'RDF_USERS'); end;
                
                grant insert on rdf_data to mdsys;
                 
                Insert into DRIK.RDF_STAGING
                   (RDF$STC_SUB, RDF$STC_PRED, RDF$STC_OBJ)
                 Values
                   ('<http://www.ihc.com/drik/term#2>', '<http://www.w3.org/2000/01/rdf-schema#subClassOf>', '<http://www.ihc.com/drik/term#1>');
                COMMIT;
                
                begin SEM_APIS.BULK_LOAD_FROM_STAGING_TABLE('Prototype', 'DRIK', 'RDF_STAGING'); end;
                
                select id, a.triple.GET_TRIPLE() from drik.rdf_data a;
                
                begin SEM_APIS.CREATE_RULES_INDEX('PROTOTYPE_RB_IDX', SEM_Models('Prototype'), SEM_Rulebases('RDFS')); end;
                
                select
                  c
                from 
                  table(sem_match('{ <http://www.ihc.com/drik/term#2> <http://www.w3.org/2000/01/rdf-schema#subClassOf> ?c . }', 
                        sem_models('Prototype'),
                        sem_rulebases('RDFS'),
                        sem_aliases(sem_alias('rdfs','http://www.w3.org/2000/01/rdf-schema/')),
                        null
                        ));
                        
                Results in...
                Table created.
                Table created.
                PL/SQL procedure successfully completed.
                Grant complete.
                1 row created.
                Commit complete.
                PL/SQL procedure successfully completed.
                
                        ID A.TRIPLE.GET_TRIPLE()                             
                ---------- --------------------------------------------------
                           (<http://www.ihc.com/drik/term#2>, <http://www.w3.org/2000/01/rdf-schema#subClassOf>, <http://www.ihc.com/drik/term#1>)                               
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                1 row selected.
                PL/SQL procedure successfully completed.
                select
                  c
                from 
                  table(sem_match('{ <http://www.ihc.com/drik/term#2> <http://www.w3.org/2000/01/rdf-schema#subClassOf> ?c . }', 
                        sem_models('Prototype'),
                        sem_rulebases('RDFS'),
                        sem_aliases(sem_alias('rdfs','http://www.w3.org/2000/01/rdf-schema/')),
                        null
                        ))
                          *
                Error at line 9
                ORA-01789: query block has incorrect number of result columns
                • 5. Re: ORA-01789 - When using TABLE operator and SEM_MATCH
                  Matperry-Oracle
                  Thanks for posting the script. I was able to reproduce the error locally.

                  The problem is the use of the sem_apis.create_rules_index() procedure, which has been deprecated:
                  http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_newfeat.htm#autoId26

                  Please use sem_apis.create_entailment() instead, and the error should go away.
                  http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sem_apis_ref.htm#CHEHDGBD

                  Thanks,
                  Matt