How to Convert a Long to Varchar2 (文档 ID 228532.1)

oracle 中怎样转化long类型，到varchar2

How to Convert a Long to Varchar2 (文档 ID 228532.1)

PURPOSE
-------

The purpose of this document is to provide a sample on how to convert a long to 
a varchar2.
 
SCOPE & APPLICATION
-------------------

This document is intended for a developer that has PL/SQL and SQL background.

Here is a sample on how to convert the long to a varchar2.
-----------------------------
 
SQL> create table long_test (field1 long, field2 varchar2(1000));
SQL> insert into long_test values ('Hello' , 'World');
SQL> set serveroutput on;
SQL> declare
  2     v1 varchar2(500);
  3  begin
  4     select field1 into v1 from long_test;
  5     dbms_output.put_line(v1);
  6* end;
SQL> /
Hello

PL/SQL procedure successfully completed.

Another test is the following:

SQL> insert into long_test (Field1 ) values ('Hello');

1 row created.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2         v1 varchar2(500);
  3      begin
  4         select field1 into v1 from long_test;
  5   dbms_output.put_line(v1);
  6  update long_test set field2 = v1;
  7* end;
SQL> /
Hello

PL/SQL procedure successfully completed.

SQL> select * from long_test;

FIELD1
--------------------------------------------------------------------------------
FIELD2
--------------------------------------------------------------------------------
Hello
Hello

***************************************************************************

If you have multiple rows that you need to convert from a long to varchar2, here is
an example on how to do this:

create table delete_me (field1 long, field2 varchar2(100));
SQL> insert into delete_me (field1) values ('Row 1');
SQL> insert into delete_me (field1) values ('Row 2')
SQL> insert into delete_me (field1) values ('Row 3')
SQL> insert into delete_me (field1) values ('Row 4')
SQL> insert into delete_me (field1) values ('Row 5')

SQL> select Field1 from delete_me;

FIELD1
----------------------------------------------------------------
Row 1
Row 2
Row 3
Row 4
Row 5


create table delete_me2 (NewField VARCHAR2(200));


declare
   v1 varchar2(100);
   CURSOR c_Select IS select Field1 from delete_me;
begin
   Open c_Select;
   LOOP
      Fetch c_Select into v1;
      Exit when c_Select%NOTFOUND;
      Insert into delete_me2 values (v1);
   END LOOP;
   Close c_Select;
END;


SQL> select * from delete_me2;

NEWFIELD
----------------------------------------------------------------
Row 1
Row 2
Row 3
Row 4
Row 5