This discussion is archived
13 Replies Latest reply: Jan 19, 2013 2:56 PM by rp0428 RSS

ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty

984152 Newbie
Currently Being Moderated
Hi,
insert the following type in oracle express edition 11g release 2:

CREATE TYPE emp_person_ty AS OBJECT (
name VARCHAR2(30),
manager REF emp_person_ty );

and the following table:

CREATE TABLE emp_person_obj_table OF emp_person_ty;

when I want to view the data in the table is the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty

Help me!

Edited by: 981149 on 10-gen-2013 9.54
  • 1. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
    >
    insert the following type in oracle express edition 11g release 2:

    CREATE TYPE emp_person_ty AS OBJECT (
    name VARCHAR2(30),
    manager REF emp_person_ty );

    and the following table:

    CREATE TABLE emp_person_obj_table OF emp_person_ty;

    when I want to view the data in the table is the following error:

    ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    >
    How can anyone help you? You say your problem is related to 'when I want to view the data in the table' but you don't provide ANY information about how you are trying to 'view the data in the table'.

    Post the DML showing how you put data in the table and the query you are trying to use to get the data back out.
  • 2. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    984152 Newbie
    Currently Being Moderated
    open oracle 11g express edition.
    From the main home and then go to the sql workshop in object browser.
    Here I select in the drop-down table and the corresponding table.
    I select data from the table to the right and I do not view the data in the table but the error that I pointed out.
    I could not send screenshots to be more clear.
  • 3. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    Fran Guru
    Currently Being Moderated
    this error shows when you try to insert any value into the table or just only when do a select ?
    could you try with sql?

    open a sql windows and execute the same:
    SQL> CREATE TYPE emp_person_ty AS OBJECT (
      2  name VARCHAR2(30),
      3  manager REF emp_person_ty );
    
    Tipo creado.
    
    SQL> CREATE TABLE emp_person_obj_table OF emp_person_ty;
    
    Tabla creada.
    
    SQL> select * from emp_person_obj_table;
    
    ninguna fila seleccionada
    
    SQL> desc emp_person_obj_table;
     Nombre                                    ‚ĒźNulo?   Tipo
     ----------------------------------------- -------- --------------------------
     NAME                                               VARCHAR2(30)
     MANAGER                                            REF OF EMP_PERSON_TY
    
    SQL> INSERT INTO emp_person_obj_table VALUES (
      2     emp_person_ty ('John Smith', NULL));
    
    1 fila creada.
    
    SQL> commit;
    
    Confirmaci¾n terminada.
    
    SQL> select * from emp_person_obj_table;
    
    NAME
    ------------------------------
    MANAGER
    -----------------------------------------------
    John Smith
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjint.htm
  • 4. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    984152 Newbie
    Currently Being Moderated
    this error is always, when insert new row or select the table.
    there are some package suitable for use REF?
    my problem i don't see element of table...
    how can I send screeshoot my database on this forum?
  • 5. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    rp0428 Guru
    Currently Being Moderated
    We don't need screenshots. So far you haven't posted anything that indicates there is ANY data in the table to view.

    Post the DML showing how you put data in the table and the query you are trying to use to get the data back out.
  • 6. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    984152 Newbie
    Currently Being Moderated
    this is what I did:

    CREATE TYPE emp_person_typ AS OBJECT (
    name VARCHAR2(30),
    manager REF emp_person_typ );

    CREATE TABLE emp_person_obj_table OF emp_person_typ;

    INSERT INTO emp_person_obj_table VALUES (
    emp_person_typ ('John Smith', NULL));

    Query:
    select *

    Output:
    Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_typ

    I can do any query, the result is always the same
  • 7. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    jgarry Guru
    Currently Being Moderated
    It worked ok for me. Are you sure you aren't getting any errors you haven't shown us? Have you tried dropping the objects and starting over?

    Edit: Just an offhand thought, perhaps you have some public synonyms defined and so have a different object?

    Edited by: jgarry on Jan 15, 2013 1:52 PM
  • 8. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    rp0428 Guru
    Currently Being Moderated
    >
    CREATE TYPE emp_person_typ AS OBJECT (
    name VARCHAR2(30),
    manager REF emp_person_typ );

    CREATE TABLE emp_person_obj_table OF emp_person_typ;

    INSERT INTO emp_person_obj_table VALUES (
    emp_person_typ ('John Smith', NULL));
    >
    Works fine for me in 11.2.0.1
  • 9. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    984152 Newbie
    Currently Being Moderated
    now apply the query:

    SELECT *
    FROM emp_person_obj_table

    to me is displayed:

    ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
  • 10. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    rp0428 Guru
    Currently Being Moderated
    >
    now apply the query:

    SELECT *
    FROM emp_person_obj_table

    to me is displayed:

    ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    >
    And to me is displayed:
    SELECT *
    FROM emp_person_obj_table
    /
    
    NAME     MANAGER
    John Smith     
    The fact that your error message has 'db.emp_person_ty' suggests that the type and table are defined in one schema but are being used in a different schema.

    If I do this I get an error, though not the one you get.
    -- as SCOTT
    grant select on emp_person_obj_table to hr;
    
    -- as HR
    SELECT *
    FROM scott.emp_person_obj_table
    
    OCI-22303: type "SCOTT"."EMP_PERSON_TYP" not found
    As jgarry suggested you should check for synonyms or other objects with the same name and you should check that the proper privileges have been granted to the user executing the query.

    What user owns the type object and the table? What user is executing the query?
  • 11. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    984152 Newbie
    Currently Being Moderated
    the user that creates the tables is the same as making the query!
    I'm always ...
    with regard to the scheme I have one project called db
    I can not understand what could be the problem
  • 12. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    984152 Newbie
    Currently Being Moderated
    here's all the transactions after downloading oracle:
    -start application start database;
    -started get started with oracle database application 11g express edition;
    -selected application express menu;
    -'re logged in with the credentials defined sys + password to the installation;
    created a workspace with:
    - Username: db;
    - Application username: dbase;
    - Added password;
    - Open the workspace I went on sql workshop;
    - Then I clicked on sql commands;
    - And I put the following commands:

    - CREATE TYPE emp_person_typ AS OBJECT (
              name VARCHAR2(30),
              manager REF emp_person_typ );

         -CREATE TABLE emp_person_obj_table OF emp_person_typ;

         -INSERT INTO emp_person_obj_table VALUES (
              emp_person_typ ('John Smith', NULL));

         -SELECT *
         FROM emp_person_obj_table;

    -at this point I will get the following errors:

    ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_typ

    I only do these operations I did! I did not change anything but these problems presented to me.
    I also tried on two different machines but nothing changes. help me!!!
  • 13. Re: ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_ty
    rp0428 Guru
    Currently Being Moderated
    >
    the user that creates the tables is the same as making the query!
    I'm always ...
    with regard to the scheme I have one project called db
    . . .
    created a workspace with:
    - Username: db;
    - Application username: dbase;
    - Added password;
    . . .
    ORA-00932: inconsistent datatypes: expected NUMBER got REF db.emp_person_typ
    >
    The oracle exception includes 'db.emp_person_typ'. I have never known Oracle to include a schema qualifier unless the object belongs to a different schema than the one being used to execute the query.l

    As already suggested above by me and others you should connect as a dba and see if there are any other objects or synonyms that are already using that object name. Perhaps they got created as part of a prior failed attempt and never got deleted.

    I also suggest you cross-post in the application express forum: Application Express

    In your post there provide the link to this thread and in this thread provide the link to your APEX thread so everyone can follow the entire chain of events.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points