?
Solved

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

Posted on 2006-04-17
6
Medium Priority
?
11,586 Views
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 ()
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;

0
Comment
Question by:tech_question
  • 4
6 Comments
 

Author Comment

by:tech_question
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 !
0
 
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.
0
 
LVL 19

Expert Comment

by:actonwang
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.

0
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

by:
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.
0
 
LVL 19

Expert Comment

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

Expert Comment

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

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