Difference between view and subquery using user-defined types

Difference between view and subquery using user-defined types

Post by Carsten Reinhar » Fri, 10 Sep 1999 04:00:00


we are having trouble querying views using user-defined types (Oracle
8+8i on Solaris). A select on a view works while the same select on the
corresponding subquery results in ORA-00904.

We use the following defintion:

create or replace view ev as    (select e1.o1.obj_method() as obj from
e01 e1);

In this
select e2.obj.obj_method() from ev e2;
works,  while
select e2.obj.obj_method() from (select e1.o1.obj_method() as obj from
e01 e1) e2;
results in the above error message.

Has anyone any experience/solution for this problem ??

All necessary definitions for the example to run are appended below.

Carsten Reinhard,
University of Hannover, Department of Mathematics and Computer Science.

create type obj_type as object (
        i       number,
        j       number,
        member  function obj_method return obj_type,
        pragma  restrict_references (obj_method, RNDS, WNDS, RNPS, WNPS)

create or replace type body obj_type as
        member function obj_method return obj_type
        is begin
                return NULL;

create table e01 (
        o1      obj_type,
        o2      obj_type);


1. user defined function - error converting data type when used in a view

I have a user defined function which is passed a numeric and then
returns another numeric (a range lookup).

When used in a view, this returns everything as expected.

I then try to limit the view by the returned value (select * from
myview where maxrange >= 5), but I get an error 'Converting nvarchar
to numeric'.

Problem is, that I know it is numeric.

My question is - can I do select from a view on the result of a
function (I think YES).... my second problem is that if I do something
like (select * from myview where somecolumn=1234567 and myvalue >= 5)
this will work.

I can run this sucessfully on my development SQL Server 2000, but the
production database (which I created my scripts from the development
database) do not work. I am confused at the differences as the
databases should be identical.

Anyone got any ideas?

2. Which one is the best? Paradox, MS-Access or Foxpro?

3. ORA-3113 using views and user-defined types

4. Newbie question: Any problems with mac OS 9.1 ?

5. Using a User defined function returning a table in a subquery

6. Newbie Help: query 2 databases

7. Using user defined types in table variables?

8. need postnet utility

9. Advice needed in using user-defined types in table variables

10. Using user defined types

11. Using user-defined types in temp tables

12. DAO in VB5 yields User-defined type not defined

13. How to define a user-defined type in ACCESS