Home » Developer & Programmer » Precompilers, OCI & OCCI » Changing the value of SQLCA run time (Oracle 9i on HP-UNIX)
Changing the value of SQLCA run time [message #285792] Wed, 05 December 2007 11:35 Go to next message
ritvikd
Messages: 11
Registered: January 2007
Junior Member
I have a front end application code generated using Coolgen(4GL) in Language C (PRO C).
The code reads each record from table tempA in Cursor then it reads each record from table tempB (Nested LOOP). NOTE: tempA and tempB are not related. Then on certain condition it deletes tempA record. The deletes works fine but then again it reads tempB record and again deletes the tempA record which doesnot exists!

Though oracle won't provide fatal error on this scenario but the front end application is generated in such a way that if it tries to delete already deleted value then it will provide fatal error.

The workaround we thought is of using a trigger before delete on each row whereby we would check the old unique key in the trigger and check one temporary table i.e. temp. if record
doesnot exists on this table for the unique key then this unique key would be inserted in to temporary table temp. This will ensure that the delete would work fine for the first instance. But when again the code will try to delete the entry which has already been deleted then this trigger would check for the record in the temp table and if found would change the SQLCA code to 0. But SQLCA being the rvalue we cannot assign any value to it.

The generate code before executing delete statement has one statement
sqlca.sqlcode=0;


We want to set same statement again but in the trigger so that once delete query is executed then before deletion the trigger will fire and check the value in temp table, if exists then reset the sqlcode=0 so that the front end application won't provide fatal error.

Is there any way we can manipulate this?
Even PRO C code will do.

Thanks,

[Updated on: Wed, 05 December 2007 14:05]

Report message to a moderator

Re: Changing the value of SQLCA run time [message #285794 is a reply to message #285792] Wed, 05 December 2007 11:42 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The workaround we thought is of using a trigger before delete
IMO, a better solution is NOT use temporary tables; which are almost NEVER needed in Oracle.
Previous Topic: error LNK2019: unresolved external symbol _sqlcxt
Next Topic: OCCI - problem to read user defined function return values
Goto Forum:
  


Current Time: Thu Mar 28 08:01:35 CDT 2024