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

[K Brown]

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

[Ed prochak]

katrzyna@hotmail.com (K Brown) wrote in message news:<a49c105d.0403260723.499f1ea2@posting.google.com>... 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
[] Thanks! K Brown

Why go thru all this work? You said the production table was "an exact
replica" of the developmental one, so just do an export and an import.
Have you tried that?

HTH,
ed

[Daniel Morgan]

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@x.washington.edu
(replace 'x' with a 'u' to reply)

[K Brown]

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@x.washington.edu> wrote in message news:<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]

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@x.washington.edu> wrote in message news:<1080523996.575056@yasure>...K Brown wrote:I am hoping that someone out there has some experience with INSTEAD OFtriggers, nested-table inserts, and CAST expression using MULTISET.I have a situation where I need to move data from one table withnested tables in a development schema to an exact replica of the tablewith nested tables in a production schema. I am hoping to use our ETLtool (Informatica) to manage this process, however, the ETL tool doesnot support inserts or updates into nested tables. To work aroundthis issue I have created a "De-Nested" view of the nested table withthe ETL tool inserting the data into the view. To do this, I need anINSTEAD OF trigger to fire upon the insert statement, reorganize theinsert for the nested table structure and execute the insert to theappropriate table and nested tables. The following trigger compilesand works, but when there is more than 1 nested record associated witha parent table record, I get a unique constraint issue.CREATE OR REPLACE TRIGGER ioft_insrt_sample_plansINSTEAD OF INSERTON v_sample_plansFOR EACH ROWBEGININSERT 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 asubquery to grab all the records from the nested table associated withany single parent record and cast them as nested record type. Belowis my attempt to code the trigger the way I need it. It looks fine tome (although I am a bit of a novice), but when I try to create thetrigger I get the errors shown after the Create Trigger statement. Ihave confirmed in SQL*Plus that the Insert statement contained withinthe trigger works beautifully by itself but the trigger doesn't seemto 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 documentationthat 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) astt_attributes)); 20* END ioft_insrt_sample_plans; 21 /Warning: Trigger created with compilation errors.SQL> show errorsErrors for TRIGGER IOFT_INSRT_SAMPLE_PLANS:LINE/COL ERROR-------- -----------------------------------------------------------------2/1 PL/SQL: SQL Statement ignored12/8 PLS-00201: identifier 'OPS$DEVLIMS.NAIOT_SAMPLE_PLANS' mustbe declaredThanks!K BrownI'm with Ed in that you seem to me doing something simple by a torturedroute. And I am loathe to help you since you too seem to think it isappropriate to post to every usenet group you can find with the wordOracle in its name ... but in the interest of being helpful:http://www.psoug.org/reference/cast.htmlhttp://www.psoug.org/reference/instead_of_trigger.htmlIf you respond please eliminate the two irrelevant usenet groups fromyour 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@x.washington.edu
(replace 'x' with a 'u' to reply)