RE: PL/SQL

Ok, I need a visual. Got a diagram????? :o)

thanks,
Christine Turner
Database Administrator
IPS-Sendero
Scottsdale, Arizona
Phone: (800) 321-6899 ext. 3286
Fax: (480) 946-8224
E-mail: christine.turner_at_ips-sendero.com

-----Original Message-----

From:	Jeff Wiegand [SMTP:jwiegand_at_ancept.com]
Sent:	Monday, July 31, 2000 10:04 AM
To:	Multiple recipients of list ORACLE-L
Subject:	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.

Jeff

-- 
Author: Jeff Wiegand
  INET: jwiegand_at_ancept.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).