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

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-

Todd Owers wrote:

> 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_at_&cs INSERT table_1 USING SELECT * from table_1
> COPY TO &un/&pw_at_&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 CET