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

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
toddo_at_gcr1.com Received on Tue Nov 10 1998 - 00:00:00 CST