Home » SQL & PL/SQL » SQL & PL/SQL » Errors while trying to create object Tables,kindly help. (Oracle 10.2g)
Errors while trying to create object Tables,kindly help. [message #380886] Tue, 13 January 2009 16:26 Go to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
sorry the messages are duplicated due to network problem.

Hi Created the Object type as
 1  CREATE TYPE address_objtyp AS OBJECT (
  2    streetname       VARCHAR2(30),
  3*   postcode         VARCHAR2(30));

 
  1  CREATE TYPE type_schoolInfo As OBJECT
  2  (  SCHOOLID                        REF type_school
  3  ,  SCHOOLNAME              VARCHAR2(50)
  4  ,  PHONELIST                       VARCHAR2(50)
  5  ,  ADDRESS                address_objtyp
  6  ,  HEADTEACHERNAME VARCHAR2(30)
  7* ,  LEAPASSWORD             VARCHAR2(30)) ;



when i try to create a table based on that as

SQL> CREATE TABLE SchoolInformation_table OF type_schoolInfo
  2     (PRIMARY KEY (SCHOOLID, SCHOOLNAME))
  3     OBJECT IDENTIFIER PRIMARY KEY;
CREATE TABLE SchoolInformation_table OF type_schoolInfo
*
ERROR at line 1:
ORA-22971: invalid datatype for PRIMARY KEY-based object identifier



My opinion why the error occurs is maybe because it is creating SCHOOLID REF type_school in type_schoolInfo with datatype as type and thats why it says invalid datatype.
kindly point out as to why the error occurs.and how it can be corrected.

Thank you.

[Updated on: Tue, 13 January 2009 16:27]

Report message to a moderator

Re: Errors while trying to create object Tables,kindly help. [message #380888 is a reply to message #380886] Tue, 13 January 2009 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Oracle Error :: ORA-22971

invalid datatype for PRIMARY KEY-based object identifier

Cause

When creating an object view, the datatype of an expression in the WITH OBJECT OID clause is not allowed for PRIMARY KEY-based OID.

Action

Replace the expression with one of appropriate scalar datatype.

Are you unwilling or incapable to GOOGLE the error code yourself?
Re: Errors while trying to create object Tables,kindly help. [message #380889 is a reply to message #380888] Tue, 13 January 2009 17:06 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
I did google the error but thats not the correct solution thats why i was seeking for help

Replace the expression with one of appropriate scalar datatype

The reason why we create
CREATE TYPE type_schoolInfo As OBJECT
(  SCHOOLID                        REF type_school
,  SCHOOLNAME              VARCHAR2(50)
,  PHONELIST                       VARCHAR2(50)
,  ADDRESS                address_objtyp
,  HEADTEACHERNAME         VARCHAR2(30)
,  LEAPASSWORD             VARCHAR2(30)) ;


It is because we want to declare a datatype which can be used

so when we create
CREATE TYPE type_school AS OBJECT
( 	SCHOOL			VARCHAR2(30)
, 	VA				NUMBER(4)
, 	ID				NUMBER(4)
, 	SN				VARCHAR2(30)) ; /


and then say

CREATE TYPE type_schoolInfo As OBJECT
(  SCHOOLID                        REF type_school


that dosent mean to create the schoolid as a type but to refer to type_school type.

Thank you for the reply.
Re: Errors while trying to create object Tables,kindly help. [message #380891 is a reply to message #380886] Tue, 13 January 2009 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Your 2nd post made NO mention of Primary Key which was what caused the error in the 1st post in this thread.

We are left to guess what problem you are really trying to solve
or even the desired solution.

With regards to (ab)using Object Oriented Programming inside of Oracle; you reap what you sow.
Re: Errors while trying to create object Tables,kindly help. [message #380892 is a reply to message #380891] Tue, 13 January 2009 17:32 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
could you kindly give me a reply which i can understand.obviously thats why i am requesting help in the forum.
I am trying to create object tables


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

School Information Type

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)
) ;
/


Then
when i try to create a object table based on that as

SQL> CREATE TABLE SchoolInformation_table OF type_schoolInfo
  2     (PRIMARY KEY (SCHOOLID, SCHOOLNAME))
  3     OBJECT IDENTIFIER PRIMARY KEY;
CREATE TABLE SchoolInformation_table OF type_schoolInfo
*
ERROR at line 1:
ORA-22971: invalid datatype for PRIMARY KEY-based object identifier


Thank you
Re: Errors while trying to create object Tables,kindly help. [message #380895 is a reply to message #380886] Tue, 13 January 2009 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What part of the following do you NOT understand?

Action -
Replace the expression with one of appropriate scalar datatype.

Oracle does NOT support Primary Key on Object datatype.





Re: Errors while trying to create object Tables,kindly help. [message #380896 is a reply to message #380895] Tue, 13 January 2009 19:27 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Do u mean to say


This below code should be changed as
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)



CREATE OR REPLACE TYPE SchoolInformationType As OBJECT
( SCHOOLID			VARCHAR2(30)<-------Static DType
, SCHOOLNAME		VARCHAR2(50)
, PHONELIST			TelNumArrayType
, ADDRESS ROW	( STREETNAME VARCHAR2(50),
			  POSTCODE VARCHAR2(50)) 
, HEADTEACHERNAME	VARCHAR2(50)
, LEAPASSWORD		VARCHAR2(50)



is this what your saying.just confirming.
Re: Errors while trying to create object Tables,kindly help. [message #380897 is a reply to message #380886] Tue, 13 January 2009 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Again you leave out PRIMARY KEY clause which generated the ERROR!

Why waste your time & my time posting here, as opposed to simply do it yourself?

If your DDL & DML do not throw errors & obtain your desired results (which you still have never posted here),
why do you continue to have questions?

I know that when Oracle complains with an error message, Problem Exists Between Keyboard And Chair (PEBKAC).
If you can not or will not RTFM to resolve syntax errors,
perhaps you should attempt something less challenging for you.

[Updated on: Tue, 13 January 2009 19:38]

Report message to a moderator

Re: Errors while trying to create object Tables,kindly help. [message #380898 is a reply to message #380897] Tue, 13 January 2009 19:44 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Calm down,thank you for reply i understand it might be frustrating for you,just trying to learn here.

Ok so you mentioned i am missing out the primary key

you mena to say th eobjectType needs to be defined with a primary key

CREATE OR REPLACE TYPE SchoolInformationType As OBJECT
( SCHOOLID			VARCHAR2(30)<-------Static DType
, SCHOOLNAME		VARCHAR2(50)
, PHONELIST			TelNumArrayType
, ADDRESS ROW	( STREETNAME VARCHAR2(50),
			  POSTCODE VARCHAR2(50)) 
, HEADTEACHERNAME	VARCHAR2(50)
, LEAPASSWORD		VARCHAR2(50)
PRIMARY KEY(SCHOOLID));



If i am wrong just explain me,i am not trying to wantedly waste your time,sorry for that.
Re: Errors while trying to create object Tables,kindly help. [message #380899 is a reply to message #380886] Tue, 13 January 2009 19:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.
Re: Errors while trying to create object Tables,kindly help. [message #380900 is a reply to message #380899] Tue, 13 January 2009 20:00 Go to previous message
sqlstar_student
Messages: 42
Registered: November 2007
Member
I am new to object types and just having hard time,thank you for the time,however the project i am working on does not provide me much time right now as the deadline is tomorrow.

There arent any ddl or dml statements just need to create object type and object table using the object type.

Guess have to give up on that section.

Appreciate your replies.
Previous Topic: How to get Procedure values into a Local Variable
Next Topic: Had a Problem with type in function
Goto Forum:
  


Current Time: Fri Dec 09 03:45:37 CST 2016

Total time taken to generate the page: 0.10772 seconds