OraFAQ Forum: SQL & PL/SQL » Reference a Varray Type

Home » SQL & PL/SQL » SQL & PL/SQL » Reference a Varray Type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reference a Varray Type [message #202766]

Sun, 12 November 2006 02:28

tlahyani
Messages: 20
Registered: November 2006

Junior Member

Hello -
I have a DepartmentType, a CourseType and a course_list that s a varray of CourseType.
I created a table of DepartmentType and I need a column that references all the courses that the department offers. I tried the following code, but it does not work, please let me know if you see what I'm doing wrong! Thanks!

DROP TYPE DepartmentType FORCE;
Type dropped.

DROP TYPE CourseType FORCE;
Type dropped.

DROP TYPE course_list FORCE;
Type dropped.

DROP TABLE DepartmentTab FORCE;
Table dropped.

DROP TABLE CourseTab FORCE;
Table dropped.

CREATE OR REPLACE TYPE DepartmentType AS OBJECT (
dno NUMBER,
name VARCHAR2(20),
building VARCHAR2(20),
budget NUMBER
);
/
Type created.

show err;
No errors.

CREATE OR REPLACE TYPE CourseType AS OBJECT (
cno NUMBER,
name VARCHAR2(20),
dept REF DepartmentType,
credits NUMBER
);
/
Type created.

show err;
No errors.

CREATE TYPE course_list AS VARRAY(50) OF REF CourseType;
/
Type created.

show err;
No errors.

ALTER TYPE DepartmentType
add attribute (coursesOffered course_list) CASCADE;
Type altered.

CREATE TABLE DepartmentTab OF DepartmentType
(PRIMARY KEY (dno))
OBJECT ID PRIMARY KEY
/
Table created.

CREATE TABLE CourseTab OF CourseType
(PRIMARY KEY (cno))
OBJECT ID PRIMARY KEY
/
Table created.

alter table coursetab
add constraint refCourse
FOREIGN KEY (dept) REFERENCES DepartmentTab
INITIALLY DEFERRED DEFERRABLE;
Table altered.

alter table departmenttab
add constraint refCourse
FOREIGN KEY (COURSESOFFERED) REFERENCES CourseTab
INITIALLY DEFERRED DEFERRABLE;

FOREIGN KEY (COURSESOFFERED) REFERENCES CourseTab
*

ERROR at line 3:
ORA-02267: column type incompatible with referenced column type
---------------------------------------------------------------

It is pretty obvious that the two types are incompatible, but how do I achieve this without having to create a table of type course_list? Thank you!

Report message to a moderator

Re: Reference a Varray Type [message #202792 is a reply to message #202766]

Sun, 12 November 2006 16:12

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I am trying to figure out what you are trying to do. It looks like your course_list should logically be part of your CourseTab, not your DepartmentTab. I think maybe you want something like this:

SCOTT@10gXE> 
SCOTT@10gXE> CREATE OR REPLACE TYPE DepartmentType AS OBJECT
  2    (dno			       NUMBER,
  3  	name			       VARCHAR2 (20),
  4  	building		       VARCHAR2 (20),
  5  	budget			       NUMBER);
  6  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE CourseType AS OBJECT
  2    (cno			       NUMBER,
  3  	name			       VARCHAR2 (20),
  4  	credits 		       NUMBER);
  5  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE course_list AS VARRAY (50) OF CourseType;
  2  /

Type created.

SCOTT@10gXE> CREATE TABLE DepartmentTab OF DepartmentType
  2    (PRIMARY KEY (dno))
  3    OBJECT id PRIMARY KEY
  4  /

Table created.

SCOTT@10gXE> CREATE TABLE CourseTab
  2    (dept			       REF DepartmentType REFERENCES DepartmentTab
  3  				       DEFERRABLE INITIALLY DEFERRED,
  4  	cno_list		       course_list)
  5  /

Table created.

SCOTT@10gXE>

Report message to a moderator

Re: Reference a Varray Type [message #202793 is a reply to message #202792]

Sun, 12 November 2006 17:54

tlahyani
Messages: 20
Registered: November 2006

Junior Member

Hello Barbara -

Your solution would definitely work. But in this particular case, what I d really like to have is a column in the department table that points to all courses that that department offers. I guess it's something like a nested table with references to all courses the department offers.
That is why I created the course_list type and added it to the department table, but then I can't figure out how to define the foreign key constraint. Is that possible? Thanks,

Report message to a moderator

Re: Reference a Varray Type [message #202808 is a reply to message #202793]

Sun, 12 November 2006 22:41

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

If you have the course_list in the DepartmentTab table, then there is no purpose for having a CourseTab table and no need for a foreign key. It would just be duplicate information.

Report message to a moderator

Re: Reference a Varray Type [message #203119 is a reply to message #202808]

Mon, 13 November 2006 23:31

tlahyani
Messages: 20
Registered: November 2006

Junior Member

The course_list would be a list of references to Courses, not a list of Course objects. I just thought it might enhance the performance of some queries if those references are available in the department table. Thanks again!

Report message to a moderator

Previous Topic:	what method is used in decrementing of cnt from day 13
Next Topic:	Double Inheritence

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:16:40 CST 2024