Home » SQL & PL/SQL » SQL & PL/SQL » trigger has :new is a collection type (Oracle 10g)
trigger has :new is a collection type [message #409224] Sat, 20 June 2009 13:42 Go to next message
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)


)
) ;
Re: trigger has :new is a collection type [message #409225 is a reply to message #409224] Sat, 20 June 2009 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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.
Re: trigger has :new is a collection type [message #409240 is a reply to message #409225] Sun, 21 June 2009 00:54 Go to previous messageGo to next message
khuatdinh
Messages: 2
Registered: June 2009
Junior Member
Embarassed 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!
Re: trigger has :new is a collection type [message #409242 is a reply to message #409240] Sun, 21 June 2009 01:16 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Call multiple schema in Function
Next Topic: how to create schema in this book
Goto Forum:
  


Current Time: Sat Dec 03 01:26:44 CST 2016

Total time taken to generate the page: 0.11831 seconds