Home » SQL & PL/SQL » SQL & PL/SQL » SQL 1999 problem (Oracle 9i)
SQL 1999 problem [message #379644] Wed, 07 January 2009 07:44 Go to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
I have create a type as follows

CREATE TYPE TeacherType AS OBJECT
	(TID NUMBER, TEACHERINITIAL VARCHAR2(50), TEACHERNAME VACHAR2(50),
	 SCHOOL NUMBER, SUBJECTID NUMBER); /


And have tried to create a table using that type as follows

CREATE TABLE Teacher OF TeacherType (TID PRIMARY KEY)
OBJECT IDENTIFIER PRIMARY KEY
SCHOOL REF(SchoolType) SCOPE SCHOOL;


But I get the error "ERROR at line 3: ORA-00922: missing or invalid option"

The SchoolType is as follows if it helps:

CREATE TYPE SchoolType AS OBJECT
	(SCHOOL NUMBER, VA NUMBER, ID NUMBER, SN VARCHAR(50)); /


Thanks for any help.

Re: SQL 1999 problem [message #379645 is a reply to message #379644] Wed, 07 January 2009 07:49 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
drpepperman85 wrote on Wed, 07 January 2009 08:44

TEACHERNAME VACHAR2(50),




Invalid datatype.
Re: SQL 1999 problem [message #379648 is a reply to message #379645] Wed, 07 January 2009 07:53 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
Cheers, it's times like this I hate the small things about programming.
Re: SQL 1999 problem [message #379649 is a reply to message #379644] Wed, 07 January 2009 07:56 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
Although, when I type

CREATE TABLE Teacher OF TeacherType (TID PRIMARY KEY)
OBJECT IDENTIFIER PRIMARY KEY
SCHOOL REF(SchoolType) SCOPE SCHOOL;


I still get that error on the 3rd line.
Re: SQL 1999 problem [message #379716 is a reply to message #379644] Wed, 07 January 2009 14:30 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
When I leave out the line

SCHOOL REF(SchoolType) SCOPE SCHOOL;


It works and creates the table. It seems to be just this line above.

Am I missing anything out from this line to get this to work?

Re: SQL 1999 problem [message #379746 is a reply to message #379716] Wed, 07 January 2009 18:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE TYPE SchoolType AS OBJECT
  2  ( SCHOOL	  NUMBER
  3  , VA	  NUMBER
  4  , ID	  NUMBER
  5  , SN	  VARCHAR2(50)
  6  );
  7  /

Type created.

SCOTT@orcl_11g> CREATE TABLE SchoolType_tab OF SchoolType
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE TeacherType AS OBJECT
  2  ( TID		   NUMBER
  3  , TEACHERINITIAL	   VARCHAR2(50)
  4  , TEACHERNAME	   VARCHAR2(50)
  5  , SCHOOL		   REF Schooltype
  6  , SUBJECTID	   NUMBER
  7  );
  8  /

Type created.

SCOTT@orcl_11g> CREATE TABLE Teacher OF TeacherType
  2  ( TID PRIMARY KEY )
  3  OBJECT IDENTIFIER PRIMARY KEY
  4  /

Table created.

SCOTT@orcl_11g> ALTER TABLE Teacher
  2  ADD (SCOPE FOR (SCHOOL) IS SchoolType_tab)
  3  /

Table altered.

SCOTT@orcl_11g>

Re: SQL 1999 problem [message #379899 is a reply to message #379644] Thu, 08 January 2009 04:53 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
Thanks for the help. I have tried something similar on my database but now get a new error:

SQL> CREATE TABLE Student OF StudentType (UPN PRIMARY KEY)
  2  OBJECT IDENTIFIER PRIMARY KEY;

Table created.

SQL> ALTER TABLE Student
  2  ADD (SCOPE FOR (SCHOOL) IS SchoolType) /
ADD (SCOPE FOR (SCHOOL) IS SchoolType)
     *
ERROR at line 2:
ORA-22893: constraint can be specified only for REF columns

[Updated on: Thu, 08 January 2009 04:54]

Report message to a moderator

Re: SQL 1999 problem [message #379970 is a reply to message #379899] Thu, 08 January 2009 13:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It is difficult to guess at what is wrong when you don't post all of the related code. However, the error message is pretty much self-explanatory. You are missing the REF somewhere in the portion of the code that you did not post.

You are probably doing something like this:

SCOTT@orcl_11g> CREATE OR REPLACE TYPE SchoolType AS OBJECT
  2  ( SCHOOL	  NUMBER
  3  , VA	  NUMBER
  4  , ID	  NUMBER
  5  , SN	  VARCHAR2(50)
  6  );
  7  /

Type created.

SCOTT@orcl_11g> CREATE TABLE SchoolType_tab OF SchoolType
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE StudentType AS OBJECT
  2  ( UPN		   NUMBER
  3  , SCHOOL		   NUMBER
  4  );
  5  /

Type created.

SCOTT@orcl_11g> CREATE TABLE Student OF StudentType
  2  ( UPN PRIMARY KEY )
  3  OBJECT IDENTIFIER PRIMARY KEY
  4  /

Table created.

SCOTT@orcl_11g> ALTER TABLE Student
  2  ADD (SCOPE FOR (SCHOOL) IS SchoolType_tab)
  3  /
ADD (SCOPE FOR (SCHOOL) IS SchoolType_tab)
     *
ERROR at line 2:
ORA-22893: constraint can be specified only for REF columns


You should be doing something like the following. Notice that the REF in the StudentType and that the scope is for SchoolType_tab.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE SchoolType AS OBJECT
  2  ( SCHOOL	  NUMBER
  3  , VA	  NUMBER
  4  , ID	  NUMBER
  5  , SN	  VARCHAR2(50)
  6  );
  7  /

Type created.

SCOTT@orcl_11g> CREATE TABLE SchoolType_tab OF SchoolType
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE StudentType AS OBJECT
  2  ( UPN		   NUMBER
  3  , SCHOOL		   REF Schooltype
  4  );
  5  /

Type created.

SCOTT@orcl_11g> CREATE TABLE Student OF StudentType
  2  ( UPN PRIMARY KEY )
  3  OBJECT IDENTIFIER PRIMARY KEY
  4  /

Table created.

SCOTT@orcl_11g> ALTER TABLE Student
  2  ADD (SCOPE FOR (SCHOOL) IS SchoolType_tab)
  3  /

Table altered.


Re: SQL 1999 problem [message #380621 is a reply to message #379644] Mon, 12 January 2009 14:42 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
Thanks for the help it has cleared up alot for me. One last problem that I have is trying to create a type with a ROW type as follows:

CREATE OR REPLACE TYPE SchoolInformationType As OBJECT
( SCHOOLID			REF SchoolType
, SCHOOLNAME		VARCHAR2(50)
, PHONELIST			TelNumArrayType
, ADDRESS ROW	( STREETNAME VARCHAR2(50),
			  POSTCODE VARCHAR2(50)) 
, HEADTEACHERNAME	VARCHAR2(50)
, LEAPASSWORD		VARCHAR2(50)
) ;
/


I get the error:

Warning: Type created with compilation errors.


But when I go to desc the newly created type I get the following:

SQL> desc schoolinformation;
ERROR:
ORA-24372: invalid object for describe


Could anyone point out where I am going wrong?

Cheers
Re: SQL 1999 problem [message #380622 is a reply to message #380621] Mon, 12 January 2009 14:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You have created SchoolInformationType and then tried to describe SchoolInformation. The names are different.

[Updated on: Mon, 12 January 2009 14:53]

Report message to a moderator

Re: SQL 1999 problem [message #380628 is a reply to message #380622] Mon, 12 January 2009 15:08 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
Barbara Boehmer wrote on Mon, 12 January 2009 20:52
You have created SchoolInformationType and then tried to describe SchoolInformation. The names are different.



Sorry about that, lack of sleep is giving me typos!

SQL> desc schoolinformationtype;
ERROR:
ORA-24372: invalid object for describe


Same error.
Re: SQL 1999 problem [message #380629 is a reply to message #379644] Mon, 12 January 2009 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ORA-24372: invalid object for describe
Cause: The object to be described is not valid. It either has compilation or authorization errors.
Action: The object to be described must be valid.

but you already knew it was invalid.
So make it valid & try again.

[Updated on: Mon, 12 January 2009 15:18]

Report message to a moderator

Re: SQL 1999 problem [message #380630 is a reply to message #380628] Mon, 12 January 2009 15:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can use show errors to show what the errors are and where they are as shown below. As previously requested, you need to provide copy and paste of a continuous run of all related code, including dependent objects, complete with line numbers, as I did in my previous demo.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE SchoolInformationType As OBJECT
  2  ( SCHOOLID 		     REF SchoolType
  3  , SCHOOLNAME	     VARCHAR2(50)
  4  , PHONELIST		     TelNumArrayType
  5  , ADDRESS ROW   ( STREETNAME VARCHAR2(50),
  6  			       POSTCODE VARCHAR2(50))
  7  , HEADTEACHERNAME	     VARCHAR2(50)
  8  , LEAPASSWORD	     VARCHAR2(50)
  9  ) ;
 10  /

Warning: Type created with compilation errors.

SCOTT@orcl_11g> desc schoolinformationtype
ERROR:
ORA-24372: invalid object for describe


SCOTT@orcl_11g> show errors type schoolinformationtype
Errors for TYPE SCHOOLINFORMATIONTYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/28     PLS-00103: Encountered the symbol "VARCHAR2" when expecting one
         of the following:
         . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec between || multiset member submultiset
         The symbol "." was substituted for "VARCHAR2" to continue.

6/15     PLS-00103: Encountered the symbol "VARCHAR2" when expecting one
         of the following:
         . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec between || multiset member submultiset
         The symbol "." was substituted for "VARCHAR2" to continue.

SCOTT@orcl_11g>



Re: SQL 1999 problem [message #380631 is a reply to message #380621] Mon, 12 January 2009 15:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Where did you get this syntax or what makes you think this should work?

ADDRESS ROW ( STREETNAME VARCHAR2(50),
POSTCODE VARCHAR2(50))

Re: SQL 1999 problem [message #380633 is a reply to message #380630] Mon, 12 January 2009 15:43 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
SQL> CREATE OR REPLACE TYPE SchoolInformationType As OBJECT
  2  ( SCHOOLID   REF SchoolType
  3  , SCHOOLNAME  VARCHAR2(50)
  4  , PHONELIST   TelNumArrayType
  5  , ADDRESS ROW ( STREETNAME VARCHAR2(50),
  6       POSTCODE VARCHAR2(50)) 
  7  , HEADTEACHERNAME VARCHAR2(50)
  8  , LEAPASSWORD  VARCHAR2(50)
  9  )
 10  ;
 11  /

Warning: Type created with compilation errors.

SQL> desc schoolinformation;
ERROR:
ORA-24372: invalid object for describe


SQL> show errors type schoolinformationtype;
Errors for TYPE SCHOOLINFORMATIONTYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/28     PLS-00103: Encountered the symbol "VARCHAR2" when expecting one
         of the following:
         . ( ) , * @ % & | = - + < / > at in is mod remainder not
         range rem => .. <an exponent (**)> <> or != or ~= >= <= <>
         and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member
         SUBMULTISET_
         The symbol "." was substituted for "VARCHAR2" to continue.

6/15     PLS-00103: Encountered the symbol "VARCHAR2" when expecting one
         of the following:
         . ( ) , * @ % & | = - + < / > at in is mod remainder not

LINE/COL ERROR
-------- -----------------------------------------------------------------
         range rem => .. <an exponent (**)> <> or != or ~= >= <= <>
         and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member
         SUBMULTISET_
         The symbol "." was substituted for "VARCHAR2" to continue.
Re: SQL 1999 problem [message #380634 is a reply to message #380631] Mon, 12 January 2009 15:45 Go to previous messageGo to next message
drpepperman85
Messages: 9
Registered: January 2009
Junior Member
I need to add those attributes as a ROW type.

I take it from your post this can't be done via the way I have tried to implemented it.
Re: SQL 1999 problem [message #380638 is a reply to message #380634] Mon, 12 January 2009 15:56 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Your syntax looks like something from Informatix or some such thing. Oracle's version of a named row type is as follows:

SCOTT@orcl_11g> CREATE OR REPLACE TYPE address AS OBJECT
  2    (streetname  VARCHAR2(50),
  3  	postcode    VARCHAR2(50))
  4  /

Type created.

SCOTT@orcl_11g> CREATE TYPE address_row AS TABLE OF address;
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE SchoolInformationType AS OBJECT
  2    (some_columns  VARCHAR2(50),
  3  	addresses     address_row,
  4  	more_columns  VARCHAR2(50));
  5  /

Type created.

SCOTT@orcl_11g> DESC SchoolInformationType
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOME_COLUMNS                                       VARCHAR2(50)
 ADDRESSES                                          ADDRESS_ROW
 MORE_COLUMNS                                       VARCHAR2(50)

SCOTT@orcl_11g> 

Previous Topic: Duplicate rows
Next Topic: replace space with *
Goto Forum:
  


Current Time: Sat Dec 10 12:37:46 CST 2016

Total time taken to generate the page: 0.21952 seconds