Solved TD-3920: SQL Error when select data from oracle table.

Post found bugs and possible workarounds.
Sunil

TD-3920: SQL Error when select data from oracle table.

Post by Sunil » 17 Oct 2007, 20:08

SqlImmediate given SQL error ' Fetching non-numeric character into number variable'.



You can reproduce this error like this.



- Create variable number : x

- select any column ( datatype should be varchar2) from oracle table into :x



This will give SQL Error.

Jean-Marc Gemperle

Re: TD-3920: SQL Error when select data from oracle table.

Post by Jean-Marc Gemperle » 18 Oct 2007, 10:33

Hi Sunil,



I will try to reproduce the problem, I assume that you are using COMA instead of a DOT for DECIMAL separation in your regional settings, right? Can you tell me what is your NLS_LANG setting in the registry and the NLS stetting of the database. Often it was mentioned that the use of SqlImmediate() is not recommended and I will not argue on that since it is provided in the product... but as you know SqlImmediate uses an internal SQLHANDLE...and thus you don't have control on its setting...Generally this error can be resolved by an ALTER SESSION SET NLS... the issue I believe is a difference of the NLS between the client and the database.



If you are using SqlPrepareAndExecute() do you have the same issue?



Thanks

Jean-Marc

Sunil

Re: TD-3920: SQL Error when select data from oracle table.

Post by Sunil » 18 Oct 2007, 14:31

I had checked, I am using DOT . Gupta2005.1 compiled application is working on this PC.



I also saw invalid Rowid while saving data in Oracle Table. I am testing

application running perfectly on 41.



NLS_LANG in registry having NA value.

arvindram11
United States of America
Posts: 555
Joined: 14 Apr 2019, 23:42
Location: Richmond, VA

Re: TD-3920: SQL Error when select data from oracle table.

Post by arvindram11 » 18 Oct 2007, 15:46

Sunil wrote:I had checked, I am using DOT . Gupta2005.1 compiled application is working on this PC.

I also saw invalid Rowid while saving data in Oracle Table. I am testing
application running perfectly on 41.

NLS_LANG in registry having NA value.




We have the same issue. The new Oracle native router seems to be unforgiving. The below statement works fine until the 4.2 version:



"Select Binder_Exp_Mo from System_Default into :gnBinder_Exp_Mo".



The above fails on TD 5.1. The Binder_Exp_No is defined as varchar2(2) in the table (for storing 01, 02, 03....,11,12.



So we had to change this (in TD 5.1) to:



"select to_number(Binder_Exp_Mo) from System_Default into :gnBinder_Exp_Mo".

Silvester

Re: TD-3920: SQL Error when select data from oracle table.

Post by Silvester » 19 Oct 2007, 07:45

Hi,

I got the error above also in older Team Developer versions when connecting to Oracle 10g Express.

Changing comma and decimal with

SqlPrepareAndExecute( hSql, "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'")

solved the problem for me.

Maybe this helps.



Kind Regards

Silvester

Sunil

Re: TD-3920: SQL Error when select data from oracle table.

Post by Sunil » 19 Oct 2007, 10:53

Hi Silverstor,



Problem after adding set NLS_NUMERIC_CHARACTERS='.,' is not solved.



Even i am getting funny ROWID.



Sunil

Jean-Marc Gemperle

Re: TD-3920: SQL Error when select data from oracle table.

Post by Jean-Marc Gemperle » 22 Oct 2007, 14:30

Hi,



NLS does not seem to play any role here. As mentioned on TD 4.2 the problem does not show with both NATIVE ORA ROUTER(SqlImmediate/SqlPrepareAndExecute) and OLEDB ORA provider (SqlPrepareAndExecute) . With TD 5.1 and NATIVE ROUTER the problem shows for both SqlImmediate/SqlPrepareAndExecute BUT DOES NOT when using OLEDB...It seem TD 5.1 router does not cast any longer the result from a VARCHAR2 into a number.



thus here is the bug



TD-3920



and agree with the comment "The new Oracle native router seems to be unforgiving"



Thanks

Jean-Marc

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests