ORA-00997: How to copy the data from a table to another using long type

[Paolo Quaglia]

Hi to all
I have created this table with a long data type

CREATE TABLE content (
id_content number(16) NOT NULL,
data_contenuto date NOT NULL,
xml long
);

My intention is to add a column before the xml field

I have created another table with the new field

CREATE TABLE content2 (
id_content number(16) NOT NULL,
data_contenuto date NOT NULL,
newfield varchar2(255),
xml long
);

Now I HAVE TRIED TO COPY the vals from content to content2 using:

INSERT INTO content2 SELECT * FROM content

But Oracle says:

ORA-00997: illegal use of LONG datatype

How can I copy the data of a table with long to another diffirent
table??????

Thanks very much to all!!
Paolo

[Paul Brewer]

"Paolo Quaglia" <paoloq...@hotmail.com> wrote in message
news:arj0it$29rk$1...@stargate1.inet.it...

Version?

The LONG answer to this could also be long, I'm sad to say.
But another answer might be to CLOB the thing.

And by the way, I do wish Oracle would practise as they preach, and remove
longs from the data dictionary.

Regards,
Angry of Tunbridge Wells.

[Paolo Quaglia]

I have Oracle 8.1.7
I solved my problem Using the copy command:
in my example the right answer could be:

COPY FROM contentoracle/contentoracle@ora8 INSERT
content2(id_content,data_contenuto,xml) USING select
id_content,data_contenuto,NULL,xml FROM content;

But I Agree with you that long could be replaced with clob object .... but I
have this DB ;-((

Any way, Thanks very much

Paolo

"Paul Brewer" <pa...@paul.brewers.org.uk> ha scritto nel messaggio
news:3ddd6...@mk-nntp-1.news.uk.worldonline.com...

[Holger Baer]

I'm not sure if I missed something, but according to your original
post you where trying to insert content.xml into content2.newfield.

I think the two datatypes just don't mix. If you had used
insert into content2 select id_content, data_contenuto, null, xml from
content;

this should have worked as well - but I might stand corrected there.

Regards,
Holger

--
------------------------------------------------------------------
Holger Baer | e-mail: holge...@science-computing.de
science+computing ag | www: http://www.science-computing.de
Hagellocher Weg 71-75 | phone: +49 (0)7071-9457-587
D-72070 Tuebingen | fax: +49 (0)7071-9457-110
------------------------------------------------------------------

[Holger Baer]

Holger Baer wrote:
> I'm not sure if I missed something, but according to your original
> post you where trying to insert content.xml into content2.newfield.
>
> I think the two datatypes just don't mix. If you had used
> insert into content2 select id_content, data_contenuto, null, xml from
> content;
>
> this should have worked as well - but I might stand corrected there.
>
> Regards,
> Holger
>

Sorry,

your example put me on the wrong track - after sending my reply, I
tested what I suggested, but that didn't work (on 9i). Not with the
long column containing plain nulls.

Anyway, I still believe your example and your errormessage don't
match.

Regards,

Holger

[s.kapitza]

Hi Paolo,

check the copy Command of SQL-Plus.

you have to use something like

SQL> copy from user/pass@yourdatabasetns insert dest(column, ...)
using select column,... from source;

check also set long etc. You have to set it to your longest long field.

hth.
s.kapitza

"Paolo Quaglia" <paoloq...@hotmail.com> wrote in message news:<arj0it$29rk$1...@stargate1.inet.it>...

[Anton Buijs]

Just this week I read Oracle encourages converting LONG to LOB's because
LONG will no longer exist in the next release. I thing I read it while
studying 9i New Features where a new DBMS package was described to convert
LONG to LOB type.

Paul Brewer <pa...@paul.brewers.org.uk> schreef in berichtnieuws
3ddd6...@mk-nntp-1.news.uk.worldonline.com...