3 Replies Latest reply: Nov 27, 2012 5:42 PM by Simon Greener RSS

    Error: ORA-13199 following a SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT call

    976317
      Good afternoon all,
      I am new to this group. I tried to search the web for hints and clues to my error, but could not find a solution to my problem, so I decided to post here. I have an existing spatial table in which I recently inserted new values. I am trying to validate them, using the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT function and I am getting errors:

      BEGIN
      SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('ALS.ALS_GEO_SVC_AREAS','GEOLOC','VALIDATION_RESULTS');
      END;
      /

      Error at line 1
      ORA-13199: Invalid GEOMETRY table (or table does not exist)
      ORA-06512: at "MDSYS.MD", line 1723
      ORA-06512: at "MDSYS.MDERR", line 17
      ORA-06512: at "MDSYS.SDO_GEOM", line 2828
      ORA-06512: at line 2

      Script Terminated on line 1.

      The spatial table is in a different schema = 'ALS' and is named 'ALS_GEO_SVC_AREAS'; the geometry column = 'GEOLOC'.

      I created the validation table ('VALIDATION_RESULTS') as such:

      CREATE TABLE VALIDATION_RESULTS(
      sdo_rowid ROWID,
      status VARCHAR2(2000));

      I am using TOAD 9.7.2.5.

      Can someone in the community help with this error?

      Thank you.

      Avridog
        • 1. Re: Error: ORA-13199 following a SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT call
          Simon Greener
          Avridog,

          The only way I can see to get this cross-schema validation to work is via a view.

          Here is my use case.
          DROP  TABLE VALIDATION_RESULTS;
          
          CREATE TABLE VALIDATION_RESULTS(
           sdo_rowid ROWID,
           status VARCHAR2(2000)
          );
          
          select count(*) from CODESYS.GEODPOINT2D;
          
          grant select on geodpoint2d to public with grant option;
          
          create view geodpoint2d as select * from codesys.geodpoint2d;
          
          insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
          select table_name,column_name,diminfo,srid
            from all_sdo_geom_metadata 
           where owner = 'CODESYS' and table_name = 'GEODPOINT2D' and column_name = 'GEOM';
          
          commit;
            
          BEGIN
           SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('GEODPOINT2D','GEOM','VALIDATION_RESULTS');
           END;
           /
          
          Select * from VALIDATION_RESULTS;
          
          SDO_ROWID STATUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          --------- ---------------
          NULL      Rows Processed <500>
          This worked for me (all points passed validation). If it works for you, and you believe your question is answered please award points.

          regards
          Simon
          • 2. Re: Error: ORA-13199 following a SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT call
            976317
            Hi Simon,

            Thanks for the prompt reply.

            I forgot to mention that the validation script was ran by a DBA with full rights to the schema 'ALS', where the spatial table is located. Asides from being an internal error, can you explain what error ORA-13199 means exactly?

            Thanks again.

            Avridog
            • 3. Re: Error: ORA-13199 following a SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT call
              Simon Greener
              Avridog,

              As far as I can see, the problem is inside the VALIDATE_LAYER_WITH_CONTEXT not being able to be run cross-schema.

              You can run this connected as SYS: it still won't work.
              DROP  TABLE VALIDATION_RESULTS;
               
              CREATE TABLE VALIDATION_RESULTS(
               sdo_rowid ROWID,
               status VARCHAR2(2000)
              );
               
              BEGIN
               SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('CODESYS.GEODPOINT2D','GEOM','VALIDATION_RESULTS');
              END;
              / 
               
              SELECT* FROM VALIDATION_RESULTS;
              If an Oracle Spatial developer is lurking on this thread can you confirm what the situation is?

              regards
              Simon