Re: ORA-00932 and Select * from <table>, using nested table.

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 11 Jun 2007 21:39:45 +0200
Message-ID: <466da503$0$2772$426a74cc@news.free.fr>

"Lucas Carvalho Teixeira" <lucascarvalho_at_gmail.com> a écrit dans le message de news: 1181589922.467384.103080_at_u2g2000hsc.googlegroups.com... On 11 jun, 16:20, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

> <lucascarva..._at_gmail.com> a écrit dans le message de news: 1181589406.152996.207..._at_p47g2000hsd.googlegroups.com...

> | I am using oracle-xe-univ-10.2.0.1-1.0.i386.rpm and I have this
> | simple example.
> |
> | CREATE TYPE Course AS OBJECT (
> | course_no NUMBER(4),
> | title VARCHAR2(35),
> | credits NUMBER(1));
> |
> | CREATE TYPE CourseList AS TABLE OF Course;
> |
> | CREATE TABLE department (
> | name VARCHAR2(20),
> | director VARCHAR2(20),
> | office VARCHAR2(20),
> | courses CourseList)
> | NESTED TABLE courses STORE AS courses_tab;
> |
> | INSERT INTO department
> | VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
> | CourseList(Course(1000, 'General Psychology', 5),
> | Course(2100, 'Experimental Psychology', 4),
> | Course(2200, 'Psychological Tests', 3),
> | Course(2250, 'Behavior Modification', 4),
> | Course(3540, 'Groups and Organizations', 3),
> | Course(3552, 'Human Factors in Busines', 4),
> | Course(4210, 'Theories of Learning', 4),
> | Course(4320, 'Cognitive Processes', 4),
> | Course(4410, 'Abnormal Psychology', 4)));
> |
> | The problem is when I try to execute:
> |
> | SELECT * FROM department;
> |
> | I am gotting this error:
> |
> | ORA-00932: inconsistent datatypes: expected NUMBER got
> | ARGUS.COURSELIST; <Btw Argus is my database name>
> |

>
> Works for me in 10.2.0.3:
> SQL> SELECT * FROM department;
> NAME                 DIRECTOR             OFFICE
> -------------------- -------------------- --------------------
> COURSES(COURSE_NO, TITLE, CREDITS)
> ------------------------------------------------------------------------------------------------------------------------
> Psychology           Irene Friedman       Fulton Hall 133
> COURSELIST(COURSE(1000, 'General Psychology', 5), COURSE(2100, 'Experimental Psychology', 4), COURSE(2200, 'Psychologica
> l Tests', 3), COURSE(2250, 'Behavior Modification', 4), COURSE(3540, 'Groups and Organizations', 3), COURSE(3552, 'Human
>  Factors in Busines', 4), COURSE(4210, 'Theories of Learning', 4), COURSE(4320, 'Cognitive Processes', 4), COURSE(4410,
> 'Abnormal Psychology', 4))
>
> Regards
> Michel Cadot

What oracle version are you using?
Why is it not working here?
Oracle version?
I am using the Application Express 2.1.0.00.39 to execute the SQL commands.
What is wrong? Do you have any idea?

Thank you anyways.

---

As I said I use version 10.2.0.3
It was with SQL*Plus
I don't have any idea why it does not work with but I don't know Application Express.
It was just to show you that it is a valid SQL.

Regards
Michel Cadot Received on Mon Jun 11 2007 - 14:39:45 CDT