Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Nested tables, Instead Of triggers, Cast and Multiset, with PLS-00201 error

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

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 28 Mar 2004 17:33:38 -0800
Message-ID: <1080523996.575056@yasure>


K Brown wrote:

> I am hoping that someone out there has some experience with INSTEAD OF
> 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

I'm with Ed in that you seem to me doing something simple by a tortured route. And I am loathe to help you since you too seem to think it is appropriate to post to every usenet group you can find with the word Oracle in its name ... but in the interest of being helpful:

http://www.psoug.org/reference/cast.html http://www.psoug.org/reference/instead_of_trigger.html

If you respond please eliminate the two irrelevant usenet groups from your reply.

Thank you.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sun Mar 28 2004 - 19:33:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US