User Defined Data Type

Dev Shed Forums Sponsor:

June 19th, 2012, 07:46 PM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

User Defined Data Type

I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express.

The create type and select statements execute fine and select confirms the ssn_t is a type.

The create table statement fails with the invalid data type error.

Here is an example.

CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11));

SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';

CREATE TABLE Z (A CHAR(4), B SSN_T);

Any help will be very much appreciated, TIA - Joe

June 19th, 2012, 10:09 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 188

Time spent in forums: 2 Days 9 h 9 m 4 sec
Reputation Power: 41

Try this:

Code:

create or replace type ssn_t as object( ssn_type char( 11 ) )/
create table z( a char(4), b ssn_t )/

June 20th, 2012, 02:20 PM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

Thanks, this worked.

June 21st, 2012, 08:18 PM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

I moved on to the next step ->

INSERT INTO Z VALUES('ABC', ssn_t('123-45-6789'));

SELECT * FROM Z;

The INSERT worked fine, but the SELECT failed with a ORA-00904 "B" invalid identifier.

How can I access the ssn? TIA, Joe

June 25th, 2012, 11:24 AM

fel0nynf

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 1 h 1 m 29 sec
Reputation Power: 0

User defined datatypes
A user defined datatype can be created with create type.
The code is 108, the #define is SQLT_NTY.
PL/SQL datatypes
In addition to the internal datatypes, Oracle also knows the following datatypes within PL/SQL:

binary_double
binary_float
dec
decimal
double precision
float
int
integer
natural
naturaln
numeric
pls_integer
binary_integer is equivalent to pls_integer
positive
positiven
real
signtype
smallint
string
boolean
With Oracle 11g: simple_integer

June 25th, 2012, 02:08 PM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

Thanks for the reply. I am using a C# application connected to Oracle so I do not have access to many of the data types you mentioned.

Really wish I had a boolean type.

Still need a way to access the ssn noted above. Any ideas?

June 26th, 2012, 09:16 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 188

Time spent in forums: 2 Days 9 h 9 m 4 sec
Reputation Power: 41

I ran these statements and they work fine on Oracle 11.1:

Code:

create or replace type ssn_t as object( ssn_type char( 11 ) )/
create table z( a char(4), b ssn_t )/

INSERT INTO Z VALUES('ABC', ssn_t('123-45-6789'))/

select * from z
A     B             
----  ------------- 
ABC   '123-45-6789'

June 27th, 2012, 08:14 AM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

Don't know what I am doing wrong, but no joy here.

I would like to post a screen shot of the session, but cannot figure out how.
The image icon does nothing to let me upload a gif or jpg file.

June 27th, 2012, 08:42 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 37 m 18 sec
Reputation Power: 284

Quote:

Originally Posted by jbm417

I would like to post a screen shot of the session, but cannot figure out how.

Just copy & paste the text. Much better than a screenshot anyway.

#10

June 27th, 2012, 10:48 AM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

OK, here is the session on 10g Express

CREATE or REPLACE type ssn_t as object( ssn_type char( 11 ));

Type created.
0.75 secondsDBMS Output

CREATE table z( a char(4), b ssn_t );

Table created.
0.14 secondsDBMS Output

SELECT * from z;
ORA-00932: inconsistent datatypes: expected NUMBER got HR.SSN_T

SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';

OBJECT_NAME OBJECT_TYPE
---------------------------------------
GENERIC_OBJ TYPE
SSN_T TYPE

INSERT into z VALUES('ABC', ssn_t('123-45-6789'));

1 row(s) inserted.
0.08 secondsDBMS Output

SELECT * from z
ORA-00932: inconsistent datatypes: expected NUMBER got HR.SSN_T

#11

June 27th, 2012, 04:43 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 188

Time spent in forums: 2 Days 9 h 9 m 4 sec
Reputation Power: 41

Can you try the 'select' in another sql client besides sqlplus maybe sqldevloper, toad, etc.?

#12

June 27th, 2012, 05:25 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 37 m 18 sec
Reputation Power: 284

Seems to be a limitation of the 10g SQL*Plus (or the Express version, but I don't think that). It works fine in 11g:

Code:

SQL*Plus: Release 11.2.0.1.0 Production on Do Jun 28 00:22:42 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options
SQL> CREATE or REPLACE type ssn_t as object( ssn_type char( 11 ))
  2  /

Type created.

SQL>
SQL> CREATE table z( a char(4), b ssn_t )
  2  /

Table created.

SQL>
SQL> INSERT into z VALUES('ABC', ssn_t('123-45-6789'))
  2  /

1 row created.

SQL>
SQL> SELECT * from z
  2  /

A
----
B(SSN_TYPE)
--------------------------------------------------------------------------------
ABC
SSN_T('123-45-6789')


SQL>

Using SQL Developer or another SQL client is probably your best try.

#13

June 27th, 2012, 09:56 PM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

I tried another machine running 11.2g Express and here is the session sql.

Application Express 4.0.2.00.09

CREATE or REPLACE type ssn_t as object( ssn_type char( 11 ) );

Type created.

0.09 seconds

CREATE table z( a char(4), b ssn_t );

Table created.

0.01 seconds

INSERT INTO Z VALUES('ABC', ssn_t('123-45-6789'));

1 row(s) inserted.

0.01 seconds

SELECT * from z;

A B
--------------
ABC [unsupported data type]

So, I am at a loss. Maybe the problem is that Express editions do not allow this. Does anyone have
11.2g Express to try the exact same experiment, including Application Express 4.0.2.00.09 ?

TIA, Joe

#14

June 28th, 2012, 12:17 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 37 m 18 sec
Reputation Power: 284

[QUOTE=jbm417]So, I am at a loss. Maybe the problem is that Express editions do not allow this. Does anyone have
11.2g Express to try the exact same experiment, including Application Express 4.0.2.00.09 ?/QUOTE]That still doesn't look like a SQL*Plus output. My guess is that whatever client you are using, it does not support user defined data types.

Apparently you will need to use a different SQL client.

And please format your output using [c0de] tags. Compare my SQL*Plus output with yours. Details are here: http://forums.devshed.com/misc.php?do=bbcode

__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code

Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html

#15

June 28th, 2012, 08:16 AM

jbm417

Registered User

Join Date: Jun 2012

Posts: 8

Time spent in forums: 2 h 14 m 24 sec
Reputation Power: 0

In this latest post I was using application express that is part of the 11.2g download from Oracle.

Why would AE not support user defined types?

My real world requirement is a c# application connected to either 10g express or 11g express via XE service.

What does the c# app need to do to correctly display the results of a
SELECT statement that returns a user defined type?

TIA, Joe

User Defined Data Type

Developer Shed Advertisers and Affiliates