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

Reply from LewisC on Aug 7 at 5:54 PM

Michael,

If it can be done in SQL it should be done in SQL. If it can't be done in SQL, then and only then should you descend into PL/SQL. If there are possible issues with concurrency,Oracle offers excellent concurrency control, including lock table if you really need that level.

System design should make the best usage of the database as well as make the customer happy. Doing what he wanted, including any kind of messaging with lock issues, is easy in Oracle. Hence, my comment stands.

Think in sets, not procedurally. PL/SQL in this case would just complicate matters.

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