Openquery: Problem with Oracle datatypes
-
I'm using SQL 2008 R2 and Oracle 10g. I have created a linked server (OraOLEDB.Oracle) to an Oracle database. I have a table in my Oracle database:
TableA
DateCol (datatype date)
Col1 (NUMBER)
Col2 (NUMBER)
I've created this view in a my SQL database:
select * from Openquery([MyOracleDatabase],'select DateCol, Col1,Col2)
My problem is that with the exception of the date, all of the datatypes in my view are nvarchar(384).
Does anyone know how to control how the datatypes are being set in my view?
Thank you,
Mike
-
Michael.Gagne (11/10/2011)
I'm using SQL 2008 R2 and Oracle 10g. I have created a linked server (OraOLEDB.Oracle) to an Oracle database. I have a table in my Oracle database:TableA
DateCol (datatype date)
Col1 (NUMBER)
Col2 (NUMBER)
I've created this view in a my SQL database:
select * from Openquery([MyOracleDatabase],'select DateCol, Col1,Col2)
My problem is that with the exception of the date, all of the datatypes in my view are nvarchar(384).
Does anyone know how to control how the datatypes are being set in my view?
Does it happens to "all other datatypes" or just to NUMBER datatypes with no precision and scale specified on the Oracle side?
I understand that Oracle NUMBER datatypes with no precision and scale are mapped as nvarchar(384).
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
I realize I might have missed something in my post, so let me try to clarify.
I created a view:
create view myView as
select * from Openquery([MyOracleDatabase],'select DateCol, Col1,Col2 from myOracleTable)
Eventually, I want to use this view as a data source for a cube from which I'll create some measures or use it in a SSRS report.
The Oracle table that I'm working with contains one DATE type column and the rest are NUMBER datatypes.
The DATE field converts accurately to datetime but NUMBER seems to convert to nvarchar(384).
Is there a something I can do to in the openquery statement or an option somewhere that will convert the datatype to someting other than nvarchar(384)?
I don't seem to be able to use:
select dateCol, CAST(Col1 as float), CAST(Col2 as float) from Openquery([MyOracleDatabase],'select DateCol, Col1,Col2)
The only thing I've been able to do is to create ANOTHER view:
create view myOtherView as
select dateCol, CAST(Col1 as float), CAST(Col2 as float) from myView.
Do I have any other options?
-
Long shot? Try this syntax on your openquery...
select * from Openquery([MyOracleDatabase],'select DateCol, to_number(to_char(Col1)) "COL1",to_number(to_char(Col2)) "COL2" from myOracleTable)'
Above mentioned syntax works fine when issuing the select statement directly to Oracle.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Hi there - thanks for the suggestion SSCrazy but the column is still converted to nvarchar(384).
-
Michael.Gagne (11/17/2011)
Hi there - thanks for the suggestion SSCrazy but the column is still converted to nvarchar(384).I would try adding a mask to to_number function, like...
select * from Openquery([MyOracleDatabase],'select DateCol, to_number(to_char(Col1),'99999') "COL1",to_number(to_char(Col2),'99999') "COL2" from myOracleTable)'
...or whatever mask fits the number you are expecting.
Still no warranties - long shot
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply