copy command in SQL*Plus 8.1.7

From: Dino Hsu <dino1_nospam_at_ms1.hinet.net>
Date: Sat, 28 Jul 2001 18:29:52 +0800
Message-ID: <5g45mtsh2ih91j5p79ppqfrklddnd6l5kh@4ax.com>

Dear all,

I encountered strange errors when using copy command in SQL*Plus 8.1.7 (Oracle 8.1.7 EE for NT/2K). For example,

SQL> copy from dm_admin/dm_admin_at_twdm_prod replace tb_products using select * from tb_products;

Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80)

ERROR:
ORA--1663296096: Message -1663296096 not found; product=RDBMS; facility=ORA

Note that the error number is negative.   

I checked Metalink and found this: (Oracle technical support also says this is a known but not fixed bug)

<METALINK begin>

Bookmark Fixed font Go to End

Doc ID: Note:145366.1
Subject: COPY Command in SQL*Plus Causes ORA--3929178 or Dr. Watson Type: PROBLEM
Status: REVIEWED
 Content Type: TEXT/PLAIN
Creation Date: 14-MAY-2001
Last Revision Date: 14-MAY-2001  

---

This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article.

---

Problem Description

---

You are using the GUI version of SQL*Plus on a Windows PC and are attempting to execute a copy command using syntax similar to:

SQL> copy from username/password_at_database -

     insert emp -
     using select * from myemp;

The copy statement fails with the following error:

ERROR:
ORA--3929178: Message -3929178 not found; product=RDBMS; facility=ORA

-or-

ORA--3923074: Message -3923074 not found; product=RDBMS; facility=ORA

-or-

In other cases, the COPY command fails with a Dr. Watson, gpf, or other
random error, such as "ORA--6449424" or "ORA-65535."

Solution Description

---

There is a bug in SQL*Plus that causes random errors and Dr. Watson's when using the COPY command in the GUI based version of the tool. Until
the bug is fixed, there are several workarounds. Any of these solutions
will allow successful update to your table.

1. Execute the COPY command using the character based executable - i.e. sqlplus.exe. This can be done by going to Start -> Run -> sqlplus.exe, or by executing the command in an MS-Dos window.
2. Use one of the versions of SQL*Plus that doesn't have the bug: versions 8.0.6 or 8.1.5.
3. Use the CREATE TABLE syntax instead of the COPY command, for example:

     SQL> create table myemp
          as select * from emp;

Explanation

---

Bug 1504702 is currently unresolved and only occurs in SQL*Plus versions
8.1.6 and 8.1.7 and only in the GUI based version of the tool. The bug
is published and is viewable via Metalink.

This bug is encountered when executing SQL*Plus from the Windows Programs
menu. For example: selecting Start -> Programs -> Oracle for Windows ->
SQL*Plus, or executing "sqlplusw.exe" from a command line.

Until a fix is released, one of the above workarounds should be used for
updating/creating tables when using SQL*Plus.

References

---

Bug 1504702, SQLPLUS COPY COMMAND FAILS WITH DR. WATSON, ORA-65535 OR ORA--6449424 SQL*Plus User's Guide and Reference

Additional Search Words

---

Sqlplus, SQLPlus, OEM, copy
.  

---

 

 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.

<METALINK end>

Therefore, I have the following choices: 1.use create table as... instead of copy... 2.use SQL*Plus 8.0.5 when using copy

I think this problem only happens in Oracle NT/2K, but not Unix's. Anyone has comments? Thanks.

Dino Received on Sat Jul 28 2001 - 05:29:52 CDT