You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

ORACLE Long Character

1 reply [Last post]
Guest's picture
Guest

Is it possible to select rows from a table where the Long Character field contains a value?

CompShack's picture
User offline. Last seen 6 years 4 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: ORACLE Long Character

Not easily NO - just out of curiosity, what version are you on?

I did some googling (gotta love google) and found the following, might be helpful!

This function is from Oracle's site, it can get a SUBSTR of the LONG field. The function is limited to LONGs with fewer than 32,767 characters.

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;
/

n/a