select from d1, d2, d3

Sorry

Serge,

Many thanks for your explanation, I was wrongly assuming the result set
was 'protected' once the cursor was opened.

This explains exactly what I am seeing.

Unfortunately, I don't understand the construct you are using with the
CTEs ?
 - Do you have a pointer I can go off and read?

What the procedure is doing is using 3 Regular Tables as  "temp tables"
rather than using SESSION tables.
and deleting from these temp tables at the end of the Procedure i.e
self cleaning.
  - we could look at a scheduled garbaage collection instead as a
simple solution.

PS. The Procedure currently uses SESSION tables, but this incurs a
(signiifcant)compile overhead on the first
      call after connection, and seems to require multiple plans in
the Package cache 1 per connection.
      This is a very big/complex Procedure that is required to execute
sub-second, so we are always looking
      for ways to optimise it.

Thanks.

I tried to repoduce Paul Reddin's problem, but am unable to (See below)

I fail to understand why the behaviour is different.

Any clues ?

Sathyaram

create table sptest(i int,j int)

0 record(s) affected

insert into sptest values(1,1),(2,2),(3,3),(4,4)

4 record(s) affected

CREATE PROCEDURE PROC_SPTEST (in p_i int)
result sets 1
language sql
begin
begin
declare c1 cursor with return for select i,j from sptest ;
open c1 ;
end ;
delete from sptest where i=p_i ;
end

0 record(s) affected

select * from sptest

I     J
----  ----
1     1
2     2
3     3
4     4

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

call proc_sptest(1)

I     J
----  ----
1     1
2     2
3     3
4     4

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

select * from sptest

I     J
----  ----
2     2
3     3
4     4

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 10/ms]