Hi Tom,

The TO_LOB() function works fine , for moving LONG data to table with CLOB datatype , but fails when moving LONG data into table with BLOB datatype.

E.g.
table t1 :
Name Type
----- -----
C1 CLOB

table t2 :
Name Type
----- -----
C1 LONG

table t3 :
Name Type
----- -----
C1 BLOB

insert into t1 select TO_LOB(c1) from t2; -- Works fine
insert into t3 select TO_LOB(c1) from t2; -- Gives error
ERROR at line 1:
ORA-00932: inconsistent datatypes

Why is it so, How do I insert LONG Data into table with BLOB datatype?

Thanks,
Shefali

as to how we can use the copy command and insert a long or clob column into another table...

how I can
ALTER TABLE MOVE stmt. Table with Long datatype??

using SQL COPY comman I get error
set arraysize 50
set long 100000
set set copycommit 20000

COPY FROM usr/pwd@MYSID CREATE tab2 USING SELECT * FROM tab1

I use simpl TO_LOB function

t2 (a CLOB, b number)
t1 (a LONG, b number)
insert into t2(a,b) select (to_log(a), b ) - Works GOOD!

It works.  Thanks so much for the info.    Sean 

SQL> create table t1(id number, text long);

Table created.

SQL> insert into t1(id , text)
  2  values(1, 'Hello');

1 row created.

SQL> commit;

Commit complete.

SQL> create table t2(id number, text long);

Table created.

SQL> set arraysize 100
SQL> set long 100000
SQL> set copycommit 1
SQL> copy from scott/tiger@winter insert t2(id, text) using select id, text from t1;

Array fetch/bind size is 100. (arraysize is 100)
Will commit after every array bind. (copycommit is 1)
Maximum long size is 100000. (long is 100000)
   1 rows selected from scott@winter.
   1 rows inserted into T2.
   1 rows committed into T2 at DEFAULT HOST connection.

SQL>
 

Tom, you wrote:

"LONGS goto CLOBS
LONG RAWS goto BLOBS

Makes no sense to move a long to a blob, we don't do that. "

Unfortunately, in our case, our Java develpers are insisting that we convert LONG to BLOB because when they tested CLOBs, there were issues with foreign characters.

Most records are less than 32K, so if that makes a big difference in your answer, we'll deal with the handful of records that are >32K.

We are on 8.1.7.4
Thanks.

Looking forward to seeing you at the NYOUG Day on Sep 23.
Are you going to talk about the 10g MODEL clause?

Tom,
I sent an email to the Java developers about this, and here is their response (our DB characterset is WE8ISO8859P1):

"In an ideal world (a true UTF8 database), we would be able to use CLOB instead of BLOB to store large text data. But, because our database is configured with a Western European character set (meaning it doesn't know anything about Japanese, Chinese, etc), whenever we write text to either a VARCHAR2, LONG, CLOB, or BLOB field from a Java/JDBC program, we need to perform the following steps when writing to the database:

Convert Java String to a Java UTF8 byte array
Store the byte array in the database

When reading from the database, we do the reverse:
Read byte array from the database
Convert byte array to Java String using UTF8 character encoding

With VARCHAR2 and LONG fields, for writing we use the
PreparedStatement.setAsciiStream(..) method. For reading we use ResultSet.getBytes(...). This technique works in that the bytes we store in the database are the same as the bytes we read.

For CLOB fields, we can't read and write the same was as we do with VARCHAR2 or LONG fields. To write to a CLOB in JDBC, we use:

CLOB clob = (CLOB)pstmt.getClob(3);
OutputStream os = clob.getAsciiOutputStream();
os.write(byteArray);

To read from a CLOB, we use:

ResultSet rs = pstmt.executeQuery();
rs.next();
Clob clob = rs.getClob(1);
InputStream is = clob.getAsciiStream();

The problem is that the CLOB reading/writing routines attempt to perform character conversion when reading or writing using the OCI JDBC driver, which causes corruption of foreign characters. This corruption does not happen when reading and writing strictly using the Thin JDBC driver, but I don't think we should restrict ourselves to using the Thin driver only.

The long term solution is to go to a true UTF8 database, where the database character set is UTF8, and no funny conversion occurs in the database. This would also involve converting all existing data to UTF8, which is not a trivial task. Then, we would be able to use CLOBs to store large text.
The short term solution is to use BLOBs instead of CLOBs, since BLOBs do not perform character conversion."

What do you make of this?

What is the problem here Tom?

SQL> create table foo 
  2  ( The_Whole_View   varchar2(65), 
  3    TextLength       number, 
  4    TheText          Long ) 
  5  /

Table created.

SQL> copy from ci_user/ci_user@cica_dev insert foo (the_whole_view, textlength, thetext ) using sele
ct owner||'.'||view_name, text_length, text from all_views;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA--3924922: Message -3924922 not found;  product=RDBMS; facility=ORA
 

I am not sure whether one has to provide user/password@connect-string combination twice, even though it is for the same database..., but when I did that way, it worked..

SQL> COPY FROM ci_user/ci_user@cica_dev TO ci_user/ci_user@cica_dev insert foo (the_whole_view, text
length,thetext ) using select owner||'.'||view_name, text_length, text from all_views;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
   1198 rows selected from ci_user@cica_dev.
   1198 rows inserted into FOO.
   1198 rows committed into FOO at ci_user@cica_dev.

Question:
How COPY command works..is it different from normal DML? 

I have some vague reminiscence that older versions of Oracle (7.2? 7.3?) have DBMS_LONG package that allows piecewise processing of LONG data from PL/SQl. Am I delirious or this package has silently gone?

Hi Tom,

I've been waiting to get a slice of your time to ask a question for the past few weeks.

I would really appreciate if you help me with this follow up question:

We have a 150G table with a LONG datatype column, we need to migrate this db from 2k to 8k block size. The maximum donwtime we can get is 12 hours. We are thiking of using combination of shareplex and standby database for this purpose.

Another plan was to copy that 150G table in about 6 hours, sqlplus copy for sure was taking more than 6 hrs. We have left with Perl DBI, Oracle OCI, or Pro*C.

I would like to parallelise this process by using bunch of OCI threads based on rowid ranges and insert into new table with lots of freelists.

Can you please help me write a small OCI program to copy a table with LONG datatype column using APPEND hint in the insert operation.

Thanks a lot.

Hi Tom,

Thanks for your help. That is the big constriant from our third party vendor application, we got no control atleast for now. We will eventually migrate LONG column to CLOB datatype when we have new application version available.

Can you please help me with writing a OCI program to copy a table with LONG datatype using APPEND hint (it would be great, if we have something like BULK insert in Pl/sql).

Thanks for your time.

Hi Tom,

We are on 64 bit Oracle Enterprised version 8.1.7.4

Our troubled third party vendor application does too many full table scans of huge tables, we don't have any way of changing their code. Apart from that, all of our other databases are on 8K, it was made as a stadard at our company to create all 8K blocksize database's. We grow at the rate of 100% every couple of years. Our Architect's made that decision. Migration to 8K blocksize db would improve full table scans, and much reduced row chaining/migration. When we get newer application version later this year, we wouldn't have many of these problems.

Perfect logic/science doesn't really work all the time at each and every big companies conflicting to their standards made by architects.

Would you please help me write that OCI program. Your help is very much appreciated.

Thanks.

Hi tom,

Thanks so much for your help on this. One last question:

In one of your articles you said, full table scan would not go to forwaring rowid when it hits a migrated row, becuase it will eventually get there. Does this work similarly when we parallelise jobs using rowid ranges, i.e., does the same row fetched twice in different rowid ranges?

Thanks

I've just found myself using the COPY command in sqlplus to transfer tables with LONG columns (I was trying to compare LONG data stored in the data dictionary that stored in another DB).

I note that the 9.2 and 10G state that this command will be deprecated in the future.

Does this mean that Oracle will no longer provide any means to carry out this kind of operation in the future?

Will Oracle continue to use the LONG datatype to store some dictionary data?

Regards,

Hello Tom,

I am again stuck with some migration problem.
We are migrating the data from a 8i to a 9i database. One of the table has a long column and we need to migrate that as well.
Now the problem is we cannot convert long column to CLOB or anything else for that matter as the business rule don't allow it.
We used a trigger and in that we used select staement with a dblink. But this does not get the long data column giving an error ora-00600 internal error code.
Hence we have to migrate it by some other means.
Is this possible? If yes, then how?

Hi Tom,

We have an old database which has some LONG datatype columns. I want to query a table with LONG column by using where LONG_COLUMN like '%my_string%', I got the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

How can we search for a specific string in LONG column?

Thank you for your help.

Hi Tom,

i have procedure in which a variable declared as LONG datatype.

in the code they have dynamically formed a query string and stored into this LONG datatype variable. Now i want to debug the query string so i tried to store this LONG variable into a table with CLOB datatype column.
But i am getting error.

Error: PL/SQL: ORA-00932: inconsistent datatypes: expected LONG BINARY got CHAR
Line: 2566
Text: 'MyMAPPING',To_Lob(v_Final_String),'2'

giving sample pl sql block as actual block might run in pages.


Declare
v_Final_String Long
Begin

v_Final_String := 'Insert into emp(Empno,Ename) values('||vEno||','''||vEname||''');' ;

Insert into Temp(vclob) Values(to_clob(v_Final_String));

Execute immediate v_Final_String;

End;
/