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

Thanks for the replies.

Ed is correct the tables that we will be inserting to and updating are exact replicas of each other in structure. However, there is a significant amount of value manipulation that needs to happen between development and production. Anyway, there is a lot of context I would like to provide as to the overall objective of our project, however, corporate confidentiality policies prevent me from doing that. With that said, I have one minor issue with the trigger in my original post.

That issue is as follows:

In my create trigger statement I am trying use the CAST expression with the MULTISET option. This insert works perfectly as a stand-alone command in SQL*Plus and in an anonymous block of PL/SQL code. My issue is that the create trigger fails when I try to run it.  Daniel's links, while helpful, do not illustrate the use of CAST(MULTISET(<subquery>)) in the context of an INSTEAD OF trigger. This leaves a couple of questions:

1. Can CAST(MULTISET(<subquery>)) be used in an INSTEAD OF trigger?
2. If so, is there an issue with my syntax that I am not seeing?
3. Regardless of whether this seems to be a tortured route, is there anyone out there that can rise to the challenge and make this work?

Again, thanks for your help and patience. I realize that there seem to be simpler solutions that apply here, but I would think that a 20 (or so) line trigger could be easily debugged by the experts at this site. I will be extremely appreciative to the person that shows me the error of my ways without the "tough love" approach.

And I'd be quite happy to post to the appropriate group, providing someone can tell me which one that might be. As an admitted novice, I'm not entirely clear to which the group this question should be directed.

K Brown

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1080523996.575056_at_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.
Received on Mon Mar 29 2004 - 09:11:46 CST