Re: How to copy LONG RAW data?

From: Michael T. Smith <mtsmith_at_us.oracle.com>
Date: Thu, 23 Dec 1999 10:13:21 -0600
Message-ID: <83te7h$msk$1@inet16.us.oracle.com>

The COPY command is one way to get long data from one table to another. This will work remotely as well. The COPY command will not work with long raws, however. The COPY command can be used on long of any length.

Please note, long data greater than 64k will be truncated after the 65535th character. This is a SQLPlus limitation.

Here is the usage of the COPY command:

COPY FROM <db> TO <db> <opt> <table> {(<cols>)} USING <sel>

<db>    : database string, e.g., scott/tiger_at_d:chicago-mktg
<opt>   : ONE of the keywords:  APPEND, CREATE, INSERT, or REPLACE
<table> : name of the destination table
<cols>  : a comma-separated list of destination column aliases
<sel>   : any valid SQL SELECT statement

A missing FROM or TO clause uses the current SQL*Plus connection.

Below is a sample script for performing a COPY with longs. You will need to customize this to fit your particular environment.

cut here ----------------------------

REM You can set long to any large value as long as it is REM larger than the max length of any long column in the table. REM Exception is that sqlplus commands cannot handle a long REM greater than 64k. The long column will always be truncated REM at 64k.
set long 2000000000
REM commit after one batch of recordS
set copycommit 1
REM 100 records per batch
set arraysize 100

REM copy is a SQL*Plus command and needs the continuation character REM "-" for multiple lines.
REM The "@P:" tells Oracle to use pipes and the local Database REM Use the network syntax, even if copying on the local database.

COPY from scott/tiger_at_t:otchp:V714 -
CREATE newlong(id, text1) -
USING select id, longtext from longtab

cut here ---------------------------- EXAMPLES:

There are two machines on the network.

TEST1 and TEST2.
Both machines have 7.2.3 instances.
TEST1 instance has a table called oldlong with the following definition: oldlong col1 varchar2(10) longtext long oldlong has one row in it.
1. These are examples with the create option. The destination table is not precreated.

On TEST1 doing the following COPY command will give the noted results:

copy to scott/tiger_at_t:TEST2:V723 -
create newlong (col1, longtext) -
using select col1,longtext from oldlong; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

copy to scott/tiger_at_t:TEST2:V723 -
create newlong -
using select col1,longtext from oldlong; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

2. These are examples with the insert option where the destination table is created with more columns that the originating table.

On TEST2, do the following to precreate the destination table: create table newlong (col1 varchar2(10),

                      col2 varchar2(5),
                      longtext long);

On TEST1 doing the following COPY command will give the noted errors:

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong (col1, longtext) -
using select * from oldlong;
CPY0007: Select list has fewer columns than destination table

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong (col1, longtext) -
using select col1,longtext from oldlong; CPY0007: Select list has fewer columns than destination table

NOTE: These fail with CPY0007 errors. If you desire the destination

       table to have more columns than the originating table, you
       will have to do this in a two step process.

       A.  Create the table with the same number or less columns than
           the originating table.
       B.  Alter the destination table after copy to add the additional
           columns needed.

3. Here are examples of using the insert options with the

destination table precreated.

On TEST2, do the following to precreate the table:

create table newlong (col1 varchar2(10),

                      longtext long);

on TEST1 doing the following COPY command will give the noted results:

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong (col1, longtext) -
using select col1,longtext from oldlong; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong -
using select col1,longtext from oldlong; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong (col1, longtext) -
using select * from oldlong;
Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

4. Here are examples using the insert option when the destination

table is precreated with less columns that the originating table.

On TEST2 do the following to precreate the destination tables: create table newlong (longtext long);

On TEST1 doing the following COPY command will give the noted results:

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong -
using select longtext from oldlong;
Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong (longtext) -
using select longtext from oldlong;
Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 1) Maximum long size is 1000000. (long is 1000000)

1 rows selected from DEFAULT HOST connection. 1 rows inserted into NEWLONG.
1 rows committed into NEWLONG at scott_at_t:TEST2:V723.

5. Here are examples using the COPY with Long Raw columns.

TEST1 instance has a table called oldlong with the following definition: oldlong col1 varchar2(10)

longrawtext long raw
oldlong has one row in it.

copy to scott/tiger_at_t:TEST2:V723 -
create newlong (col1,longrawtext) -
using select * from oldlong;
ORA-00932: inconsistent datatypes

copy to scott/tiger_at_t:TEST2:V723 -
create newlong -
using select * from oldlong;
ORA-00932: inconsistent datatypes

precreate table newlong:
create table newlong (col1 number, longrawtext long raw);

copy to scott/tiger_at_t:TEST2:V723 -
insert newlong (col1,longrawtext) -
using select * from oldlong;
ORA-00932: inconsistent datatypes

"Anna Sotnichenko" <annasony_at_home.com> wrote in message news:38616452.F96D2B98_at_home.com...
> I use Oracle 7.3.4 under NT.
> I need to copy a record containing a field of LONG RAW type
> Statement
>
> insert into ....
> select ... from ...
>
> does not work (which is an expected result since this is exactly what is
> written in documentation).
>
> I read that one should use C for performing such operations
> (http://osi.oracle.com/~tkyte/Misc/MoveLongs.html)
> The problem is that I need to do it on a stored procedure.
> Any suggestions?
>
> Thanks in advance.
>
> Anna Sotnichenko.
>
>
Received on Thu Dec 23 1999 - 10:13:21 CST