Re: LONG's and CLOB's

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 05 Oct 1999 17:02:56 -0400
Message-ID: <1WT6N7P8fVLbI542gqrhjYtrLpvc@4ax.com>

On Tue, 05 Oct 1999 18:34:51 GMT, Alex A. <albu_at_mailcity.com> wrote:

>I am trying to populate a table with data from another table. The source
>table has a LONG field. I have transferred the data using the SQL*Plus
>COPY command (since "insert ... select ..." doesn't work for LONG's).
>
>To be able to copy the LONG fields I have done a:
>set LONG 30000
>All data from the LONG fields got through, but it has a lot of junk
>appended to it (it looks like LONG fields have been padded with some
>characters up to length 30000). How can I avoid that? If I don't do the
>set LONG 30000, the LONG fields are truncated to 80 characters.
>

>My other question is how can I populate CLOB fields from LONG fields
>(COPY complained about type mismatch).

Use the to_lob() function.

eg.

clbeck_at_8i> desc foo

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

clbeck_at_8i> desc bar
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 C                                                  CLOB

clbeck_at_8i> insert into foo values ( 1, 'dsfsdfsdfsd' ); 1 row created.

clbeck_at_8i> insert into foo values ( 2, rpad( '*', 30000, '*' ) ); 1 row created.

clbeck_at_8i> select * from foo;

        ID L

---------- ------------------------------------------------------------
         1 dsfsdfsdfsd
         2 ************************************************************
           ************************************************************
           <lots more stuff here>
           ****************************************

clbeck_at_8i> insert into bar
  2 select id, to_lob( l )
  3 from foo;

clbeck_at_8i> select * from bar;

        ID C

---------- ------------------------------------------------------------
         1 dsfsdfsdfsd
         2 ************************************************************
           ************************************************************
           <again, lines snipped>
           ****************************************

hope this helps.

chris.

>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
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 Tue Oct 05 1999 - 16:02:56 CDT