Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: ORA-00932: inconsistent datatypes: expected CHAR got LONG

    Code:
    SELECT    PER.LAST_NAME, PER.FIRST_NAME, RES.RESUME_IMAGE_TEXT RESUME
    FROM     EMPLOY.PERSON PER, 
             EMPLOY.RESUME_IMAGE_TEXT RES 
    WHERE    PER.ENTITY_NUM=RES.PERSON 
    AND LENGTH(TO_CHAR(RES.RESUME_IMAGE_TEXT)) > LENGTH('EmployeeID=226962')
    Huh?

    I'm assuming it's the AND Predicate...anyone got any ideas?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What datatype is RESUME_IMAGE_TEXT.RESUME_IMAGE_TEXT ?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    LONG

    Also tried

    AND TO_LOB(RES.RESUME_IMAGE_TEXT) NOT LIKE 'EmployeeID=%'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Brett Kaiser View Post
    LONG
    You can't really do anything with a LONG column, except selecting it.

    The LONG datatype has been deprecated since Oracle 8, you should really (really) convert that to a CLOB column

    Edit: you might want to try UTL_RAW.CAST_TO_VARCHAR2()

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're freakin kidding me

    Code:
    CREATE OR REPLACE FUNCTION LONG_TO_CHAR( in_rowid rowid,in_owner
    varchar,in_table_name varchar,in_column varchar2) 
    RETURN varchar AS 
    /*
    CREATE BY: D.Jenkins
             Date: 28-Apr02003 
    
             Use:  Long to varchar2 conversion,
                   to allow a substring on long column in the table; 
      
             Notes: 
             Errors out with varchar > 32767        
             ORA-06502: PL/SQL: numeric or value error: character string
                buffer too small 
    
             Synonyms and Grants:
             create public synonym LONG_TO_CHAR for
                synergen.CDBF_LONG_TO_CHAR; 
             grant execute as required 
             or
             grant execute long_to_char to public; 
                
    */
    
    text_c1 varchar2(32767);
    sql_cur varchar2(2000);
    --
    begin
      sql_cur := 'select '||in_column||' from
    '||in_owner||'.'||in_table_name||' where rowid =
    '||chr(39)||in_rowid||chr(39);
      dbms_output.put_line (sql_cur);
      execute immediate sql_cur into text_c1;
    
      text_c1 := substr(text_c1, 1, 4000);
      RETURN TEXT_C1;  
    END;
    /
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    looks like LONG is not covered by LENGTH(), or any of its brothers. There is probably a system function similar to DATALENGTH() in SQL Server, but I do not know it off the top of my head. Try converting the column to CLOB by the TO_CLOB() function.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Illegal Use of LONG Datatype

    Illegal Use of LONG Datatype

    AND UTL_RAW.CAST_TO_VARCHAR2(RES.RESUME_IMAGE_TEXT) NOT LIKE TO_CHAR('EmployeeID=%')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Do migrate to CLOB anyway. It will save you a lot of headaches in the future!

Posting Permissions

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