Home » SQL & PL/SQL » SQL & PL/SQL » Error message with no column name - does not match expected data length for column '[Xyz.Oracle].'''
Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572060] Wed, 05 December 2012 12:49 Go to next message
entropus
Messages: 3
Registered: December 2012
Junior Member
Hey guys,

So I have a bit of a problem with an OPENQUERY to an Oracle database.

I use code:

(...) select
* FROM OPENQUERY(ORA2,
'SELECT TO_NUMBER(ID_XYZ) ID_XYZ,
ID_XYZ,
NVL(ID_XYZ,0) ID_XYZ,
cast (''0'' as number(5)) as AA2,
cast ('' '' as varchar(20)) as AQA
(...)

and get an error message:

OLE DB provider 'Xyz.Oracle' for linked server 'Xyz' returned data that does not match expected data length for column '[Xyz.Oracle].'''. The (maximum) expected data length is 64, while the returned data length is 2.

The problem is, there is no field name here:

length for column '[Xyz.Oracle].'''

What could be the reason for this error?

I would be extremely grateful for help!
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572062 is a reply to message #572060] Wed, 05 December 2012 13:01 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

try running SELECT directly on Oracle DB to validate it is correct & return set is as expected.
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572063 is a reply to message #572062] Wed, 05 December 2012 13:16 Go to previous messageGo to next message
entropus
Messages: 3
Registered: December 2012
Junior Member
BlackSwan, thanks a lot for the reply! Sorry for the mashup, it's just gettin real late... :/
Select does not return a dataset when run from Oracle DB, there is and error:

Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.


First, there is an insert into a table:

insert into #tmpXYZ (
		[XYZ ID],
		[XZY ID2],
		[XZY ID2]
	)


Then, there is the select part of the code:


select 
* FROM OPENQUERY(ORA2, 
'SELECT TO_NUMBER(ID_XYZ) ID_XYZ,
ID_XYZ,
NVL(ID_XYZ,0) ID_XYZ,
cast (''0'' as number(5)) as AA2,
cast ('' '' as varchar(20)) as AQA



When running the code, I get the message that does not show which column seems to be the problem.


Earlier, I run the 'select' part of the code without casts, like this:

select 
* FROM OPENQUERY(ORA2, 
'SELECT TO_NUMBER(ID_XYZ) ID_XYZ,
ID_XYZ,
NVL(ID_XYZ,0) ID_XYZ,
''0'' AA2,
'' '' AQA



And error message was like this:

OLE DB provider 'Xyz.Oracle' for linked server 'Xyz' returned data that does not match expected data length for column '[Xyz.Oracle].'AA2''. The (maximum) expected data length is 64, while the returned data length is 2.

The, each time I added a cast to a column, the error showed the next one.
For example, after executing code:

select 
* FROM OPENQUERY(ORA2, 
'SELECT TO_NUMBER(ID_XYZ) ID_XYZ,
ID_XYZ,
NVL(ID_XYZ,0) ID_XYZ,
cast (''0'' as number(5)) as AA2,
'' '' AQA


I got an error message:

OLE DB provider 'Xyz.Oracle' for linked server 'Xyz' returned data that does not match expected data length for column '[Xyz.Oracle].'AQA''. The (maximum) expected data length is 64, while the returned data length is 2.
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572065 is a reply to message #572063] Wed, 05 December 2012 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
I assume that you are initiating this "code" from MS SQL Server. Am I correct?

first you need to determine is the root cause on the MS SQLServer or with Oracle.
try running SELECT directly on Oracle DB to validate it is correct & return set is as expected.

can you successfully run any SELECT against Oracle DB from MS side like below?
SELECT COUNT(*) FROM USER_OBJECTS
or
SELECT SYSDATE FROM DUAL;


Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572074 is a reply to message #572063] Wed, 05 December 2012 18:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
entropus wrote on Wed, 05 December 2012 14:16
First, there is an insert into a table:
Then, there is the select part of the code:


Something doesn't add up here. Your temp table insert has 3 columns [XYZ ID],[XZY ID2],[XZY ID2] (and two last columns have same name which I assume is just a typo) while openquery has 5 columns.

SY.
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572087 is a reply to message #572063] Thu, 06 December 2012 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572163 is a reply to message #572087] Thu, 06 December 2012 11:29 Go to previous messageGo to next message
entropus
Messages: 3
Registered: December 2012
Junior Member
Thanks a lot for the input guys. I searched the forum a bit, so as not to ask for answers already posted.
The code i use now looks different.
Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite long.
I believe the problem lies in quotation marks, etc... Sad

The procedure compiles all right.
Each time I execute the procedure, an error occurs:

Quote:
Msg 105, Level 15, State 1, Line 53
Unclosed quotation mark after the character string 'SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1,
cast(''''1753-01-01'''' as date) NULL_DATE



I am lost - where the heck should I put those missing quotation mark?


Code:


CREATE TABLE #tmpXYZ Header (
	[XYZ_1] [int] PRIMARY KEY,
	[XYZ_2] [varchar](20),
	[XYZ_3] [varchar](20),
		-- more fields	
	[XYZ_N] [varchar](50)
 )

declare @sqlInv nvarchar(3000)
 set @sqlInv = 

        'insert into #tmpXYZ Header (
		[XYZ_1],
		[XYZ_2],
		[XYZ_3],
		-- more fields
		[XYZ_N]

	)

	select
		* FROM OPENQUERY(XYZ_ORACLE, 
		''SELECT TO_NUMBER(XYZ_1) XYZ_1,
		XYZ_2,

		cast (''''0'''' as number(5)) as B1, 
		cast(''''1753-01-01'''' as date) NULL_DATE,
		-- more fields
		cast ('''' '''' as varchar(20)) as A19 

	from XYZ.V_HEADER 
	where
        
        (DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND 
        DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND 
        QWE = ''''0'''' AND
		ABC = ''''13'''' ) '' )
	' 
			
	exec sp_executesql @sqlInv
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572164 is a reply to message #572163] Thu, 06 December 2012 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
post complete results from SQL below

SELECT * FROM V$VERSION;
Re: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].''' [message #572165 is a reply to message #572163] Thu, 06 December 2012 11:35 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is an Oracle forum, your code is NOT an Oracle code, so we can't help to debug it.
Find a more appropriate forum to debug it.

Regards
Michel
Previous Topic: SQL Error: ORA-01861: literal does not match format string
Next Topic: Update same column with different values
Goto Forum:
  


Current Time: Wed Oct 22 01:00:09 CDT 2014

Total time taken to generate the page: 0.11005 seconds