OS 7.1 with all PTFs
I have a condition handler in an sql function that returns a warning, but I am having issues trapping the error in the calling stored procedure.
Here is a snippet of the sql function returning the warning.
The joblog shows that the warning is being successfully returned when a record is not found.
Here is what I am *currently* trying in the calling stored procedure; I've tried multiple strategies and permuations.
I've tried various strategies to grab the warning being returned, but I have been unsuccessful.
Help please?
Thanks,
Walt
I have a condition handler in an sql function that returns a warning, but I am having issues trapping the error in the calling stored procedure.
Here is a snippet of the sql function returning the warning.
Code:
create or replace function ediShipmentStatusXref ( pEDIpartnerId varchar(15), pStatus varchar(2), pStatusType varchar(2), pStatusDateType varchar(3) ) returns integer begin ... declare lStatusTypeId integer default null; declare shipment_status_xref_not_found condition for '01H01'; declare exit handler for sqlexception, sqlwarning, not found begin set retCode = sqlCode; set lMsgText = 'No Shipment Status Type Cross Reference'; signal shipment_status_xref_not_found set message_text = lMsgText; return lStatusTypeId; end; select status_type_id into lStatusTypeId from if_edi_shipment_status_xref where *** some criteria ***; return lStatusTypeId; end;
Here is what I am *currently* trying in the calling stored procedure; I've tried multiple strategies and permuations.
Code:
... begin ... declare lStatusTypeId integer default null; declare retCode integer default 0; ... declare exit handler for sqlexception, sqlwarning begin set retCode = sqlcode; get diagnostics exception 1 sqlerrm = message_text; /* record error message to sql message log */ resignal; end; begin -- declare exit handler for shipment_status_xref_not_found declare exit handler for sqlexception, sqlwarning, not found begin set retCode = sqlcode; get diagnostics exception 1 sqlerrm = message_text; /* record error message to sql message log */ resignal; end; select ediShipmentStatusXref(pEDIpartnerId, pStatus, pStatusType, pStatusDateType) into lStatusTypeId from sysibm/sysdummy1; ... end; return retCode; end;
Help please?
Thanks,
Walt
Comment