This discussion is archived
4 Replies Latest reply: May 22, 2013 6:35 AM by 904690 RSS

table or view does not exist ORA-06512: at "MDSYS.SDO_GCDR"

904690 Newbie
Currently Being Moderated
Hi All,

When am executing following sql am getting below error

SELECT SDO_GCDR.GEOCODE_ALL('MDSYS',SDO_KEYWORDARRAY('Clay St', 'San Francisco, CA'),'US', 'DEFAULT') FROM DUAL;

java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception:
oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view does not exist
ORA-06512: at "MDSYS.SDO_GCDR", line 643
ORA-06512: at "MDSYS.SDO_GCDR", line 774
ORA-06512: at line 1

Not sure if i had missed anything. Have imported world_sample2010.dmp NAVTEQ data.

Request you to guide me on resolving this issue. Pls let me know if any other info. is required.

Thanks in Advance.

-Rakesh
  • 1. Re: table or view does not exist ORA-06512: at "MDSYS.SDO_GCDR"
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Rakesh,

    I suspect that you are getting ORA-00942 because you gave MDSYS as the table owner. The [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_geocode_ref.htm#CACDDBCB]documentation for SDO_GCDR.GEOCODE_ALL says that the first parameter must contain the
    Name of the user that owns the tables containing the geocoding data.
    I don't think your geocoding data is stored in MDSYS. If it is, I would strongly suggest to move it out of MDSYS (you should not need to put stuff in there, it's there for Oracle) and into a schema of your own. But you should enter the username/schemaname which owns the table your data is in.

    If that does not solve your problem, execute the same query in SQL Plus (and not through a java interface), and show the command en results here, so we may see what happens. You are probably running into some sort of privilege issue in that case.

    HTH,
    Stefan
  • 2. Re: table or view does not exist ORA-06512: at "MDSYS.SDO_GCDR"
    904690 Newbie
    Currently Being Moderated
    Hi Stefan,

    Thanks for the reply.

    We have imported WORLD_SAMPLE NAVTEQ Data and as per your suggestion I have changed the query to point to correct user "NAVTEQ_UTIL" in our case.

    SELECT SDO_GCDR.GEOCODE_ALL('NAVTEQ_UTIL',SDO_KEYWORDARRAY('Clay St', 'San Francisco, CA'),'US', 'DEFAULT') FROM DUAL;

    But still its giving the same error.

    I have observed one thing in our NAVTEQ_UTIL schema i don't see following tables:
    GC_PARSER_PROFILES
    GC_PARSER_PROFILEAFS
    GC_AREA_<suffix>
    GC_POSTAL_CODE_<suffix>
    GC_ROAD_SEGMENT_<suffix>
    GC_ROAD_<suffix>
    GC_POI_<suffix>
    GC_INTERSECTION_<suffix>

    The only table i see is "GC_COUNTRY_PROFILE".
    Do you think this has any relation to the error?

    Please let me know your inputs.

    Thanks,
    Rakesh
  • 3. Re: table or view does not exist ORA-06512: at "MDSYS.SDO_GCDR"
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Rakesh,
    Stefan Jager wrote:
    execute the same query in SQL Plus (and not through a java interface), and show the command en results here
    I only see your query, and no error. Or did you use the java interface again? By executing it through SQL Plus, we can eliminate this problem being caused by something in your java-environment, that's what I'd try to do (eliminate as many variables as possible, thus narrowing where to search for the problem).

    But:
    Rakesh wrote:
    GC_PARSER_PROFILES
    GC_PARSER_PROFILEAFS
    GC_AREA_<suffix>
    GC_POSTAL_CODE_<suffix>
    GC_ROAD_SEGMENT_<suffix>
    GC_ROAD_<suffix>
    GC_POI_<suffix>
    GC_INTERSECTION_<suffix>

    The only table i see is "GC_COUNTRY_PROFILE".
    Do you think this has any relation to the error?
    Yes it does. Why don't you start with the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_geocode_concepts.htm#CIHECECC]documentation? Sections 11.5 and 11.6 in particular describe what to set up and how. Since you are using Java, [url http://download.oracle.com/otndocs/products/spatial/pdf/spatial11gr2_geocoder_twp.pdf]this whitepaper is also a very good place to start since it describes how to set up the J2EE Geocoder stuff.

    Geocoding is not something that happens out of the box, you usually need to do some things before it works. It's not easy to do, especially not if you have to support all of the millions of ways addresses can be described all over the world. For example, this is how a typical address in the Netherlands would be given:
    <streetname> <housenumber> 
    <postal code> <city>
    Now look at a US address:
    <housenumber> <streetname> <streettype>
    <city>, <State Abbreviation> <postal code>
    Even though these are two fairly similar countries in this respect, for machines to read this can be really confusing. So you need to tell them exactly what to expect and where to expect it, and that usually takes some setting up.

    HTH,
    Stefan
  • 4. Re: table or view does not exist ORA-06512: at "MDSYS.SDO_GCDR"
    904690 Newbie
    Currently Being Moderated
    Hi Stefan,

    we are trying to just execute a plain sql query.

    For map/spatial data we have imported world_sample2010.dmp file from [Spatial data|http://www.oracle.com/technology/products/spatial/htdocs/spatial_partners_data.html] which do not seem to have geo code data.

    Is this the right .dmp we are using or do we need to download from some other source.

    Not sure on how to get GEOCODE data.

    Thanks,
    Rakesh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points