asked on

ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

I am getting the above error here. How do I convert a rowtype to a varchar

DBMS_LOB.writeappend(r_data, length(sll_rec.OracleRow), UTL_RAW.CAST_TO_VARCHAR2 (sll_rec.OracleRow)); ------------------------------------------------------------------------------------------------------------------

function createOracleFile ()
RETURN CLOB IS

       cursor sll_cur is

       SELECT *from employees

sll_rec sll_cur%ROWTYPE ;

r_data CLOB;

begin

open sll_cur;

       LOOP
       fetch sll_cur into sll_rec;
       exit when sll_cur%NOTFOUND;

-- I am getting an error here
       DBMS_LOB.writeappend(r_data, length(sll_rec.OracleRow), UTL_RAW.CAST_TO_VARCHAR2 (sll_rec.OracleRow));

       END LOOP;

       return r_data;

tech_question

ASKER

I found what the error is, I had to add this statement but still I do not get the entire data that is being returned by the query , dbms_lob.createtemporary(lob_loc => r_data, cache=> TRUE, dur=> DBMS_LOB.call) !

How can I get the entire data returned by the query !

Mark Geerlings

"How do I convert a rowtype to a varchar?" I'm not sure that is supported in Oracle, are you?

If what you want is a varchar (or varchar2) variable (or variables) that contain the contents of a particular database row, one option would be to declare varchar2 variables corresponding to each column in the table, then change your cursor from "select * from..." to: "select [column1], [column2], etc. from...", and change your "fetch" to:
fetch sll_cur into [variable1], [variable2], etc.

Acton Wang

ROW type only can convert with varchar(char) or nvarchar(nchar). Other types as date, number etc will fail to cast into row directly.

ASKER CERTIFIED SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Acton Wang

%ROWTYPE hardly convert with raw or varchar , you might add each column value in as markgeer suggested.

Acton Wang

I think it is related to your other question. I believe that you already got it and wrote your column value directly.
:)