SAP (Documents/Screen shots/Complete Solution): RE:[oracle-db-l] Oracle PL/SQL Performance and Updating

Reply from LewisC on Aug 7 at 6:30 PM

One note I failed to mention. If pessimistic locking is really required, select for update allows you to keep it set based but does duplicate the update predicates (probably, you may be able to identify the records in a simple join and just lock more records than will be required on the more complex update).

LewisC

---------------Original Message---------------
From: Hemanth Shankarappa
Sent: Monday, August 06, 2012 11:08 AM
Subject: Oracle PL/SQL Performance and Updating

I'm new to PL/ SQL and have written this function , the intent is to update the column n2FolderHasContents="+" on the table n0Folder based on a criteria which is evident in the SELECT statement. The function itself gets created with no errors, however when I try to call this function it seems to be running forever and the rows don't seem to to get updated. I'm not sure what's wrong with this function. Please help.

CREATE OR REPLACE FUNCTION UpdateFalse

RETURN NUMBER AS

falseCount NUMBER;

folder_row n0Folder%rowtype;

CURSOR myCursor IS
select *
from global.n0Folder F
where F.n2FolderHasContents = '-' and
( 0 < (select count(*)
from global.CRelate R
where F.OBID = R.Left and
((R.Class = 'n1dInc' and R.Class2 != 'n0Folder') or
R.Class = 'AdHocDep' or R.Class = 'Attach'))
OR

0< (select count(*)
from global.CRelate R,
global.n0Folder FF
where R.Class = 'n1dInc' and F.OBID = R.Left and R.Right = FF.OBID and
FF.n2FolderHasContents = '+')) FOR UPDATE OF F.n2FolderHasContents;
BEGIN

OPEN myCursor;
FETCH myCursor INTO folder_row;

if myCursor%notfound then
falseCount:=0;
else

Update n0Folder set n2FolderHasContents='+' where CURRENT OF myCursor;
COMMIT;

end if;

close myCursor;

select count(*) INTO falseCount
from global.n0Folder F
where F.n2FolderHasContents = '-' and
(0 < (select count(*)
from global.CRelate R
where F.OBID = R.Left and
((R.Class = 'n1dInc' and R.Class2 != 'n0Folder') or
R.Class = 'AdHocDep' or R.Class = 'Attach')) or
0< (select count(*)
from global.CRelate R,
global.n0Folder FF
where R.Class = 'n1dInc' and F.OBID = R.Left and R.Right = FF.OBID and
FF.n2FolderHasContents = '+'));

RETURN falseCount;

END ;
/

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback