PL/SQL

Hey There Everyone ... I know this is long, so delete at will.

Background:

I've been trying to accomplish what I thought would be a small task. Maybe it still is, I don't know. We have a learning and training Java program that connects to an Oracle database. There is a Curriculum hierarchy: Curriculum, Course, Lesson, Activity. We maintain "status" at the activity-level, in a user_activity table. The client would like to see status on an activities parent. So that if all activities for a lesson were completed, the lesson (the activities parent)would get flagged as complete. When all the lessons for a course were completed, the course would get flagged as complete.

Problem:

I cannot accomplish a recursive loop that will go get a parent (held in another table), do an insert of the parent into parent status table, then go find the parent of that parent do an insert, so on and so forth, until we reach the highest level parent. Upon which PL/SQL exits or returns.

The most simple solution would be when an activity is added to the statuses table, the parent is added by a trigger, which fires again and gets that parents parent, until we reach the highest level, then it exits. But I get a mutating table (what fun).

Does anyone have example(s) of some recursive loops in PL/SQL? Any help would be appreciated. Thanks. Received on Mon Jul 31 2000 - 10:45:17 CDT