INSERT FROM LONG DATATYPE TO LONG DATATYPE

[Vandy02]

I created a table 'B' that is the same structure as 'A'. I am wanting to copy the data from 'A' to be 'B'.

INSERT INTO B
SELECT * FROM A

I am unable to do so as col2 in both tables is a LONG datatype. I get an error ORA-00997 illegal use of LONG datatype. I have no idea what to do as I really don't think I can modify table 'A' as users view this table as it has line returns in the LONG column. Any idea?

 

[engineer2100]

I donot know anyother way except writing a small Pl/Sql script that would read the Long column into a Cursor variable and use that in your insert statement

HTH

-Engi

 

[Vandy02]

Well - That worked great!

Thanks!

 

[SantaMufasa]

Vandy,

Another option that should simplify life significantly with the LONG columns is to alter the columns to CLOB. There is no penalty that accrues and from that point onward, the CLOB columns have all the benefits of LONGs, but otherwise act just like VARCHAR2 columns.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 04:21 (17Feb05) UTC (aka "GMT" and "Zulu"),
@ 21:21 (16Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

Use sql*plus COPY command

Regards, Dima

http://seminihin.narod.ru

 

[engineer2100]

Sem (my guru!!), I thought of the COPY but didnot mention thinking that Vandy might need the Inserts within a PL/SQL code.

Mufasa (my guru!!), what if the application is running in production environment and you donot have the luxury of doing this. Infact I too am facing a similar situation and we have written a small PL/SQL code that would handle this kind of transfers between two tables on the same database or between two databases using a DB Link.

-Engi

 

[SantaMufasa]

Engi,

There should be no penalty/problem issuing the command:

ALTER TABLE <table_name> MODIFY <long_col_name> CLOB;

What you must do first is have a technical conversion meeting with the technologists involved in database administration and application development to discuss what you want to do, the benefits of the LONG-to-CLOB change, and any risks involved in making the change. Then you do a "run-through" in your development environment, then your test environment, then you make the change in your production environment.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 02:24 (22Feb05) UTC (aka "GMT" and "Zulu"),
@ 19:24 (21Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.