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

Posted on 2006-04-17
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

    Author Comment

    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=> !

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

    Expert Comment

    "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

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

    LVL 19

    Accepted Solution

    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

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

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    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.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now