HI,
I'm still trying to fetch datas in a recursive function. Waiting for your responses
to my latest email (that follows), I've tried changing the structure of my algorithm.
Now I use a function that is called in an update statement like:
UPDATE PSF.ACTIVITY_RELATIONSHIP set C_TREE_PATH = FIX_TREE_D(E_ACT_K_ACTIVITY1,
E_ACT_K_ACTIVITY2, '')
WHERE E_ACT_K_ACTIVITY1 = 7 AND E_ACT_K_ACTIVITY2 = 8
The function is declared as follow:
DROP FUNCTION FIX_TREE_D
//
CREATE FUNCTION FIX_TREE_D( father fixed, child fixed, prefix CHAR)
RETURNS VARCHAR
AS
VAR C_TREE_PATH_D VARCHAR(1000); Appo fixed(9,0); treeN VARCHAR(1000);
BEGIN
IF prefix = '' OR prefix is null THEN
set C_TREE_PATH_D = '/' || father || '/' || child || '/'
ELSE
set C_TREE_PATH_D = prefix || child || '/';
/* Mi occupo dei figli */
UPDATE PSF.ACTIVITY_RELATIONSHIP
set C_TREE_PATH = FIX_TREE_D(:Appo, E_ACT_K_ACTIVITY2, ltrim(rtrim (:C_TREE_PATH_D)))
WHERE E_ACT_K_ACTIVITY1 = :Appo;
RETURN C_TREE_PATH_D;
END;
//
At compiling time, I receive the following error: General error;-915 POS(375) No more
memory:CAT_CACHE_SUPPLY.
I read that I ought to change the database parameter CAT_CACHE_SUPPLY so that
(MAXUSERTASKS +1)*_CAT_CACHE_MINSIZE <= CAT_CACHE_SUPPLY. So I did, but nothing
changes.
What's wrong?
Is possible to create recursive function accessing tables directly or using cursors?
Or recursive functions are not allowed to?
Thanks,
Matteo
----- Original Message -----
From: "Matteo Gattoni" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 21, 2004 7:23 PM
Subject: Fetch from Dynamic cursor
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
