Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Red face Unanswered: how to synchronize two different user defined data types in two different schemas

    Hi all,

    I have two different schemas called schema01 and schema02.

    in schema01 I difined user difined data type called OBJ_CONTACT and office_details column in table01 has this datatype.All these stuffs is in schema01.

    in schema02 I want to insert table02.office_details to the details in table01.office_details.

    I'm using visual studio 2008 and when I used following coding it gives the error called "ORA-00932: inconsistent datatypes: expected schema01.OBJ_CONTACT got schema02.OBJ_CONTACT"

    the coding is as follows.

    INSERT INTO table02 VALUES((SELECT schema01.table01.office_details
    from schema01.table01
    where customer_id = 1));

    please help me to solve this bug.

  2. #2
    Join Date
    Jan 2012
    Posts
    12
    can anyone please tell me the answer as soon as possible.I'm in a big big trouble because of this matter.I cannot further developing my pl/sql package because I'm getting stuck in this point.It is better if anyone can reply me soon.Thank You.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    VALUES & SELECT are mutually exclusive
    use one or the other but never both in same statement
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    The error message is quite self-explanatory - as source and destination column have different data types, it is not possible to convert its value. You have to convert it explicitly. Of course it would not happen if the table contained multiple columns with Oracle built-in data types instead of one user-defined object one.

    It would also be helpful, if you posted a test case here as well - DDL and DML statements for re-creation your environment.
    Anyway, here they are:
    Code:
    create type obj_contact1 as object ( int integer, str varchar2(50) );
    /
    create table table01 ( office_details obj_contact1 );
    insert into table01( office_details )
    select obj_contact1( 1, 'a' ) from dual;
    
    create type obj_contact2 as object ( int integer, str varchar2(50) );
    /
    create table table02 ( office_details obj_contact2 );
    I was too lazy to cope with two schemas, so I created all in one schema, just distinguished data type names (as tables already have different names).
    As you did not post definition of OBJ_CONTACT data type, I did deduce it as simple OBJECT type with two attributes. I also suppose that it has the same definition in both schemas.
    It would be nice if you posted this in your initial post to save some work to the ones who try to help you and prevent doubts about your exact configuration.

    In the end (if my assumptions are correct), the required statement as simple as:
    Code:
    insert into table02( office_details )
    select obj_contact2( table01.office_details.int, table01.office_details.str )
    from table01 table01;

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by anacedent View Post
    VALUES & SELECT are mutually exclusive
    use one or the other but never both in same statement
    Not exactly; because of that additional parenthesis, the result of the SELECT statement is taken as a single value expression. It may fail when it will return more rows though. It will INSERT NULL, if it does not return any row. Also, if proceeded in LOOP, it may decrease performance in comparison with simple INSERT SELECT (as I posted).

    Hard to say, what is the correct behaviour, without seeing exact requirements on this statement (which I am unable to find in the initial post).

  6. #6
    Join Date
    Jan 2012
    Posts
    12

    hi flyboy

    my OBJ_CONTACT data type in both schemas are exactly same as follows.

    OBJ_CONTACT(a integer,b integer,c varchar(20));

    I coded my pl sql package according to your instructions.but it still gives the following error.

    PL/SQL: ORA-00904: "schema01"."table01"."OFFICE_DETAILS"."a": invalid identifier

    is this any issue about having lack of the priviledges in schema01?

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Tharangi View Post
    my OBJ_CONTACT data type in both schemas are exactly same as follows.

    OBJ_CONTACT(a integer,b integer,c varchar(20));
    The definition does not matter, for correct assignment it must be exactly the same data type. Thank you for confirming my guesses.
    Quote Originally Posted by Tharangi View Post
    I coded my pl sql package according to your instructions.but it still gives the following error.

    PL/SQL: ORA-00904: "schema01"."table01"."OFFICE_DETAILS"."a": invalid identifier
    No, it is not exactly as I posted. Firstly, you have to alias the table (the second TABLE01 in the FROM clause). Secondly, the column has to be referred exactly <TABLE_ALIAS>.<COLUMN_NAME>.<ATTRIBUTE_NAME> Oracle seems to be quite picky about the exact reference (most probably expecting schema/table/column names in exact places).
    Quote Originally Posted by Tharangi View Post
    is this any issue about having lack of the priviledges in schema01?
    Of course, SELECT privilege on SCHEMA01.TABLE01 has to be directly granted to the user owning the procedure (most probably SCHEMA02). But it does not seem to be the cause of this error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •