Home » SQL & PL/SQL » SQL & PL/SQL » Errors (ORA-03113, ORA-03114) for trigger and deref()
Errors (ORA-03113, ORA-03114) for trigger and deref() [message #273554] Wed, 10 October 2007 22:10 Go to next message
VladSel
Messages: 1
Registered: October 2007
Junior Member
Help ???

CREATE TYPE v1_type1 AS OBJECT (
id number,
name VARCHAR(200)
);

CREATE TABLE v1_table_type1 OF v1_type1;

CREATE TABLE v1_table1 (
id number,
name varchar2(150),
kol number,
t1 ref v1_type1 scope is v1_table_type1,
ID_T1 NUMBER
);

alter table V1_TABLE1
add constraint V1_TABLE1_FK foreign key (ID_T1)
references V1_TABLE_TYPE1 (ID);

create or replace trigger v1_table1
before insert or update or delete on v1_table1
for each row
declare
cnt_ number;
begin
----------------------------------------------------------------------
if inserting then
if :new.id_t1 is null then
:new.t1:=null;
else
select count(*) into cnt_ from v1_table_type1 t where t.id=:new.id_t1;
if cnt_=1 then
select ref(t) into :new.t1 from v1_table_type1 t where t.id=:new.id_t1;
else
raise_application_error(-20000,'Объектная ошибка!');
end if;
end if;
end if;
----------------------------------------------------------------------
if updating then
if :new.id_t1 is null then
:new.t1:=null;
else
select count(*) into cnt_ from v1_table_type1 t where t.id=:new.id_t1;
if cnt_=1 then
select ref(t) into :new.t1 from v1_table_type1 t where t.id=:new.id_t1;
else
raise_application_error(-20000,'Объектная ошибка!');
end if;
end if;
end if;
----------------------------------------------------------------------
if deleting then
null;
end if;
----------------------------------------------------------------------
end v1_table1;

insert into v1_table_type1 (id, name)
values (10,'aaa');
insert into v1_table_type1 (id, name)
values (11,'bbb');
insert into v1_table_type1 (id, name)
values (12,'ccc');
commit;

insert into v1_table1 (id, name, kol, id_t1)
values (10,'sss', 12, 1);
commit;

insert into v1_table1 (id, name, kol, id_t1)
values (11,'sss', 12, null);
commit;
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to ORACLE
Re: Errors (ORA-03113, ORA-03114) for trigger and deref() [message #273560 is a reply to message #273554] Wed, 10 October 2007 22:21 Go to previous message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated above.
The ORA-03113 should have placed a few lines in the alertSID.log files & created a trace file.
Post the contents of the trace file back here.
Previous Topic: How Do I get 'GMT' string as part of time zone format
Next Topic: where clause
Goto Forum:
  


Current Time: Wed Dec 07 16:44:01 CST 2016

Total time taken to generate the page: 0.12819 seconds