asked on

Oracle cursor syntax

I need to create a cursor for deleting from multiple tables and for each time I loop through the dataset I need to call a stored procedure as well. Here is the sytax I came up with but I dont believe its correct. Any help would be appreciated. Thanks

CREATE OR REPLACE PROCEDURE DeleteParagraph
(iPARAGRAPHID IN number )

IS

cursor c1 is
SELECT PAGE_ID
FROM OMNI_PAGE_CONTENT
WHERE SECTION_ID=iPARAGRAPHID;

BEGIN

FOR page_rec in c1
LOOP
DELETE FROM OMNI_ENT_PAGE_CONTENT WHERE SECTION_ID=iParagraphID and pageid=page_rec;
EXECUTE SP_OMNI_ENT_CONTENT_REALIGN(page_rec);
END LOOP;

DELETE FROM OMNI_ENT_PARAGRAPH WHERE PARA_ID=iParagraphID

END;

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

I would suggest you add exception section handling to your code to trap/log any errors to the log/errors table accordingly. At times and over a period of time, you might get into situations that you do not understand why your procedure is behaving that way for a given record/value and the log/errors in the log/errors table will really help at that time.

Another thing is - i see no commit is there in that code at the end ? Is that a mistake or intentional that you will not commit as part of this procedure call ?

jknj72

ASKER

Im sorry this took so long,