SQL*Plus: COPY command, LONG datatype, and ORA-01008

[Todd Owers]

I am writing a script to transfer data from an Oracle Lite 3.0 database to
an Oracle 7 database. I don't want to use Oracle's replication feature
because it appears to be more complex than my situation requires (installing
Mobile Agents, etc). I think a simple SQL*Plus script using the COPY
command will provide a quick-and-dirty, but satisfactory, solution.

The problem is that I am getting an "ORA-01008: not all variables bound"
error on tables with a LONG column. On tables without a LONG column,
everything works fine. The documentation states that this error is caused
by an SQL statement containing substitution variables being executed without
all variables bound. But I don't understand why the error occurs only on
tables with LONG columns. It seems to me that if a variable is unbound, the
error would occur on all tables.

Here is the script. It is run from SQL*Plus 3.3.4.0.0 connected to an
Oracle Lite 3.0.6.2.6 database. It prompts for the username, password, and
connect string of the target Oracle 7 database. The table names and
structures in both databases are identical:

SET LONG 2000000000
SET LONGCHUNKSIZE 1000
--
-- Get username/password for server
--
CLEAR SCREEN
PROMPT Enter the following information for the SERVER database:
ACCEPT un PROMPT 'User Name: '
ACCEPT pw PROMPT 'Password: ' HIDE
ACCEPT cs PROMPT 'Connect String: '
--
-- Process each table
--
COPY TO &un/&pw@&cs INSERT table_1 USING SELECT * from table_1
COPY TO &un/&pw@&cs INSERT table_2 USING SELECT * from table_2
etc... There are 14 tables in all, 2 of which contain LONG columns.

As mentioned above, this script works fine for tables without a LONG column,
but gives the ORA-01008 error for tables with a LONG column. Thanks in
advance for your help and suggestions.

Todd Owers
to...@gcr1.com

[Bhanu Gogineni]

Todd,
I have never faced this kinda' problem, but here's an educated guess.
Since COPY ... is actually an SQLPLUS command (not a SQL statement), I think the
SQLPLUS session internally creates SQLPLUS variables (or bind variables) for
each field in the table , to complete the command.
If the above is true, I can see why you are erring out on a LONG column, since
SQLPLUS variables have a size limit (not sure what the limit is) and the size of
a LONG datatype exceeds the limit.

Hope that helps.

Bhanu-

[Thomas Bartos]

This is interesting... The copy command is copying data as if it were doing
an insert on your table. Applying intermintent commits during large data
uploads may help. Although this doesn't exactly match the error you are
getting.

on the remote database try
set copycommit 1 - copycommit tells SQL*Plus how many batches should be
committed at one time
set arraysize 500 -- This determines the number of records that will be
retrieved in each "batch". Adjust this number as necessary.

then run your coy scripts on your local database

COPY TO &un/&pw@&cs INSERT table_1 USING SELECT * from table_1
COPY TO &un/&pw@&cs INSERT table_2 USING SELECT * from table_2

hope this helps
- Thomas Bartos

[Todd Owers]

As a follow up, I think I found the reason why it doesn't work. One of the
restrictions on the LONG datatype is that a LONG column cannot be used when
inserting into a table with a query (i.e., INSERT INTO table_name SELECT
col_1, col_2 FROM table_2). In essence, this is what the SQL*Plus COPY
command is doing: INSERTing with a SELECT statement.

This being the case, however, I would have expected to receive the
"ORA-00997: illegal use of LONG datatype" error, instead of the ORA-01008.

Thanks to all who replied.

Todd

Todd Owers wrote in message <72agf6$eqo$1...@nntp.gulfsouth.verio.net>...
> [snip]

>The problem is that I am getting an "ORA-01008: not all variables bound"
>error on tables with a LONG column. On tables without a LONG column,
>everything works fine.

> [snip]
>Here is the script.
> [snip]

>COPY TO &un/&pw@&cs INSERT table_1 USING SELECT * from table_1
>COPY TO &un/&pw@&cs INSERT table_2 USING SELECT * from table_2

[Andrew Protasov]

Hi Todd,

You are wrong. COPY command does not insert ... select.
It uses two different connections and makes select in the first
and insert in the second. It works with LONG columns. But
it does not work with LONG RAW columns.

I made FREE utility Table Copy which works in the similar way but
supports LANG RAW:

http://protasov.simplenet.com

Andrew Protasov

[Todd Owers]

Through trial and error, I uncovered the reason for the ORA-01008 error. It
was the setting of the SQL*Plus LONG environment variable. This variable
sets the maximum width, in characters, for displaying and copying LONG
values. The default is 80, which is too small. Initially, I increased it
to 2000000000 (2 GB, the maximum value), and the error occurred. When I
reduced it to 2000000 (2 MB), the error disappeared. In my situation, 2M is
sufficiently large to hold all the data.

Thanks to all who responded.

Todd

Todd Owers wrote in message <72agf6$eqo$1...@nntp.gulfsouth.verio.net>...
[snip]
>The problem is that I am getting an "ORA-01008: not all variables bound"
>error on tables with a LONG column. On tables without a LONG column,
>everything works fine.
[snip]
>Here is the script.
[snip]
>

>SET LONG 2000000000
>SET LONGCHUNKSIZE 1000
>--