5 Replies Latest reply on Nov 27, 2007 1:18 PM by APC

    ORA-00932: inconsistent datatypes: expected - got CLOB

    Patrick S
      i am writing the following function :

      FUNCTION get_deal_rights(p_deal_id NUMBER,p_type_code NUMBER) RETURN CLOB is

      CURSOR deal_properies_cur(p_deal_id NUMBER) IS
      SELECT description
      FROM properties
      WHERE deal_id = p_deal_id

      deal_rights      CLOB := null;

      BEGIN

      DBMS_LOB.CREATETEMPORARY(deal_rights,true);

      FOR deal_properies_rec IN deal_properies_cur(p_deal_id LOOP

      deal_rights := deal_rights ||to_clob(deal_rights_rec.rights) ||', ' ;

      END LOOP;

      RETURN deal_rights;

      END get_deal_rights;

      When i call this funtion from an sql statement to get the property descriptions of a deal i get the following error : ORA-00932: inconsistent datatypes: expected - got CLOB.

      Please advice ..
        • 1. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
          APC
          Please advice ..
          Yeah, well I would have thought something like this would be more appropriate:
          [pre[
          FUNCTION get_deal_rights(p_deal_id NUMBER,p_type_code NUMBER) RETURN CLOB is

          CURSOR deal_properies_cur(p_deal_id NUMBER) IS
          SELECT description
          FROM properties
          WHERE deal_id = p_deal_id

          deal_rights CLOB := null;

          BEGIN

          DBMS_LOB.CREATETEMPORARY(deal_rights,true);

          FOR deal_properies_rec IN deal_properies_cur(p_deal_id)LOOP

          dbms_lob.writeappend( deal_rights, length(deal_rights_rec.description), deal_rights_rec.description);

          END LOOP;

          RETURN deal_rights;

          END get_deal_rights;

          Bearing in mind, of course, that the code you have posted is not consistent and wouldn't run even if you had got the syntax correct.

          Cheers, APC

          Blog : http://radiofreetooting.blogspot.com/
          • 2. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
            Patrick S
            ok i modified my code but when i run this statement :

            SELECT get_deal_rights(283) FROM DUAL

            I get this message: ORA-24813: cannot send or receive an unsupported LOB ??
            • 3. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
              APC
              I get this message: ORA-24813: cannot send or receive
              an unsupported LOB ??
              Hmm, well this error occurs when the versions of the client and server are not compatible. Does that fit your case?

              Cheers, APC

              Blog : http://radiofreetooting.blogspot.com/
              • 4. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                Patrick S
                That was exactly the problem. I've tried on the correct client and it worked.
                But i want to use this function in a query that selects distinct values and i'm getting an error. I've read i cannot user distinct with CLOB columns.

                any advice for a workaround or solution ??
                • 5. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                  APC
                  I've read i cannot user distinct with CLOB columns.
                  Well, that's what the docs say.
                  any advice for a workaround or solution ??
                  That rather depends upon precisely what you want to do. We can use the DBMS_LOB.compare() method to compare two CLOB values. So you write something which will figure out a set of distinct CLOBs.

                  However, you ought to consider the fact that if this was a simple and efficient thing to code Oracle would already have done it. So it's likely to be hard to code and/or likely to nadger your server. If this is a one-off exercise you may be happy to take that hit. But if this is something you want to do on a regular basis you would be better advised to reconsider your data-loading processes so as to avoid the need to do this regularly.

                  Cheers, APC

                  Blog : http://radiofreetooting.blogspot.com/