TD4.2 - Oracle exception NO_DATA_FOUND

[dballay]

SQLWindows doesn't throw the Oracle exception no_data_found with SqlPLSQLCommand Or SqlOraPLSQLPrepare + Execute.
PL/SQL sample :
create procedure toto ( n number ) is
n1 number ;
begin
select 1 into n1 from dual where 1 = n ;
end ;

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.

Thanks
David

[Jean-Marc Gemperle]

Hi David,

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...

Thanks

JM

[dballay]

Yes it's better with uselob=1 but the function SqlGetError cannot retrieve the real Oracle message. In place, I've got "Error - OCI_NODATA" , but at least with the real error message number.
Before changing programs, can you tell me if there ara some side effects with this kind of solution ?

Thanks

David

[Jean-Marc Gemperle]

Hi David,

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

but at least with the real error message number

I ask because I'm wondering why we get error 100...might be a reason but I don't know it

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
https://support.guptatechnologies.com/supportforum/viewtopic.php?f=18&t=1290
maybe there this is a better way as using USELOB=1 specifically to workaround that issue

Cheers
JM

[dballay]

The error number reported by SqlGetError was 100.
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 ???

David