ORA-06502: PL/SQL: numeric or value error: character string buffer too small when using solution for converting long raw to varchar2

The original solution URL is: http://www.experts-exchange.com/Databases/Oracle/Product_Info/Q_20673467.html?query=TEXTRAWIO&clearTAFilter=true

will this be ?

   hex_data          VARCHAR2(32760);  -- 32767 instead of 32760
    char_data         VARCHAR2(32760); -- similarly here

Thanks

I'm with paquicuba on this one.  Try it with CLOBs instead.  The only reason I'm posting is I would suggest a couple more changes.

FUNCTION tidblobchar(v_object_id char) RETURN CLOB
and
char_data        clob;

I tried out your example in 10.2 and it didn't complain.  I can't test in 9i.

Thank you all for the inputs. I used the suggestions from paquicuba and slightwv  , it works fine, but one things is that it's kind of slow. So we have a java stored procedure to do the similar thing, the problem is the
java String can't be converted to CLOB, any idea to deal with that?
public static String rawToText(String sql_query){
        String result = "";
        try {
            Connection conn =
            DriverManager.getConnection("jdbc:default:connection:");

            PreparedStatement pstmt = conn.prepareStatement(sql_query);
           
            ResultSet rSet = pstmt.executeQuery();
            //System.out.println("selected data from tblrequestclob");

            byte[] ba = null;
            while (rSet.next()){
                // this should only go one time
                ba = rSet.getBytes(1);
            }
            // copy the byte array into a StringBuffer
            if (ba != null){
                StringBuffer sb = new StringBuffer();
                for (int i=0;i<ba.length;i++){
                    int c = ba[i];
                    sb.append((char)c);
                }
                result = sb.toString();
            }else{
                result = "";
            }
        }catch (Exception e){
            e.printStackTrace();
            result = "Exception: " + e.getMessage();
        }
       
        return result;
    }
CREATE OR REPLACE FUNCTION rawToText (sql_query IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'IN_ReadRaw.rawToText(java.lang.String) return String';

These code works fine within the VARCHAR2 limits, but has problem with larger size data. So I was trying to change the return value of the function from VARCHAR2 to CLOB, the compliler didn't complain, but when I try to use it , I got error saying :
SQL> /
ERROR:
ORA-00932: inconsistent datatypes: expected Unsupported conversion



no rows selected

Any help will be greatly appriciated!

I was trying to return CLOB as shown below:
CREATE OR REPLACE FUNCTION rawToText (sql_query IN VARCHAR2) RETURN CLOB
AS LANGUAGE JAVA
NAME 'IN_ReadRaw.rawToText(java.lang.String) return String';
/
The compilation went OK. the query results the ORA-00932: inconsistent datatypes: expected Unsupported conversion.