Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> -> 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 <>
Date: Mon, 29 Mar 2004 08:30:32 -0800
Message-ID: <1080577811.254477@yasure>

K Brown wrote:

> 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 <> 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
>>>ON v_sample_plans
>>>INSERT INTO sample_plans
>>>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
>>> 3 ON v_sample_plans
>>> 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,
>>> 16 from ops$devlims.naiot_sample_plans T1, table(obj_attributes)
>>> 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
>>> 20* END ioft_insrt_sample_plans;
>>> 21 /
>>>Warning: Trigger created with compilation errors.
>>>SQL> show errors
>>>-------- -----------------------------------------------------------------
>>>2/1 PL/SQL: SQL Statement ignored
>>>12/8 PLS-00201: identifier 'OPS$DEVLIMS.NAIOT_SAMPLE_PLANS' must
>>> declared
>>>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:
>>If you respond please eliminate the two irrelevant usenet groups from
>>your reply.
>>Thank you.

You use it in a trigger just as anywhere else. My suspicion is that your version of Oracle, unmentioned of course, doesn't support it in PL/SQL so you need to wrap its use in native dynamic SQL.

Daniel Morgan
(replace 'x' with a 'u' to reply)
Received on Mon Mar 29 2004 - 10:30:32 CST

Original text of this message