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

lucascarvalho_at_gmail.com wrote:
> 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>

First off your database name is not argus because Oracle is not SQL Server. Argus is the name of the schema that owns the COURSELIST type. And it appears from what you are trying to run a published demo.

Go to http://www.psoug.org/reference/nested_tab.html and try the demo there, all within a single schema. It should work.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org