Insert data in Table containing Long column in Oracle database(OFF TOPIC)

We can use the copy command in sqlplus to insert data in table containing a long column in the database. This will only work for long column and will not work for long raws.

First, we need to login to the database using sqlplus


> SQLPLUS tkyte/tkyte@aria 


we have the below table with the long column


create table foo
( The_Whole_View   varchar2(65),
  TextLength       number,
  TheText          Long )
/


Next, we can set the below set commands to define the parameters for the copy command to be used to insert into the table which has the long column

• set arraysize N -- the number of rows the copy command will copy with each fetch
• set long N -- the size of your longest long
• set copycommit M -- the number of fetches to do before commit (N*M rows!!)

Then we can use the copy command to insert data into the table which has the long column

SQL> copy from tkyte/tkyte@aria insert foo (the_whole_view, textlength, thetext ) using select owner||'.'||view_name, text_length, text from all_views;

This article is also available in the link Moving LONGS Around