7 Replies Latest reply: Dec 17, 2013 6:04 AM by Prince_Kaileena RSS

    ora-00932 inconsistent datatypes expected udt got char

    Prince_Kaileena

      I am getting the beloe exception while insert:

      ora-00932 inconsistent datatypes expected udt got char

      How to overcome this issue?

       

      Query:

      INSERT INTO syncdiffassociatedobject

                  (syncdiffassociatedobjectid,

                   sdao2syncdiff,

                   sdao2dimobject,

                   sdao2object,

                   sdao2upk,

                   sdao2udmclass,

                   NAME, nestedlevel, behaviour

                  )

           VALUES (seq_sas.NEXTVAL,

                   TREAT (:b1 AS o_sync_differenceobject).sdo2syncdiff,

                   NVL (TREAT (:b2 AS o_sync_differenceobject).dimobjectid,

                        (SELECT udmclass2dimobject

                           FROM udmclass

                          WHERE udmclassid =

                                   TREAT (:b8 AS o_sync_differenceobject).udmclassid)

                       ),

                   TREAT (:b3 AS o_sync_differenceobject).objectid,

                   TREAT (:b4 AS o_sync_differenceobject).upk,

                   TREAT (:b5 AS o_sync_differenceobject).udmclassid,

                   TREAT (:b6 AS o_sync_differenceobject).NAME, 0, :b7

                  );

        • 1. Re: ora-00932 inconsistent datatypes expected udt got char
          _Karthick_

          As a first step describe your table syncdiffassociatedobject for us please.

          • 2. Re: ora-00932 inconsistent datatypes expected udt got char
            Roger

            And as a second...do NOT use NVL when parameter 2 is a select statement.

            • 3. Re: ora-00932 inconsistent datatypes expected udt got char
              Prince_Kaileena

              desc syncdiffassociatedobject;

               

              Column NameIDPkNull?Data TypeDefaultHistogramEncryption AlgSalt

               

               

              SYNCDIFFASSOCIATEDOBJECTID11NNUMBERYes
              SDAO2SYNCDIFF2NNUMBERYes
              SDAO2DIMOBJECT3NNUMBERYes
              SDAO2OBJECT4YNUMBERYes
              SDAO2UPK5YVARCHAR2 (240 Char)Yes
              SDAO2UDMCLASS6YNUMBERYes
              NAME7YVARCHAR2 (240 Char)Yes
              OBJECTCONTEXT8YVARCHAR2 (2000 Char)Yes
              NESTEDLEVEL9NNUMBERYes
              BEHAVIOUR10NNUMBERYes
              • 4. Re: ora-00932 inconsistent datatypes expected udt got char
                rp0428

                I am getting the beloe exception while insert:

                ora-00932 inconsistent datatypes expected udt got char

                How to overcome this issue?

                Hmmm - don't give it a UDT (user defined type) when the column datatype is a NUMBER?

                 

                This is what you are using for that second column:

                TREAT (:b1 AS o_sync_differenceobject).sdo2syncdiff,

                So you are trying to use 'A.B' since the '.sdo2syncdiff' is OUTSIDE the parentheses. See the TREAD statement in the doc

                http://docs.oracle.com/cd/E11882_01/server.112/e41084.pdf

                 

                All of the other usages of TREAT have the same issue.

                 

                Forget the INSERT and just use a simple SELECT until you get the syntax of TREAT correct. Then use that in your INSERT.

                • 5. Re: ora-00932 inconsistent datatypes expected udt got char
                  gaverill

                  If your calling/binding code to the INSERT 'knows' what these objects are, in a polymorphic sense, then move your TREAT expressions (if you even need them anymore) outside of the INSERT statement and do them when binding to scalar-only parameters; e.g.:

                   

                  ...

                  insertSql := q'[INSERT INTO syncdiffassociatedobject

                              (syncdiffassociatedobjectid,

                               sdao2syncdiff,

                               sdao2dimobject,

                               sdao2object,

                               sdao2upk,

                               sdao2udmclass,

                               NAME, nestedlevel, behaviour

                              )

                       VALUES (seq_sas.NEXTVAL,

                              :sdo2syncdiff,

                              COALESCE(:dimobjectid,

                                    (SELECT udmclass2dimobject

                                       FROM udmclass

                                      WHERE udmclassid = :udmclassid)

                                   ),

                               :objectid,

                               :upk,

                               :udmclassid,

                               :NAME, 0, :b7

                              )]';

                  ...

                  execute immediate

                       insertSql

                  using

                      TREAT (var_b1 AS o_sync_differenceobject).sdo2syncdiff,

                      TREAT (var_b2 AS o_sync_differenceobject).dimobjectid,

                      TREAT (var_b8 AS o_sync_differenceobject).udmclassid,

                      TREAT (var_b3 AS o_sync_differenceobject).objectid,

                      TREAT (var_b4 AS o_sync_differenceobject).upk,

                      TREAT (var_b5 AS o_sync_differenceobject).udmclassid,

                      TREAT (var_b6 AS o_sync_differenceobject).NAME, 0, var_b7;

                  ...

                   

                  Gerard

                  • 6. Re: ora-00932 inconsistent datatypes expected udt got char
                    rp0428
                    If your calling/binding code to the INSERT 'knows' what these objects are, in a polymorphic sense, then move your TREAT expressions (if you even need them anymore) outside of the INSERT statement and do them when binding to scalar-only parameters; e.g.:

                    That isn't possible. I don't have any 'calling/binding' code. OP is the one with code and the issue.

                    • 7. Re: ora-00932 inconsistent datatypes expected udt got char
                      Prince_Kaileena

                      Strange to here that,same query is running fine in other environment.