OraFAQ Forum: SQL & PL/SQL » trigger has :new is a collection type

Home » SQL & PL/SQL » SQL & PL/SQL » trigger has :new is a collection type (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trigger has :new is a collection type [message #409224]

Sat, 20 June 2009 13:42

khuatdinh
Messages: 2
Registered: June 2009

Junior Member

My trouble is trigger have :new is a collection type. I can't take its value.
This is my example: the error in the trigger is at sentence: "INSERT INTO TABLE (select v.a FROM myexample v) VALUES (:new.val,:new.VT,:new.TT) ;
". :new.VT is a collection. Is there another solution, can you help me? Thank you very much for all of yours attentions.

My pl/sql:

create or replace TYPE period_typ as object
(
sta TIMESTAMP,
en TIMESTAMP);/

create or replace TYPE ELE_TYP AS VARRAY(5) OF PERIOD_TYP;/

create or replace TYPE INT_BI_PRE_TYP AS OBJECT
(
val int,
vt ELE_TYP,
tt PERIOD_TYP
);/

create or replace TYPE INT_BI_TYP AS TABLE OF INT_BI_PRE_TYP;/

create table myexample (v int,a INT_BI_TYP)
nested table a store as myexample_a;/

insert into myexample values(1,INT_BI_TYP(
INT_BI_PRE_TYP('1000',ELE_TYP(
PERIOD_TYP(SYSDATE-1,SYSDATE),
PERIOD_TYP(SYSDATE-1,SYSDATE)
),
PERIOD_TYP(SYSDATE-1,SYSDATE)

),
INT_BI_PRE_TYP('1000',ELE_TYP(
PERIOD_TYP(SYSDATE-1,SYSDATE),
PERIOD_TYP(SYSDATE-1,SYSDATE)
),
PERIOD_TYP(SYSDATE-1,SYSDATE)

)
)
);/

create view myexample_view as select * from myexample;/

select * from table(select v.a FROM myexample_view v where v=1);/

create or replace TRIGGER thunghiem_view1_tri INSTEAD OF INSERT
ON NESTED TABLE a OF myexample_view FOR EACH ROW
declare
VTtam ELE_TYP;

BEGIN
--VTtam:=ELE_TYP();

INSERT INTO TABLE (select v.a FROM myexample v) VALUES (:new.val,:new.VT,:new.TT) ;
END;

insert into table(select v.a FROM myexample_view v where v=1) values
(
INT_BI_PRE_TYP('3000',ELE_TYP(
PERIOD_TYP(SYSDATE-1,SYSDATE),
PERIOD_TYP(SYSDATE-1,SYSDATE)
),
PERIOD_TYP(SYSDATE-1,SYSDATE)

)
) ;

Report message to a moderator

Re: trigger has :new is a collection type [message #409225 is a reply to message #409224]

Sat, 20 June 2009 14:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I can't take its value.
This is 100% un-helpful.

We don't know exactly what you did or how Oracle responded.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Report message to a moderator

Re: trigger has :new is a collection type [message #409240 is a reply to message #409225]

Sun, 21 June 2009 00:54

khuatdinh
Messages: 2
Registered: June 2009

Junior Member

Sorry for my inconvenience.

thank you very much for your attendtion and your remind!

If I write the trigger:
create or replace TRIGGER myexample_view_tri INSTEAD OF INSERT
ON NESTED TABLE a OF myexample_view FOR EACH ROW
declare
VTtam ELE_TYP;

BEGIN
--VTtam:=ELE_TYP();

INSERT INTO TABLE (select v.a FROM myexample v) VALUES (:new.val,NULL,:new.TT) ;
END;
/

THERE WILL BE NO ERROR.

But if the trigger is:

If I write the trigger:
create or replace TRIGGER myexample_view_tri INSTEAD OF INSERT
ON NESTED TABLE a OF myexample_view FOR EACH ROW
declare
VTtam ELE_TYP;

BEGIN
--VTtam:=ELE_TYP();

INSERT INTO TABLE (select v.a FROM myexample v) VALUES (:new.val,:new.VT,:new.TT) ;
END;
/
The ERROR IS: ORA-03113:end-of-file on communication channel and it makes me disconnect as well.

I need all of your help. So thank you so much!

Report message to a moderator

Re: trigger has :new is a collection type [message #409242 is a reply to message #409240]

Sun, 21 June 2009 01:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Previous Topic:	Call multiple schema in Function
Next Topic:	how to create schema in this book

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 18:01:22 CST 2024