Re: long to varchar2

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 23 Sep 1999 15:50:29 -0400
Message-ID: <XIHqN+skm65lyS8AaQ9VqylwfwRk@4ax.com>

On Wed, 22 Sep 1999 16:44:47 -0700, Breno Gomes <brenogomesNOmeSPAM_at_computer.org> wrote:

>I successfully performed migration from FoxPro 2.5 files to
>Oracle 8.0.4.0.0.
>
>Fox memo fields were converted to long. All data are plain
>text, with 500 characters or less, due 254 fox limits.
>
>In the new application it would better to use Varchar2
>(1000) instead Long. How could I read data from long and
>write to Varchar2 ?

You can use pl/sql

begin
  for c in ( select * from <old_table_with_long> ) loop     insert into <new_table_with_varchar2(1000)>       ( col1, col2, col3, col4 )
    values
      ( c.old_col1, c.old_col2, c.old_col3, c.old_col4 );   end loop;
end;
/

eg.

SQL> desc foo

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 L                                                  LONG

SQL> desc bar
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 V                                                  VARCHAR2(1000)

SQL> select * from foo;

ID L

---------- ------------------------------------------------------------
         2 Another long value
         1 A long value

SQL> select * from bar;
no rows selected

SQL> insert into bar select * from foo; insert into bar select * from foo

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> select * from bar;
no rows selected

SQL> begin
2 for c in ( select * from foo ) loop

  3      insert into bar ( id, v )
  4      values ( c.id, c.l );

  5 end loop;
  6 end;
  8 /
PL/SQL procedure successfully completed.

SQL> select * from bar;

ID V

---------- ------------------------------------------------------------
         2 Another long value
         1 A long value

hope this helps.

chris.

>
>I appreciate your suggestions.
>
>Thanks in advance.
>
>brenogomes_at_ieee.org
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 23 1999 - 14:50:29 CDT