Re: LONG column restricts CREATE/INSERT using SELECT clause

In article <4nd6v0$7s4_at_spruce.citicorp.com> Melisa Mrazik <mmrazik> writes:
>Path: nntp.teleport.com!news.structured.net!news.uoregon.edu!arclight.uoregon.edu!usenet.eel.ufl.edu!bofh.dot!newsfeed.internetmci.com!in1.uu.net!citicorp.com!usenet
>From: Melisa Mrazik <mmrazik>
>Newsgroups: comp.databases.oracle
>Subject: LONG column restricts CREATE/INSERT using SELECT clause
>Date: 15 May 1996 18:14:56 GMT
>Organization: Citicorp - CGIN
>Lines: 24
>Message-ID: <4nd6v0$7s4_at_spruce.citicorp.com>
>NNTP-Posting-Host: tngws7.gftpd.us-fl.citicorp.com
>Mime-Version: 1.0
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>X-Mailer: Mozilla 1.1N (X11; I; HP-UX A.09.03 9000/712)
>X-URL: news:comp.databases.oracle/69713-69812

>We are currently running Oracle 7.1.4 on HP-UX 9.0
>I was trying to copy a table in a user's schema with the following command:
>CREATE TABLE error2 AS SELECT * FROM error1;
>and recieved an ORA-00997 error on a LONG datatype
 

>I also tried to create table error2 and then use the following command:
>INSERT INTO error2 SELECT * FROM error1;
> and recieved the same error.
 

>Oracle support has told me that I can either use PL/SQL to convert the LONG to
>a VARCHAR2 column to successfully copy (not an option as the group wants to
>keep the LONG column) or to use the COPY command. However, the analyst was not
>able to tell me _why_ I can't copy from one table to another when a LONG column
>is in the original table. Apparently, this isn't a bug so can anyone tell me
>what's wrong with this statement?
 

>TIA,
>Melisa Mrazik
 

>-------------------------------------------------------------------
>|Melisa Mrazik, Systems Officer | "I quote others only in |
>|Citicorp, N.A. Westlake, TX | order to better express |
>|melisa.mrazik_at_citicorp.com | myself." -Michel de Montaigne|

Personally, I think it is a bug; but I guess if it is expected behavior it is not a bug, but an enhancement request. Yes, you have to use sqlplus and the copy command. PL/SQL and the Oracle kernal do not support sql operations on longs (or long raws). You can use pl/sql if the long is less than 32K. I think it is silly also. You can do it from sqlplus because it is using the OCI interface. So you can write an external program, that handles longs, but not an sql statement as you found out.

Jim Kennedy Received on Wed May 15 1996 - 00:00:00 CEST