HI,
the problem 'General error;-915 POS(375) No more memory:CAT_CACHE_SUPPLY.' is due to
the recursive call of the function.
Deleting it from the body of the function, in this way
DROP FUNCTION FIX_TREE_D
//
CREATE FUNCTION FIX_TREE_D( father fixed(9,0), 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 || '/';
Appo = child;
/* Mi occupo dei figli */
UPDATE PSF.ACTIVITY_RELATIONSHIP
set C_TREE_PATH = ''
WHERE E_ACT_K_ACTIVITY1 = :Appo;
RETURN C_TREE_PATH_D;
END;
//
I'm able to compile the function.
But the function, however, doesn't do what it has to. Calling it in this way,
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 system seems to be waiting endesly for something; I think that the first update
locks the table, so that the update in the function isn't able to act properly and
waits for the unlocking by the first one.
The table used is created in the following way
CREATE TABLE ACTIVITY_RELATIONSHIP
(E_ACT_K_ACTIVITY1 NUMBER(9,0) NOT NULL
,E_ACT_K_ACTIVITY2 NUMBER(9,0) NOT NULL
,E_REL_K_RELATIONSHIP_TYPE NUMBER(9,0)
,D_START DATE NOT NULL
,O_MODIFY DATE NOT NULL
,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
,C_NOTE VARCHAR2(250)
,D_END DATE
,C_TREE_PATH VARCHAR2 (4000)
)TABLESPACE TBS_PSF_DATI
and contains the following records.
INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
E_REL_K_RELATIONSHIP_TYPE,
D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END ) VALUES (
2, 7, 1, TO_Date( '06/08/2004 12:43:36 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date(
'06/08/2004 12:43:36 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1', NULL, '/1/2/7/', NULL)
//
INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
E_REL_K_RELATIONSHIP_TYPE,
D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END ) VALUES (
7, 8, 1, TO_Date( '06/08/2004 12:44:32 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date(
'06/08/2004 12:44:32 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1', NULL, '/1/2/7/8/', NULL)
//
INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
E_REL_K_RELATIONSHIP_TYPE,
D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END ) VALUES (
1, 2, 1, TO_Date( '06/08/2004 12:44:49 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date(
'06/08/2004 12:44:49 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1', NULL, '/1/2/', NULL)
//
INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
E_REL_K_RELATIONSHIP_TYPE,
D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END ) VALUES (
7, 4, 1, TO_Date( '06/08/2004 12:45:07 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date(
'06/08/2004 12:45:07 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1', NULL, '/1/2/7/4/', NULL)
//
INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
E_REL_K_RELATIONSHIP_TYPE,
D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END ) VALUES (
5, 6, 1, TO_Date( '06/11/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date(
'06/11/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1', NULL, '/5/6/', NULL)
//
My hypothesis fail making a simplify example of the algorithm.
The table used is
DROP TABLE PIPPOZ
//
CREATE TABLE PIPPOZ
(Prova NUMBER(9,0),
valo NUMBER(9,0))
//
containing the following two elements
INSERT INTO PIPPOZ (Prova,valo) values (1,1)
//
INSERT INTO PIPPOZ (Prova,valo) values (2,2)
//
I create the function
drop function PIPPOS
//
create function PIPPOS(zuppa NUMBER) RETURNS NUMBER
AS
VAR zuppaI NUMBER(9,0);
BEGIN
zuppaI = zuppa;
UPDATE PPORTAL.PIPPOZ set valo = :zuppaI+1 where Prova = 2;
RETURN zuppaI;
END;
and call it in this way
UPDATE PIPPOZ SET VALO = PIPPOS(4) WHERE Prova = 1
In this example everything goes as excepted.
What's wrong with the first function (FIX_TREE_D)? Why does it seem that the locking
is different between the first and the example?
However, I need to call recursevly the function; so the problem with Cat_cache_supply
(reported in the previous email here following attached) still remains.
At the same way, I still have problem fecthing dynamic cursors (as reported in the
other mail here attached). Having more information about that could solve everything
for me.
Thank in advance.
Bye,
Matteo
----- Original Message -----
From: "Matteo Gattoni" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 22, 2004 5:17 PM
Subject: CAT_CACHE_SUPPLY in Recursive function
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
