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

Home -> Community -> Usenet -> c.d.o.server -> 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: 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 <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.

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
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 Mon Mar 29 2004 - 10:30:32 CST

Original text of this message

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