Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • DBMS_STATS Metalinks Notes Oracle
  • sid_wise_sql.sql Further explaination Oracle
  • eplan.sql Oracle
  • To see mem usage and CPU usage system wide. Linux/Unix
  • oracle 11g RAC on vmware Oracle
  • Drop all SPM baselines for SQL handle Oracle
  • Load SPM baseline from AWR Oracle
  • Is It Recommended To Apply Patch Bundles When PSU Is Available? -ID 743554.1 Oracle
  • get_vmstat_solaris Oracle
  • Very clear article about oracle dataguard Oracle
  • Drop specific SQL plan baseline – spm Oracle
  • Good RAC & Standby Notes Oracle
  • PLSQL Table Syntax 1 Oracle
  • load SPM baseline from cursor cache Oracle
  • findinfo.sql (SQL for getting CPU and Active session info) 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

  • get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause Oracle
  • Virtual Indexes in Oracle Oracle
  • get_vmstat_solaris Oracle
  • Jai Shree Ram Oracle
  • Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE fplan.sql Oracle
  • AWR license 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
  • Search and replace editor command in vi Linux/Unix
  • metalink all dynamic view reference notes. Oracle
  • New OFA for 11g Oracle
  • Oracle 10g Installation/Applying Patches Tips Oracle
  • Read CSV File using Python Python/PySpark
  • DBMS_UTILITY.ANALYZE_SCHEMA Oracle
  • How to collect CPU usage on Linux using Shell script Linux/Unix
  • ORA-8031 issue and solution if it is occuring due to truncate. Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme