PL/SQL sample :
create procedure toto ( n number ) is
n1 number ;
select 1 into n1 from dual where 1 = n ;
And the call to < SqlPlSqlCommand ( mySqlHandle, "toto ( 0 )" ) > returns TRUE.
In fact, I had already this bug with the previous TD 1.5.1 and Oracle 8.1.7.
And the problem is still remaining whith TD 4.2 and Oracle 9.2.0.
Just a though, can you try to force USELOB=1 in you SQL.INI if you do not have this setting already set? and test with both ORACLE functions?
In all case I don't recommend you to have that setting in your SQL.INI gobaly but it is just to check. If it happens to change something in the good side...you might want to programatically set it via SqlSetParameterAll() using DBP_USELOB..as a workaround...
I will check the issue on 5.1
Forcing uselob=1 will force the OCI8 to be used instead of the OCI7 AFAIK...
Before changing programs, can you tell me if there ara some side effects with this kind of solution ?
Ok first with TD 5.1 the issue does not show since we use the OCI8, and the behavior is then the SAME as TD 4.2 when using USELOB=1, also was curious to check USELOB=0 in TD 5.1 but it does not have any effect, this would imply the default is OCI8 and no longer using OCI7. So both with TD 4.2 and TD 5.1 and like you have when using SqlGetError() (Turns off backend error mapping and reports real backend errors.) the ERROR message OCI_NODATA BUT with an ERRR number 100...??? I would have excpect ORA-1403 "No Data Found" error instead....
What you get as a ERROR NUMBER?
when you say
I ask because I'm wondering why we get error 100...might be a reason but I don't know itbut at least with the real error message number
It seems to me in the ROUTER we simply trap that error and display OCI_NODATA like show in code snipet here
http://download.oracle.com/docs/cd/B283 ... i10new.htm
and don't let through the ORA-1403.
Anyway we get the error now and to your question what are the side effect....Well I know if you would globaly turn on USELOB=1 you might probably get some problem withg SP execution crashing either with SqlPLSQLCommand or the SqlORA function not sure anymore and also issue for what I recall with SqlPrepare/Execute (I'm talking about previous TD version not 5.1) ...I often had to tell customer to disable it in previous version and to turn it on programatically when ever LOB are needed. This issue of ERROR not beeing trap is similar to the other issue where inserting larger amounf of data in a column did not return any error! I'm not an oracle specialist but one way arround was to use trigger and RAISE ERROR see post
maybe there this is a better way as using USELOB=1 specifically to workaround that issue
In fact, when you try this proc (toto) in SQL*Plus, SQL*Plus displays ORA-01403.
But when I catch the NO_DATA_FOUND exception in the PL/SQL block, Oracle SQLCODE is 100 : it's the only exception that is treated like that (see the PL/SQL User's guide and reference about handling PL/SQL Errors and the predefined PL/SQL exceptions).
I don't want to workaround that problem, because these errors (in my programs) are not expected : I want to know when it happens (it's a bug) and I don't want to write some EXCEPTION routines in all Oracle packages that can be concerned.
I remember that in older version, in SAM_SqlError, SQLWindows SqlGetError function returned error number 0.
Perhaps a real solution ???
Who is online
Users browsing this forum: [Ccbot] and 3 guests