Receiving error when querying Oracle DB from SQL Server 2005

Posted on 2007-12-06
Last Modified: 2013-12-07
When I run the query below, I receive an error stating that Invalid Data for type numeric.  The query is being executed from SQL 2005 against a linked server to an Oracle DB.  If I select just the first record, it returns without issue.  However, when I run it against the entire dataset, I get the error.  From what I've read, this can occur if an integer is stored in a numeric field.  How can I get around this?  I'm not familiar with PLSQL.

select top 10 

cast([LE] as varchar(100))

,cast([DIV] as varchar(100))

,cast([LOCPROG] as varchar(100))

,cast([CC] as varchar(100))

,cast([ACCT] as varchar(100))

,cast([PROJ] as varchar(100))

,cast([PER_YEAR] as varchar(100))

,cast([PER_NUM] as varchar(100))

,cast([ACCT_DESCRIPTION] as varchar(100))

,cast([CSIGN] as varchar(100))

,cast([PERACT] as varchar(100))

,cast([PERIOD_STATUS] as varchar(100)) 

From OPENQUERY(gsiprod,


g.segment1 LE,

g.segment2 DIV,

g.segment3 LOCprog,

g.segment4 CC,

g.segment5 ACCT,

g.segment6 PROJ,

b.period_year Per_Year,

b.period_num Per_Num,

acct.description acct_description,

decode(substr(acct.compiled_value_attributes,5,1),''R'',-1,1) csign,

SUM(NVL(b.period_net_dr,0) - nvl(b.period_net_cr,0)) PerAct,

decode(s.closing_status,''C'',''Closed'',''P'',''Closed'',''Open'') period_status


gl.gl_period_statuses s,

(select * from apps.fnd_flex_values_vl where flex_value_set_id = 1002591) acct,

gl.gl_code_combinations g,

gl.gl_balances b,

report.calendar_445 cal

where 1=1

and s.set_of_books_id = b.set_of_books_id

and s.application_id = 101

and s.period_name = b.period_name

and acct.flex_value (+) = g.segment5

and g.code_combination_id = b.code_combination_id

and b.actual_flag = ''A'' 

and nvl(b.translated_flag,''USD'') = ''USD'' 

and (NVL(b.period_net_dr,0) - nvl(b.period_net_cr,0)) != 0

and g.segment5 between ''30000'' AND ''99999'' --Income Statement Accounts

and b.period_year in (cal.fiscalyear, cal.fiscalyear - 1, cal.fiscalyear - 2)

and cal.actual_date = trunc(sysdate) - 7


g.segment1 ,

g.segment2 ,

g.segment3 ,

g.segment4 ,

g.segment5 ,

g.segment6 ,

b.period_year ,

b.period_num ,


decode(substr(acct.compiled_value_attributes,5,1),''R'',-1,1) ,

decode(s.closing_status,''C'',''Closed'',''P'',''Closed'',''Open'') ')

Open in new window

Question by:lovingatx
LVL 25

Accepted Solution

imitchie earned 500 total points
Comment Utility
Check that the these columns are not NUMERIC, they are being nvl-ed or compared to CHAR types

and b.actual_flag = ''A''
and nvl(b.translated_flag,''USD'') = ''USD''
and g.segment5 between ''30000'' AND ''99999'' --Income Statement Accounts

Author Closing Comment

Comment Utility
Needed to cast all fields within the distributed query

Expert Comment

Comment Utility
This problem seems to be caused by the 64 bit oracle driver. Pretty sure it's okay on 32 bit, but I'm struggling with this in a 64 bit world. Also, this only seems to occur with numeric values that end with a zero. I'm gonna upgrade the driver from Version 10.2 to 11.1 next week to see if the problem has been solved.

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now