Access Data from Oracle

Question

Access Data from Oracle

mathewspsimon

SSCommitted

Points: 1706
More actions
July 20, 2009 at 7:28 am

#136370

Hi I have the following issue when I try to access data from Oracle Database.
SELECT * FROM OPENQUERY(CIDEV,'
SELECT PREVIOUS_NUMBER, GIVEN_NAME, MIDDLE_NAME, EMAIL_ADDRESS, MEDICALLICENSEHAAD
FROM XXHR_EMP_DETAILS_INTEGRATION_V
Where
GIVEN_NAME = ''Sandro''')
When I run the query I get the last two columsn as null
whereas the same query in Oracle works fine.Can some help me please.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

mathewspsimon (7/20/2009)
Hi I have the following issue when I try to access data from Oracle Database.
SELECT * FROM OPENQUERY(CIDEV,'
SELECT PREVIOUS_NUMBER, GIVEN_NAME, MIDDLE_NAME, EMAIL_ADDRESS, MEDICALLICENSEHAAD
FROM XXHR_EMP_DETAILS_INTEGRATION_V
Where
GIVEN_NAME = ''Sandro''')
When I run the query I get the last two columsn as null
whereas the same query in Oracle works fine.Can some help me please.

NULL is a perfectly reasonable thing to return, it doesn't mean something is broken. What does it return in Oracle?

Also

GIVEN_NAME = ''Sandro'' is not valid.. you're doubling up on your tick marks here

I believe GIVEN_NAME = 'Sandro' is what that should be?

What you have is basically

GIVEN_NAME = ''

Sandro''

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

mathewspsimon SSCommitted Points: 1706 More actions · Answer 2

The qoutes in Sandro is because I am executing the query in SQL Server and I guess that is the syntax for that.Also the same query when I run in Oracle I get the values and not null.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 3

mathewspsimon (7/20/2009)
The qoutes in Sandro is because I am executing the query in SQL Server and I guess that is the syntax for that.Also the same query when I run in Oracle I get the values and not null.

Are you accessing an Oracle database from a SQL connection?

Or is this the same database on 2 different platforms / machines?

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

mathewspsimon SSCommitted Points: 1706 More actions · Answer 4

I am accessing Oracle from SQL Server.And both are on different machines.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 5

ok my first question would be why are you crossing platforms? Perhaps there is a better approach you can take.

What datatype are the two columns within Oracle that you're having trouble with.. in fact.. can you drop the create table script for the oracle table here?

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

mathewspsimon SSCommitted Points: 1706 More actions · Answer 6

I have a ERP which is running on Oracle and a retail application which is in SQL Server.Oracle has the Employee information and I have created view which I am accessing as select statement.I am unable to understand why the datatype will be an Issue.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 7

mathewspsimon (7/20/2009)
I have a ERP which is running on Oracle and a retail application which is in SQL Server.Oracle has the Employee information and I have created view which I am accessing as select statement.I am unable to understand why the datatype will be an Issue.

Crossing database platforms is never fun, and rarely a good idea in my experience. If its just employee information, can you not create an employee table in your SQL server and copy the data there? Maybe have a daily process which updates it from Oracle? If it was an inventory thing, I'd be able to understand the need for real-time data, but employee data shouldn't change on a daily basis within most business models.

Oracle is not SQL Server. SQL Server is not Oracle. They have different datatypes.. of course, if you dont want to tell me what they are, thats fine. Just means I can't help any further.

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

mathewspsimon SSCommitted Points: 1706 More actions · Answer 8

No,I am not able to understand why datatype will be a issue in a select statement.2)this column is of varchar type.Every other column I am able to retrieve info.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 9

Would it not have been easier to just say its a varchar field, the same as all the rest I'm using, rather than dragging the question out across multiple posts?

I asked because it COULD have been Oracle 8.x-specific data types, such as CLOB, BLOB, BFILE, NCHAR, NCLOB, and NVARCHAR2 which I do not believe are supported.

Do you see any thing wrong with my suggestion to copy the data from Oracle to the SQL Server? It should make life a lot easier for you.

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

mathewspsimon SSCommitted Points: 1706 More actions · Answer 10

Hi torpkev,

I found what was the issue.the view was using an oracle function userenv('LANG') to get the language of the current session in oracle.since we have one US English,so instead we hard coded as we are not using any other language and the issue was resolved.