Re: LONG datatype

A copy of this was sent to "pai" <p_at_zainet.com> (if that email address didn't require changing) On Wed, 24 Mar 1999 18:02:41 -0500, you wrote:

>Oracle dis-allow Long datatype included in a sub-query statement, I wonder
>if there is any alternative for this?
>
>Example like ,
>
>first,
>create table sales
>(
> department char(10),
> detail long
>);
>
>create unique index sales_dep on sales (department);
>
>then,
>rename table sales to sales_temp;
>create table sales
>(
> department char(10),
> salesperson char(10),
> detail long
>);
>
>insert into sales select
>department,
>'BILL',
>detail
>from sales_temp;
>
>drop table sales_temp;
>
>create unique index sales_dep on sales (department, salesperson);
>
>
>The above senario does not work because column 'detail' is a long
>datatype.
>
>Any suggestion will be appreciated.
>
>

look at the sql*plus copy command. It will do it. You need a sql*net connect string that loops back to your local database (not a dblink, a sqlplus connect string, you need to be able to "sqlplus scott/tiger_at_yourdatabase"...  

For example, I just:  

create table foo
( The_Whole_View varchar2(65),

  TextLength       number,
  TheText          Long )

which is a table, sort of like all_views (which has a long)... Then I:  

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

So the sqlplus command transformed the table for me (the columns are not the same). Also, I could have used a where clause to pick off just some rows.  

You'll want to set  

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

in plus before doing this. see the manual for all the options....    

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation