Re: How to: Copy LONG field from one table to another

From: Marc Mazerolle <informaze_at_sympatico.ca>
Date: Wed, 03 Feb 1999 13:43:23 -0500
Message-ID: <36B898CB.DEF1B949@sympatico.ca>

A modified version of my anonymous PL-SQL Script i posted before will do this.

<cut>
drop table db_tab_columns;

create table db_tab_columns
(

OWNER                           VARCHAR2(30) NOT NULL,
TABLE_NAME                      VARCHAR2(30) NOT NULL,
COLUMN_NAME                     VARCHAR2(30) NOT NULL,
DATA_TYPE                       VARCHAR2(9),
DATA_LENGTH                     NUMBER NOT NULL,
DATA_PRECISION                  NUMBER,
DATA_SCALE                      NUMBER,
NULLABLE                        VARCHAR2(1),
COLUMN_ID                       NUMBER NOT NULL,
DEFAULT_LENGTH                  NUMBER,
DATA_DEFAULT                    LONG,
NUM_DISTINCT                    NUMBER,
LOW_VALUE                       RAW(32),
HIGH_VALUE                      RAW(32),
DENSITY                         NUMBER,
NUM_NULLS                       NUMBER,
NUM_BUCKETS                     NUMBER,
LAST_ANALYZED                   DATE,
SAMPLE_SIZE                     NUMBER);

var v_ret number;

declare
cursor cp is

    select * from dba_tab_columns;
begin

    :v_ret := 0;
    for recs in cp loop

        if (length(recs.data_default) > :v_ret) then
            :v_ret := length(recs.data_default);
        end if;
        insert into db_tab_columns values(
            recs.OWNER,
            recs.TABLE_NAME,
            recs.COLUMN_NAME,
            recs.DATA_TYPE,
            recs.DATA_LENGTH,
            recs.DATA_PRECISION,
            recs.DATA_SCALE,
            recs.NULLABLE,
            recs.COLUMN_ID,
            recs.DEFAULT_LENGTH,
            recs.DATA_DEFAULT,
            recs.NUM_DISTINCT,
            recs.LOW_VALUE,
            recs.HIGH_VALUE,
            recs.DENSITY,
            recs.NUM_NULLS,
            recs.NUM_BUCKETS,
            recs.LAST_ANALYZED,
            recs.SAMPLE_SIZE);

    end loop;
    commit;
end;
/
print v_ret
<end cut>

Regards,

Marc Mazerolle
InforMaze Technologies

Ken Rachynski wrote:

> Thomas,
>
> Thanks for the tip.
>
> If I may ask another question: How does one find out how long the longest LONG is?
>
> Thomas Kyte wrote:
>
> >
> > 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
> >
> > Anti-Anti Spam Msg: if you want an answer emailed to you,
> > you have to make it easy to get email to you. Any bounced
> > email will be treated the same way i treat SPAM-- I delete it.
>
> --
> Ken Rachynski
> Database Analyst
> krachyn_at_cadvision.com
> <http://www.cadvision.com/krachyn>
> ICQ: 3113514
Received on Wed Feb 03 1999 - 12:43:23 CST