ORA-00905 missing keyword

From: Wayne <wayne.r.whitten_at_ssa.gov>
Date: 27 Apr 2006 08:48:15 -0700
Message-ID: <1146152895.145831.249340@g10g2000cwb.googlegroups.com>

I'm trying to use a query analyzer on some pre-existing procedure code and can't get past this message.

"Describe Error: Failed to execute EXPLAIN plan: ORA-00905: missing keyword"

Here's the procedure. Below it are the tables.

/***************************************************************************

   Deletes a row in the activities table by the primary key value.    Also cascade deletes all children and all other associated data.

****************************************************************************/

  PROCEDURE delete_activity
  (
    p_activity_id IN activities.activity_id%TYPE   )
  IS
    CURSOR v_children_activities(p_parent_activity_id activities.activity_id%TYPE) IS

      SELECT activity_id
        FROM activities
       WHERE parent_id = p_parent_activity_id;

  BEGIN
    UPDATE activities

       SET current_child_id = NULL
     WHERE activity_id = p_activity_id;

    FOR rec IN v_children_activities(p_activity_id) LOOP     delete_activity(rec.activity_id);
  END LOOP;   DELETE FROM work_products
   WHERE activity_id = p_activity_id;

  DELETE FROM attachments
   WHERE activity_id = p_activity_id;

  DELETE FROM contacts
   WHERE activity_id = p_activity_id;

  DELETE FROM regulation_lookups
   WHERE activity_id = p_activity_id;

  DELETE FROM activities
   WHERE activity_id = p_activity_id;

  EXCEPTION
    WHEN others THEN

      pips_debug.raise_exception(


        'pips_dml',

       'delete_activity',


        'Unable to delete a row by primary key in the activities table:

'||SQLERRM);
  END; CREATE TABLE "PIPS_OWNER"."ACTIVITIES" (     "ACTIVITY_ID" VARCHAR2(32) NOT NULL PRIMARY KEY REFERENCES PIPS_OWNER.ACTIVITIES(ACTIVITY_ID) REFERENCES PIPS_OWNER.ACTIVITIES(ACTIVITY_ID),

    "MI_CATEGORY_ID"     VARCHAR2(32),
    "PARENT_ID"          VARCHAR2(32),
    "TYPE_ID"            VARCHAR2(32) NOT NULL,
    "LEAD_ORGANIZATION_ID" VARCHAR2(32),
    "NAME_ID"            VARCHAR2(32),
    "ENTERED_BY_USER_ID" VARCHAR2(32),
    "UPDATED_BY_USER_ID" VARCHAR2(32),
    "COMPLEXITY_ID"      VARCHAR2(32),
    "UNITS_OF_TIME_ID"   VARCHAR2(32),
    "PRIORITY_ID"        VARCHAR2(32),
    "STATUS_ID"          VARCHAR2(32) NOT NULL,
    "PRESENTATION_SEQUENCE" DOUBLE PRECISION,
    "NAME"               VARCHAR2(50),
    "DESCRIPTION"        VARCHAR2(4000),
    "RECORDED_DATE"      DATE,
    "UPDATED_DATE"       DATE,
    "START_DATE"         DATE,
    "EXPECTED_COMPLETION_DATE" DATE,
    "BASELINE_COMPLETION_DATE" DATE,
    "ACTUAL_COMPLETION_DATE" DATE,
    "NOTE"               VARCHAR2(4000),
    "EXPECTED_TIME_TO_COMPLETE" DOUBLE PRECISION,
    "SEQUENCE_ORDER"     DOUBLE PRECISION,
    "CURRENT_CHILD_ID"   VARCHAR2(32),
    "ACTION_TYPE_ID"     VARCHAR2(32),
    "MIGRATED_FROM_ARTS_IND" CHAR(1)

)

CREATE TABLE "PIPS_OWNER"."WORK_PRODUCTS" (     "WORK_PRODUCT_ID" VARCHAR2(32) NOT NULL PRIMARY KEY,     "ACTIVITY_ID" VARCHAR2(32) REFERENCES PIPS_OWNER.ACTIVITIES(ACTIVITY_ID),

    "NAME"               VARCHAR2(50),
    "TYPE_ID"            VARCHAR2(32),
    "IS_INPUT"           CHAR(1),
    "IS_OUTPUT"          CHAR(1)

)

CREATE TABLE "PIPS_OWNER"."ATTACHMENTS" (

    "ATTACHMENT_ID"      VARCHAR2(32) NOT NULL PRIMARY KEY,
    "ACTIVITY_ID"        VARCHAR2(32) NOT NULL REFERENCES
PIPS_OWNER.ACTIVITIES(ACTIVITY_ID),
    "PERSON_ID"          VARCHAR2(32) NOT NULL REFERENCES
PIPS_OWNER.PEOPLE(PERSON_ID),
    "CREATE_DATE"        DATE NOT NULL,
    "VALUE"              CLOB NOT NULL

)

CREATE TABLE "PIPS_OWNER"."CONTACTS" (

    "CONTACT_ID"         VARCHAR2(32) NOT NULL PRIMARY KEY,
    "ACTIVITY_ID"        VARCHAR2(32) REFERENCES
PIPS_OWNER.ACTIVITIES(ACTIVITY_ID),
    "PERSON_ID"          VARCHAR2(32) NOT NULL REFERENCES
PIPS_OWNER.PEOPLE(PERSON_ID),
    "ROLE_ID"            VARCHAR2(32) REFERENCES
PIPS_OWNER.ROLES(ROLE_ID),
    "DEPTH"              DOUBLE PRECISION

)

CREATE TABLE "PIPS_OWNER"."REGULATION_LOOKUPS" (

    "REGULATION_LOOKUPS_ID" VARCHAR2(32) NOT NULL PRIMARY KEY,
    "ACTIVITY_ID"        VARCHAR2(32) NOT NULL,
    "LOOKUP_ID"          VARCHAR2(32) NOT NULL

)

Also, what does this do? (found in middle of query)   FOR rec IN v_children_activities(p_activity_id) LOOP     delete_activity(rec.activity_id);
  END LOOP;
I'm assuming this is what performs the cascade delete. I'm just not familiar with using "rec", so resources for learning more on this as well as advice on using it would be appreciated too.

Many thanks.

Wayne Received on Thu Apr 27 2006 - 10:48:15 CDT