Nested tables, Instead Of triggers, Cast and Multiset, with PLS-00201 error

From: K Brown <katrzyna_at_hotmail.com>
Date: 26 Mar 2004 07:23:54 -0800
Message-ID: <a49c105d.0403260723.499f1ea2_at_posting.google.com>

[Quoted] [Quoted] I am hoping that someone out there has some experience with INSTEAD OF [Quoted] [Quoted] triggers, nested-table inserts, and CAST expression using MULTISET.

I have a situation where I need to move data from one table with nested tables in a development schema to an exact replica of the table with nested tables in a production schema. I am hoping to use our ETL tool (Informatica) to manage this process, however, the ETL tool does not support inserts or updates into nested tables. To work around this issue I have created a "De-Nested" view of the nested table with the ETL tool inserting the data into the view. To do this, I need an INSTEAD OF trigger to fire upon the insert statement, reorganize the insert for the nested table structure and execute the insert to the appropriate table and nested tables. The following trigger compiles and works, but when there is more than 1 nested record associated with a parent table record, I get a unique constraint issue.

CREATE OR REPLACE TRIGGER ioft_insrt_sample_plans INSTEAD OF INSERT
ON v_sample_plans
FOR EACH ROW BEGIN
INSERT INTO sample_plans
(sample_plan_id,
sample_plan_version,
sample_plan_generation,
attributes)
VALUES
(:new.sample_plan_id,
:new.sample_plan_version,
:new.sample_plan_generation,

tt_attributes(ty_attributes(:new.class,:new.sequence,:new.hidden)));

END ioft_insrt_sample_plans;

My insert statement really needs to use the CAST expression with a subquery to grab all the records from the nested table associated with any single parent record and cast them as nested record type. Below is my attempt to code the trigger the way I need it. It looks fine to me (although I am a bit of a novice), but when I try to create the trigger I get the errors shown after the Create Trigger statement. I have confirmed in SQL*Plus that the Insert statement contained within the trigger works beautifully by itself but the trigger doesn't seem to like it. If anyone has any ideas why the trigger will not compile, please post them. I am at a loss. I cannot find any documentation that says the CAST is incompatible with triggers. Please help!

1 CREATE OR REPLACE TRIGGER ioft_insrt_sample_plans   2 INSTEAD OF INSERT
  3 ON v_sample_plans
  4 FOR EACH ROW
  5 BEGIN
  6 INSERT INTO sample_plans
  7 (sample_plan_id,
  8 sample_plan_version,
  9 sample_plan_generation,
 10 attributes)
 11 VALUES
 12 (:new.sample_plan_id,
 13 :new.sample_plan_version,
 14 :new.sample_plan_generation,
 15 cast(multiset(select t2.class, t2.attribute.sequence, t2.attribute.hidden
 16 from ops$devlims.naiot_sample_plans T1, table(obj_attributes) T2
 17 where t1.sample_plan_id = :new.sample_plan_id  18 and t1.sample_plan_version = :new.sample_plan_version  19 and t1.sample_plan_generation = :new.sample_plan_generation) as tt_attributes));
 20* END ioft_insrt_sample_plans;
 21 /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER IOFT_INSRT_SAMPLE_PLANS:

LINE/COL ERROR

-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
12/8     PLS-00201: identifier 'OPS$DEVLIMS.NAIOT_SAMPLE_PLANS' must
be
         declared

Thanks!
K Brown Received on Fri Mar 26 2004 - 16:23:54 CET