I have a SQL Server 12.0 database that is linked to an Oracle 12.1 database.
I want to create a view in the SQL Server database that returns data from an Oracle table filtered by date. The Oracle table has an index on the date column.
A query that works successfully is:
select * from ORADB..SCHEMA.MYTABLE where MYDATE >= '20140701';
However this runs very slowly. I assume it is because the comparison is taking place in SQL Server so every row is being returned.
If I go:
DECLARE @earliest date = '20140701';
select * from ORADB..SCHEMA.MYTABLE where MYDATE >= @earliest;
Then it runs fast, presumably because the condition is being passed to Oracle so the Oracle index on the table is being used.
My problem is that I want to create a view. I can't find a way of using the second version of the code to create a view. If I simply do:
create myview as select * from ORADB..SCHEMA.MYTABLE where MYDATE >= '20140701';
Then it runs slowly.
Is there another format for the date literal that SQL Server will pass to Oracle, or is there another solution? I wondered also if it was to do with the parameters used in creating the link to Oracle. For reference they are:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORADB', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'DPDB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORADB',@useself=N'False',@locallogin=NULL,@rmtuser=N'MYUSER',@rmtpassword='#######'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
EDIT: I just found a very similar question: Forcing a SQL Remote Query to filter remotely instead of locally
Just use: select to_date(date_value, 'yyyy-mm-dd') as date_value from table; To convert to a date. That's it!
I prefer the ODBC format:
--DateTime
SELECT {ts'2015-09-20 12:30:00'}
--Time (however this comes with "today"-time)
SELECT {t'12:30:00'}
--Date
SELECT {d'2015-09-20'}
GO
The simple date literal is not culture independent...
SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATETIME);--ERROR: there's no month "13"
GO
But it works - however - with target type DATE
(this difference is rather weird...):
SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATE);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATE);--ERROR: there's no month "13"
GO
Thx to lad2025 I want to add for completness the "full" ISO 8601, which works fine:
SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);
GO
If the query is running on Oracle database, then I suggest use the ANSI date literal which uses a fixed Format YYYY-MM-DD.
For example,
DATE '2015-10-20'
In Oracle, '20140701'
is a string and not a DATE. You might just be lucky to see an implicit data type conversion and get the result based on the locale-specific NLS settings of your client. You should always avoid it, and explicitly convert the string into date for date comparisons.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With