Home » SQL & PL/SQL » SQL & PL/SQL » Reference a Varray Type
Reference a Varray Type [message #202766] Sun, 12 November 2006 02:28 Go to next message
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!
Re: Reference a Varray Type [message #202792 is a reply to message #202766] Sun, 12 November 2006 16:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
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>

Re: Reference a Varray Type [message #202793 is a reply to message #202792] Sun, 12 November 2006 17:54 Go to previous messageGo to next message
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,
Re: Reference a Varray Type [message #202808 is a reply to message #202793] Sun, 12 November 2006 22:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
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.
Re: Reference a Varray Type [message #203119 is a reply to message #202808] Mon, 13 November 2006 23:31 Go to previous message
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!
Previous Topic: what method is used in decrementing of cnt from day 13
Next Topic: Double Inheritence
Goto Forum:
  


Current Time: Mon Dec 05 13:05:10 CST 2016

Total time taken to generate the page: 0.23014 seconds