Help: Stored Procedures Implicit cursors with hold closed
One of our developers has run into the following
situation with stored procedures.
We are attempting to run a foreach loop 'with hold'
to read shipment information.
For each shipment we want all or nothing.
Foreach is declared with hold.
begin and commit happens within the loop.
The problem is that the commit work shuts down the
cursor after the first row.
We have tried nested procedures with the same
result.
Is there a workaround for this or, are we doing
something wrong?
Thanks in advance, Sample code and table structures
below sig.
Environment Solaris 2.4
DSA 7.12UC1X5
--
--
Peter Tashkoff <tashkop@kiwi.co.nz>
Project Consultant/DBA
NZ Kiwifruit Marketing Board Usual
Disclaimers Apply
--------------------------------------------------------------------------
ALL RIGHTS RESERVED
This posting is copyright and may be used only for
the purposes
of professional study or the professional exchange
of information.
Use of this posting by any commercial organisation
for the
purposes of vilifying another is explicitly prohibited
---------------------------------------------------------------------------
drop procedure test;
create procedure test( )
define lv_vl_num like if_transport.vl_num;
set debug file to "test.trace";
foreach Ctest with hold for
select s.vl_num
into lv_vl_num
from tsh_vessvoy s, lo_type l
where s.loadout_type = l.loadtype_code
and s.vl_num < 5010
and (s.confirm_flag = "C" or
( s.status_flag = "SD" and
today >= (s.etd_date +
l.num_days_from_etd)))
begin work;
trace "processing Shipment "||lv_vl_num;
-- do some work here
commit work;
end foreach
end procedure;
execute procedure test( );
Table Structures----------------------------
DBSCHEMA Schema Utility INFORMIX-SQL
Version 7.12.UC1X5
Copyright (C) Informix Software, Inc., 1984-1995
Software Serial Number AAB#J352860
{ TABLE "dba".tsh_vessvoy row size = 82 number
of columns = 23 index size = 9 }
create table "dba".tsh_vessvoy
(
vl_num smallint not null constraint
"dba".n377_308,
voyage_name char(25),
voyage_number char(6),
sc_code char(4),
loadout_type char(1),
lrnz_date date,
etd_date date,
status_flag char(2),
comments char(15),
confirm_flag char(1),
lv_flag char(1),
adams_flag char(1),
restricted_flag char(1),
cc_exempt_flag char(2),
cc_date date,
preverify_status char(1),
ga_status char(1),
part_pallet_exempt char(1),
note_lines smallint,
integrity_flag char(1),
updated_flag char(1),
processlv_flag char(1),
prod_sec_exempt char(1)
);
revoke all on "dba".tsh_vessvoy from "public";
create unique index "dba".xpktsh_vessvo on
"dba".tsh_vessvoy (vl_num);
DBSCHEMA Schema Utility INFORMIX-SQL
Version 7.12.UC1X5
Copyright (C) Informix Software, Inc., 1984-1995
Software Serial Number AAB#J352860
{ TABLE "dba".lo_type row size = 38 number of
columns = 8 index size = 7 }
create table "dba".lo_type
(
loadtype_code char(1) not null constraint
"dba".n304_711,
name char(25),
real_boat_flag char(1),
date_created date,
min_vl_num smallint,
max_vl_num smallint,
loadtype_cat_ind char(1),
num_days_from_etd smallint,
primary key (loadtype_code) constraint
"dba".u156_161
);
revoke all on "dba".lo_type from "public";