Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Find Time Consuming SQL Statements in Oracle 10g Oracle
  • Export Oracle data and Compress at same time Oracle
  • DBMS_UTILITY PACKAGE Oracle
  • Processes Parameter decision Oracle
  • temp_use.sql diplays usage of temp ts Oracle
  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • OEM-troubleshooting on 20-MAY-08 Oracle
  • Difference between SYNC and AFFIRM Oracle
  • create database syntax Oracle
  • Btee and Bitmap Plans in Oracle 9i and higher Oracle
  • The most important Tuning Notes Oracle
  • Privilege to describe the table. Oracle
  • sid_wise_sql.sql Further explaination Oracle
  • When error comes for temporary tablespace with version <= 9i Oracle
  • dbms_job.submit example Oracle

CTAS with LONG Column for 7.x and 8 and 8i

Posted on 28-Nov-2005 By Admin No Comments on CTAS with LONG Column for 7.x and 8 and 8i

How to Copy Data from a Table with a LONG Column into an Existing Table

=======================================================================

Purpose:

========

The purpose of this article is to provide a working example of how to COPY

data from a table with a LONG column into another existing table with the

same structure definition. This document is especially useful for helping

avoid an ORA-997 error as you try to achive the above objective.

Scope & Application:

====================

This article is intended for use by anyone who is receiving an ORA-997

error when they try to create a new table with a LONG column from

an existing table using the ‘Create Table As Select’ (CTAS) command.

You receive the same ORA-997 error even when you try to INSERT data from

the table with a LONG column into another table with a LONG column

using the INSERT INTO … command.

The only way to resolve this problem is to use the SQL*Plus COPY command

as discussed in this article.

This article is applicable for Oracle versions 7.3.4 through 8.1.X.

How to Copy Data from a Table with a LONG Column into an Existing Table:

========================================================================

Example:

——–

$ sqlplus scott/tiger@otcsol1_v734

SQL> create table le

(c1 number,

c2 long);

SQL> desc le

Name Null? Type

——————————- ——– —-

C1 NUMBER

C2 LONG

SQL> insert into le values ( 123, ‘long column values’);

SQL> commit;

SQL> select * from le;

C1 C2

—– ————————————————–

123 long column values

SQL> create table le2

(c1 number,

c2 long);

SQL> desc le2

Name Null? Type

——————————- ——– —-

C1 NUMBER

C2 LONG

SQL> copy from scott/tiger@otcsol1_v734 to scott/tiger@otcsol1_v734 –

> append le2 –

> using select * from le;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

1 rows selected from scott@otcsol1_v734.

1 rows inserted into LE2.

1 rows committed into LE2 at scott@otcsol1_v734.

Notes:

——

o You must use the connect string in the FROM and TO clauses of the COPY

command as shown above.

o You must use the “-” ( dash) at the end of each line if you want to break

the command into a number of lines. Otherwise, the COPY command may fail.

o This has been tested using a V734 connect string as well as other releases.

SQL> select * from le2;

C1 C2

—– ————————————————–

123 long column values

Oracle, SQL scripts

Post navigation

Previous Post: My FTP Job Scheduling for www.pvmehta.com
Next Post: CTAS with LONG Column for 9i and higher

Related Posts

  • How to set Processes Parameter Oracle
  • cp_filesystem.sql Oracle
  • Mutating Table Error while using database trigger Oracle
  • Export Import with QUERY Oracle
  • Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository Oracle
  • How to calculate PROCESSES parameter Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • create database syntax Oracle
  • online_ts_bkup.sql Oracle
  • Which environment is used by currently running process ( Very good) Linux/Unix
  • Exadata Basics Oracle
  • How do I get Oracle to automatically start when my server boots up? Oracle
  • Jai Shree Ram Linux/Unix
  • New OFA for 11g Oracle
  • Locktree.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme