transporting LONG datatype 1

[Thiko]

Hi

I am trying:

sql> insert into schema.table select * from schema.table;

and getting
ORA-00997: Illegal use of LONG datatype.

One of the colums in both tables is of LONG datatype.

We have Oracle 8.0.6.

Is anyway to insert/transport a long datatype?

I still get this error even if I specify the columns individually. Many Thanks.

Thiko!

 

[karluk]

Based on a simple test I tried, this can be done in PL/SQL using cursors. For a table named "test_long" consisting of only a single LONG column, the PL/SQL would look something like

declare
cursor c1 is
select long_col from test_long;
r1 c1%rowtype;
begin
open c1;
fetch c1 into r1;
while c1%notfound <> true loop
insert into test_long_copy values (r1.long_col);
fetch c1 into r1;
end loop;
close c1;
end;

 

[ljspop]

Hi,

I think Karluk's solution is going to barf if your long is over 32k. Per the Docs &quot; you cannot retrieve a value longer than 32760 bytes from a LONG column into a LONG variable. &quot;

If none of your longs are longer than that you're set.

I have used PERL DBI Blob reads to deal with longs, and I believe DBMS_SQL has similar chunk reading capability. Basically you have to open a cursor and read your long data in specified length chunks. Of course you'll have to figure somewhere to put it as you read it out before you can stuff it in, if your longs are big - in memory might not be the right answer.

I have used third party tools to do this kind of stuff - just depends on what is in house. One of my favorites is the import/export utility included with Benthic software's Golden32 query tool. (

http://www.benthicsoftware.com).

It'll allow you to export based on a query, including specifying the columns you need. Oracle's export tool will allow you to specifiy a where clause, but not the columns. I've used it on Longs and Long Raws before with success, but not on real big data. I'm sure there are others out there, but that is what has worked for me.

Good luck. I really don't like longs.

Bob Bob Lowell
ljspop@yahoo.com

 

[karluk]

I haven't tested the 32k limit, but I don't doubt that it's a problem. Longs definitely seem to be &quot;blessed&quot; with an unusual number of restrictions.

Here is another idea that shows promise: use the SQL*Plus copy command. The copy command handles long columns, and the length limit is determined by the value of the environment variable &quot;long&quot;. According to the documentation, the maximum value is 2 gigabytes.

The SQL*Plus to do this would look similar to the following.

set long 1000000
copy from uid/pwd@sid -
append TEST_LONG_COPY -
using -
select * from TEST_LONG

If no one finds any major flaws in this approach, I may write it up as a FAQ. This issue has come up quite a few times in this forum.

 

[ljspop]

Hi Karluk,

Having very little social life lately, and really liking your idea, I gave it a try tonight and found it to work real nicely. I did a 47k long datatype copy and it worked great.

A couple of footnotes for your faq - it didn't work for me on long raw data, and folks are better off using the 'copy' command from the line mode sqlplus (sqlplus.exe) as opposed to the 'gui' sqlplus (sqlplusw.exe) in an NT enivronment. Every time copy err'd in sqlplusw.exe sqlplus would crash (this was both using an 8.0.6 and 8.1.6 client) and would not display the error message - which made it hard to figure out what I was doing wrong.

Cool idea to add to the bag of tricks!

Bob Bob Lowell
ljspop@yahoo.com