I'm running into an issue working in LINQ to SQL to Oracle code.
Systems:
Oracle10g
MS SQL Server 2008 64 bit
Background:
I have several tables in oracle that I would like to use on my ASP page. I Figure that a LINQ to SQL would be the easiest/most efficient way of going about this. (We already have Link Servers set up for the Oracle Database on our MS2008 SQLServer).
Basically: ORACLE -> MS SQL -> LINQ -> ASP
I went ahead and created an oracle VIEW that pulls all the data that I want. Something like this:
CREATE OR REPLACE FORCE VIEW "WCADMIN"."V_METRICS" ("DOC_ID", "DOC_NAME", "DOC_NUMBER", "DOC_TITLE", "DOC_DESC", "DOC_TYPE", "ORG_NAME", "LIB_NAME", "LIB_DESC", "DOC_STATE", "DOC_REVIEWDATE", "DAYS_BETWEEN", "DOC_REV") AS
select DOC_ID, DOC_NAME, DOC_NUMBER, DOC_TITLE, DOC_DESC, DOC_TYPE,
ORG_NAME, LIB_NAME, LIB_DESC, DOC_STATE, DOC_REVIEWDATE, CAST(MONTHS_BETWEEN(to_date(DOC_REVIEWDATE, 'MM-DD-YYYY'), to_date(SYSDATE, 'DD-MON-YY'))*31 as Integer) as DAYS_BETWEEN, DOC_REV
from
(select distinct
org.NAMECONTAINERINFO "ORG_NAME",
A3.namecontainerInfo "LIB_NAME",
A3.DESCRIPTIONCONTAINERINFO "LIB_DESC",
CONCAT('wt.doc.WTDocument:',A4.IDA2A2) "DOC_ID",
DM.NAME "DOC_NAME",
DM.wtdocumentnumber "DOC_NUMBER",
A4.TITLE "DOC_TITLE",
A4.DESCRIPTION "DOC_DESC",
SUBSTR(T2.NAME,INSTR(T2.NAME,'.',-1)+1) "DOC_CATEGORY",
SUBSTR(T1.NAME,INSTR(T1.NAME,'.',-1)+1) "DOC_TYPE",
to_char(SOA.VALUE,'MM-DD-YYYY') "DOC_REVIEWDATE",
A4.STATESTATE "DOC_STATE",
A4.VERSIONIDA2VERSIONINFO "DOC_REV",
DECODE(A4.STATECHECKOUTINFO,'c/o',1,0) "ISCHECKDOUT"
from
wcadmin.orgcontainer ORG,
wcadmin.wtlibrary A3,
wcadmin.WTDocument A4,
wcadmin.wtdocumentMaster DM,
wcadmin.WtTypeDefinition T1,
wcadmin.WtTypeDefinition T2,
wcadmin.TIMESTAMPVALUE SOA,
wcadmin.TIMESTAMPDEFINITION SOB
where
ORG.ida2a2 = A3.IDA3CONTAINERREFERENCE and
A3.ida2a2 = DM.ida3containerreference and
DM.ida2a2 = A4.ida3masterReference and
A4.LATESTITERATIONINFO = 1 and
A4.STATESTATE <> 'OBSOLETE' and
A4.STATESTATE <> 'HISTORICAL' and
A4.IDA2TYPEDEFINITIONREFERENCE = T1.IDA2A2 and
T1.IDA3PARENTREFERENCE = T2.IDA2A2 and
T2.NAME = 'com' and
A4.IDA2A2 = SOA.IDA3A4 (+) and
SOA.HIERARCHYIDA6 = SOB.HIERARCHYID and
SOB.NAME = 'RequiredReviewDate' and
A4.STATECHECKOUTINFO IN ('c/i','c/o')
)
where ORG_NAME = 'SOME ORG' AND
DOC_REVIEWDATE != '00-00-0000' and
MONTHS_BETWEEN(to_date(DOC_REVIEWDATE, 'MM-DD-YYYY'), to_date(SYSDATE, 'DD-MON-YY'))*31 < 31;
All is well with this View, all data is pulling correctly. But, when I create a SQL view to copy this data to our MS Server Server, the view was created. But, when I try to view it, I get the following error:
OLE DB provider "OraOLEDB.Oracle" for linked server "wind" returned message "ORA-01861: literal does not match format string".
OLE DB provider "OraOLEDB.Oracle" for linked server "wind" returned message "ORA-01861: literal does not match format string".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "select DOC_ID, DOC_NAME, DOC_NUMBER, DOC_TITLE, DOC_DESC, DOC_TYPE, ORG_NAME, LIB_NAME, LIB_DESC, DOC_STATE, DOC_REVIEWDATE, DAYS_BETWEEN, DOC_REV
from schema.v_METRICS" against OLE DB provider "OraOLEDB.Oracle" for linked server "wind".
Here's the code:
CREATE VIEW [dbo].[vExample]
AS
SELECT TOP (100) PERCENT DOC_ID, DOC_NAME, DOC_NUMBER, DOC_TITLE, DOC_DESC, DOC_TYPE, ORG_NAME, LIB_NAME, LIB_DESC, DOC_STATE, DOC_REVIEWDATE, CAST(DAYS_BETWEEN AS INT) AS DAYS_BETWEEN, DOC_REV
FROM OPENQUERY(wind,
'select DOC_ID, DOC_NAME, DOC_NUMBER, DOC_TITLE, DOC_DESC, DOC_TYPE, ORG_NAME, LIB_NAME, LIB_DESC, DOC_STATE, DOC_REVIEWDATE, DAYS_BETWEEN, DOC_REV
from schema.v_METRICS')
AS derivedtbl_1
I tried removing all references to date/time/sysdate references to get the barbone, for testing, but returned the same error:
CREATE VIEW [dbo].[v_example]
AS
SELECT TOP (100) PERCENT DOC_ID
FROM OPENQUERY(wind,
'select DOC_ID
from schema.v_METRICS')
AS derivedtbl_1
Does anyone know why oracle likes the literal/string and MS SQL Server does not?
THANKS!
Edited by: user12184405 on Nov 7, 2009 8:20 AM