Error with Oracle linked server & Date

Question

Post reply

Error with Oracle linked server & Date

linuxchiq

SSC-Addicted

Points: 447
More actions
February 26, 2004 at 11:51 am

#161034

I am trying to use the following code to create a view in a SQL Server db of data in an Oracle db:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.Review
AS
SELECT *
FROM OPENQUERY(LAWSON,'SELECT
   CAST(EMPLOYEE AS Int) AS EMPLOYEE,
   CAST(POS_LEVEL AS Int) AS POS_LEVEL,
   CAST(POSITION AS VarChar(50))AS POSITION,
   CAST(POS_EFFECT_DATE AS DateTime) AS POS_EFFECT_DATE,
   CAST(POS_END_DATE AS DateTime) AS POS_END_DATE,
   CAST(JOB_CODE AS VarChar(50)) AS JOB_CODE,
   CAST(DEPARTMENT AS VarChar(50)) AS DEPARTMENT,
   CAST(SUPERVISOR AS VarChar(50)) AS SUPERVISOR,
   CAST(SUPERVISOR_IND AS VarChar(50)) AS SUPERVISOR_IND,
   CAST(CODE AS VarChar(10)) AS CODE,
   CAST(DUE_DATE AS DateTime) AS DUE_DATE,
   CAST(ACTUAL_DATE AS DateTime) AS ACTUAL_DATE,
   CAST(RATING AS VarChar(10)) AS RATING
FROM STAPLD.REVIEWS')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
And I get the following error:

ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

Which disappears if I remove the DATE columns from the equation.

Anyone have an idea how to make this work?

Cheers!

SELECT * FROM users WHERE clue > 0
0 rows returned.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Andrew McKeon SSC Veteran Points: 281 More actions · Answer 1

Hi,

Oracle uses functions to convert data types rather than cast/convert. If I recall correctly you use

to_date('string', 'format')

have a look at http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html

hope this helps

jaredsimi SSCrazy Points: 2420 More actions · Answer 2

Oracle can use casts to convert dates as well.

oracle doesn't have a datatype called datetime. OpenQuery is executed as a passthru query on the remote server. Thats why you are receiving a ora- error. Try removing the cast specification.

linuxchiq SSC-Addicted Points: 447 More actions · Answer 3

Unfortunately if I don't do the CAST the query runs okay to create the view, but when I try to look at the data I get "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Once I get this sorted out, there is another potential problem in that the system the Oracle data is coming from uses the value '1700-01-01' to indicate something that has not occured - like the end date of a still active employee . . .

SELECT * FROM users WHERE clue > 0
0 rows returned.

jaredsimi SSCrazy Points: 2420 More actions · Answer 4

the issue is that some of the dates coming in from oracle are below the sql server min date (somewhere around 1753)

this query works

SELECT * from OPENQUERY(ORACLE,'SELECT TO_DATE(''1/1/1900'',''MM/DD/YYYY'') FROM DUAL')

this query does not

SELECT * from OPENQUERY(ORACLE,'SELECT TO_DATE(''1/1/1700'',''MM/DD/YYYY'') FROM DUAL')

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Andrew McKeon SSC Veteran Points: 281 More actions · Answer 5

I’ve used Oracle since 7.3 and although it has got more ANSI compliant with 9i I have generally found it best to use the Oracle’s functions rather than trying to use CAST.

I have found that the easiest way to get the a SQL Server query to work correctly when querying a remote Oracle database is to connect to it using sqlplus (the TNS must already be set up for the SQL Server to connect to it), write the query using Oracle syntax (at least that way you know it works on the Oracle database so you only SQL Server to worry about) and paste this as the OPENQUERY SQL:

TO_CHAR(datefield, 'YYYY/MM/DD') (use whatever format you need for the view)

CREATE VIEW dbo.Review

AS

SELECT *

FROM OPENQUERY(LAWSON,'SELECT

CAST(EMPLOYEE AS Int) AS EMPLOYEE,

CAST(POS_LEVEL AS Int) AS POS_LEVEL,

CAST(POSITION AS VarChar(50))AS POSITION,

CAST(POS_EFFECT_DATE AS DateTime) AS POS_EFFECT_DATE,

CAST(POS_END_DATE AS DateTime) AS POS_END_DATE,

CAST(JOB_CODE AS VarChar(50)) AS JOB_CODE,

CAST(DEPARTMENT AS VarChar(50)) AS DEPARTMENT,

CAST(SUPERVISOR AS VarChar(50)) AS SUPERVISOR,

CAST(SUPERVISOR_IND AS VarChar(50)) AS SUPERVISOR_IND,

CAST(CODE AS VarChar(10)) AS CODE,

TO_CHAR(DUE_DATE, 'YYYY/MM/DD') AS DUE_DATE,

TO_CHAR(ACTUAL_DATE, 'YYYY/MM/DD') AS ACTUAL_DATE,

CAST(RATING AS VarChar(10)) AS RATING

FROM STAPLD.REVIEWS')

Hope this helps – I don’t have Oracle installed at work so I can’t be sure the syntax is totally correct!

linuxchiq SSC-Addicted Points: 447 More actions · Answer 6

Thanks for your help, guys

Here's what actually works, the only question now is whether we can do date-arithmetic on chars . . . anyone happen to know?

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER VIEW dbo.Review

AS

SELECT *

FROM OPENQUERY(LAWSON,'SELECT

CAST(EMPLOYEE AS Int) AS EMPLOYEE,

CAST(POS_LEVEL AS Int) AS POS_LEVEL,

CAST(POSITION AS VarChar(50))AS POSITION,

TO_CHAR(POS_EFFECT_DATE, ''YYYY-MM-DD'') AS POS_EFFECT_DATE,

TO_CHAR(POS_END_DATE, ''YYYY-MM-DD'') AS POS_END_DATE,

CAST(JOB_CODE AS VarChar(50)) AS JOB_CODE,

CAST(DEPARTMENT AS VarChar(50)) AS DEPARTMENT,

CAST(SUPERVISOR AS VarChar(50)) AS SUPERVISOR,

CAST(SUPERVISOR_IND AS VarChar(50)) AS SUPERVISOR_IND,

CAST(CODE AS VarChar(10)) AS CODE,

TO_CHAR(DUE_DATE, ''YYYY-MM-DD'') AS DUE_DATE,

TO_CHAR(ACTUAL_DATE, ''YYYY-MM-DD'') AS ACTUAL_DATE,

CAST(RATING AS VarChar(10)) AS RATING

FROM STAPLD.REVIEWS')

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Select * From Review;

SELECT * FROM users WHERE clue > 0
0 rows returned.

jaredsimi SSCrazy Points: 2420 More actions · Answer 7

not unless you convert it back to a date, which won't work for the dates prior to 1753. I supposed you could wrap it in a udf and not support date arithmetic for the invalid dates.