[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle cursor syntax

Posted on 2012-09-17
3
Medium Priority
?
393 Views
Last Modified: 2012-10-22
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;
0
Comment
Question by:jknj72
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38407102
One minor change:

...
and pageid=page_rec.page_id;
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38462750
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 ?
0
 

Author Closing Comment

by:jknj72
ID: 38522222
Im sorry this took so long,
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question