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

Posted on 2006-04-17
Medium Priority
Last Modified: 2008-01-09
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 ()

       cursor sll_cur is

        SELECT *from employees

sll_rec sll_cur%ROWTYPE ;

r_data CLOB;


     open sll_cur;

         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;

Question by:tech_question
  • 4

Author Comment

ID: 16472603
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 !
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16472784
"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.
LVL 19

Expert Comment

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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

LVL 19

Accepted Solution

actonwang earned 2000 total points
ID: 16475039
sorry, the above was wrong as I looked at the wrong place.

>> UTL_RAW.CAST_TO_VARCHAR2(sll_rec.OracleRow)

     function UTL_RAW.CAST_TO_VARCHAR2()  normally accept RAW datatype. If you pass in other datatype, it will do implicit conversion using hextoraw function.
     The error is triggered by hextoraw function.

      Try this:
      select utl_raw.cast_to_varchar2('31') from dual

      you will get '1'

     but this:
     select utl_raw.cast_to_varchar2(' 1') from dual
     you will get error which is about the same you got.
LVL 19

Expert Comment

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

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question