Hi,
I'm using Dynamic cursor declared in the following way:
stmt = 'DECLARE Children' || child || ' CURSOR FOR SELECT E_ACT_K_ACTIVITY2 from
PSF.ACTIVITY_RELATIONSHIP '
|| ' WHERE E_ACT_K_ACTIVITY1 = ' || child;
EXECUTE stmt;
When I try to fetch it using
stmt = 'FETCH Childrens' || child || ' INTO :n_child';
EXECUTE stmt;
the variable n_child is always null; even if the select isn't empty.
I've tried to declare the cursor in a different way:
$CURSOR = 'Children' || child;
DECLARE :$CURSOR CURSOR FOR SELECT E_ACT_K_ACTIVITY2 from PSF.ACTIVITY_RELATIONSHIP
WHERE E_ACT_K_ACTIVITY1 = :child;
and using FETCH :$CURSOR INTO :n_child;
But I'm not able to compile the procedure because MaxDb says General error;-5006
POS(1223) Missing identifier.
Finally, I've tried like that:
$CURSOR = 'Children' || child;
stmt = 'DECLARE ' || :$CURSOR || ' CURSOR FOR SELECT E_ACT_K_ACTIVITY2 from
PSF.ACTIVITY_RELATIONSHIP
WHERE E_ACT_K_ACTIVITY1 = :child';
EXECUTE stmt;
But it says General error;-7045 POS(1084) Parameter spec not allowed during
compilation.
How could I create cursors with different name depending on a variable? And how could
I fetch them?
I need this purpose because I have a recursive procedure that every time create a
cursor, using different name I hope that it wouldn't rewrite the old istance.
Thanks in advance.
Bye,
Matteo