Re: ORA-3113 using views and user-defined types

A copy of this was sent to Carsten Reinhard <reinhard_at_informatik.uni-hannover.de>
(if that email address didn't require changing) On Thu, 09 Sep 1999 16:16:55 +0200, you wrote:

>Hi,
>
>we are having trouble querying views using user-defined types (Oracle
>8+8i on Solaris). A regular select works while the same select with
>where-clause results in ORA-3113.
>
>We use the following defintion:
>
>create view e02v as (select e.m.obj2_method() as m, e.n.obj2_method() as
>n from e02 e);
>
>In this
>select * from e02v;
>works, while
>select * from e02v where n=1;
>results in the above error message.
>
>Has anyone any experience/solution for this problem ??
>

I confirmed this.

I filed bug 988230 for it.

It happens with the combination of NULL and the Nested table type....

Please call support and reference bug988230 and they will be able to track it for you.

>All necessary definitions for the example to run are appended below.
>
>Carsten Reinhard,
>University of Hannover, Department of Mathematics and Computer Science.
>
>
>
>Defs:
>
>create type obj1_type as object (
> i number);
>/
>
>create type obj1_tabl as table of obj1_type;
>/
>
>create type obj2_type as object (
> nt obj1_tabl,
> member function obj2_method return number,
> pragma restrict_references (obj2_method, RNDS, WNDS, RNPS, WNPS)
>);
>/
>
>create or replace type body obj2_type as
> member function obj2_method return number is
> begin
> return NULL;
> end;
>end;
>/
>
>create table e02 (
> m obj2_type,
> n obj2_type
>) nested table m.nt store as e02_m_ntabl,
> nested table n.nt store as e02_n_ntabl;
>
>insert into e02 values (obj2_type(obj1_tabl(obj1_type(1))),
>obj2_type(obj1_tabl(obj1_type(1))));

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 10:52:59 CDT