?
Solved

how to convert long data type to clob - error ORA-06502: PL/SQL: numeric or value error

Posted on 2007-10-08
2
Medium Priority
?
1,845 Views
Last Modified: 2008-01-09
I'm trying to copy data from long data type to clob by copying data from one table to another using pl/sql procedure.
I'm getting error - ORA-06502: PL/SQL: numeric or value error while fetching long data type.
I assume lenght exceed 32k. How can I solve this issue?
The logic is very simple:
CREATE OR REPLACE PROCEDURE convert_long
AS
 CURSOR long_text_c
   IS
      SELECT id, long_field FROM table_long_text;
BEGIN
   FOR v_long_text IN long_text_c
   LOOP
           INSERT INTO table_clob_text
                        (id,                     clob_field)
           VALUES (v_long_text.id, v_long_text.long_field);
   END LOOP;
  COMMIT;
EXCEPTION
   WHEN OTHERS  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/
0
Comment
Question by:tribuna33
2 Comments
 
LVL 9

Accepted Solution

by:
arioh earned 195 total points
ID: 20038500
CREATE OR REPLACE PROCEDURE convert_long
AS
 CURSOR long_text_c
   IS
      SELECT id, long_field FROM table_long_text;

  tRowID varchar2( 100 );
  tCLOB CLOB;
BEGIN
   FOR v_long_text IN long_text_c
   LOOP
     INSERT INTO table_clob_text ( id, clob_field )
     VALUES ( v_long_text.id, EMPTY_CLOB() )
      returning ROWID into tRowID;
     select CLOB_FIELD into tCLOB from table_clob_text where rowid = tRowID for update;
     dbms_lob.write( tCLOB, length( to_char( v_long_text.long_field ) ), 1, to_char( v_long_text.long_field ) );
   END LOOP;
  COMMIT;
EXCEPTION
   WHEN OTHERS  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 180 total points
ID: 20038632
only if you are moving data from table1.long_column to table2.clob_column (or blob) you can use function to_lob
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions185.htm#i79464
you can use it only in insert-select statement, but it's your case.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

864 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