Clicky

I try to query Oracle Spatial Database using Navicat. There is this oci preference where you cant set the path where Navicat can find the oci.dll it will use. If I use oci.dll that comes with instant client downloaded from the Oracles website I can succesfully query my spatial database like
SELECT
SDO_GCDR.GEOCODE_AS_GEOMETRY ...
and the result is what it should be.

If I try to use Navicat with the oci.dll that is installed as part of the ODAC also downloaded form the Oracle website the result of the same query will be NULL !

What is the differences in these instant clients and why there even need to be a difference and if there is no difference what then makes the difference?

asked 06/04/2011 09:17

Pipperman's gravatar image

Pipperman ♦♦


6 Answers:
I believe the instant client package is meant for apps that use 3rd-party drivers/interfaces like ruby-oci for Ruby and DBI/DBD-Oracle for Perl while ODAC is meant to be a complete package that provides native connectivity to Oracle for the languages it supports.

Most likely your ODAC just requires further configuration or maybe is a failed installation, or your issue might be related to this: http://forums.oracle.com/forums/thread.jspa?threadID=850597&tstart=150
link

answered

johanntagle's gravatar image

johanntagle

I guess you use different versions of the instant client. It should be appropriate and certified for the version of Oracle DB you use.
Pay also attention to the 32 and 64 bit versions of the instant client.

By the way what exactly means "the result of the same query will be NULL "? Could you elaborate?
link

answered 2011-06-04 at 22:13:08

schwertner's gravatar image

schwertner

Ok. Maby I was not clear enough. The case is.
I downloaded and installed the 32bit Windows version of the
ODAC 11.2 Release 3 (11.2.0.2.1) with Oracle Developer Tools for Visual Studio which includes the
Oracle Instant Client 11.2.0.2.0

http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

And in Navicat you can set the path where it searches the oci.dll to use. I set it to point to the location where the oci.dll was placed during the ODAC installation.

If i then tried to query my Spatially enabled Oracle DB like this
1:
2:
3:
4:
5:
6:
SELECT SDO_GCDR.GEOCODE_AS_GEOMETRY ( 
'SPATIAL',                   -- Spatial schema storing the geocoder data 
SDO_KEYWORDARRAY             -- Object combining different address components 
( '3746  CONNECTICUT AVE NW', 'WASHINGTON, DC 20008' ), 
'US'                         -- Name of the country ) 
geom FROM DUAL ;


the result was NULL.

Then I tried it another way. I downloaded only the 32 bit Windows
http://www.oracle.com/technetwork/topics/winsoft-085727.html
Version 11.2.0.2.0 Instant Client Package -
Basic: All files required to run OCI, OCCI, and JDBC-OCI applications

I just saved the downloaded files to my filesystem and changed the Navicats oci paht to point this new location. And after this I did the exact same query to my database and it worked like a charm.

As far as I understand both Instant Client pakages are the same. Just that the other one works and the other don't.
link

answered 2011-06-08 at 07:51:39

Pipperman's gravatar image

Pipperman

You possibly connect different Data bases.
To check the database use as SYS:

SELECT host_name, instance_name FROM v$instance;
link

answered 2011-06-08 at 13:30:23

schwertner's gravatar image

schwertner

Thank you folks for your answers.

I am not sure what was the problem after all. Now the query I previously posted is working just fine no mather do I make the query with 32 or 64 bit client or is the OS runnin the client 32 or 64 bit. After all I thought that the problem might have been in the instace of my Oracle installation. Meaning that the instance was no runing properly while I was last time trying to query against it. So at the moment everything works well.
link

answered 2011-06-10 at 10:10:33

Pipperman's gravatar image

Pipperman

Well the reason for the problem was not that clear after all so there was no clear solution. Thats why the "solution" is not so usefull.

link

answered 2011-07-14 at 02:51:09

Pipperman's gravatar image

Pipperman

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×6
×1
×51
×1

Asked: 06/04/2011 09:17

Seen: 469 times

Last updated: 08/28/2011 09:16