Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Try this:
    Code:
    create or replace type ssn_t as object( ssn_type char( 11 ) )/
    create table z( a char(4), b ssn_t )/
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep Power
    0
    Thanks, this worked.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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?
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    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' 
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    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.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Can you try the 'select' in another sql client besides sqlplus maybe sqldevloper, toad, etc.?
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    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.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    [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
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    8
    Rep 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
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo