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_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.
Date: Mon, 29 Mar 2004 08:30:32 -0800
Message-ID: <1080577811.254477_at_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 - 18:30:32 CEST