Skip to Main Content
  • Questions
  • How to use Java to perform TO_LOB functionality over a link

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Chuck.

Asked: May 23, 2000 - 9:16 pm UTC

Last updated: November 21, 2005 - 9:54 am UTC

Version: Solaris v8.1.6

Viewed 1000+ times

You Asked

I am developing a document repository on a Solaris w/8.1.6. The initial load is a fixed set of documents which are currently in a v7.3.4.5 as a LONG RAW. Export, Import, TO_LOB and I have my BLOBS. The tricky part is moving new documents created in the remote v7 LONG RAWs. I want the 8.1.6 to retrieve the LONG RAWs via link(s) (up to 4 remote sites) and load into a BLOB.

The docs are 500k on average, but can get up to 8M. I've seen a lot on streaming to and from OS files, but I prefer to cut out the file and move it stream to stream (assuming a stream is necessary - big server.)

Thoughts, opinions, and please some code!!!

and Tom said...

We can do this but we'll need 2 connections -- one to read the LONG RAW as a stream and one to write the BLOB as a stream. The reason we need 2 connections is:

<quote>
Streaming Data Precautions

This section describes some of the precautions you must take to ensure that you do not accidentally discard or lose your stream data. The drivers automatically discard stream data if you perform any JDBC operation that communicates with the
database, other than reading the current stream. Two common precautions are described in the following sections:
...
</quote>

see

</code> http://docs.oracle.com/cd/A81042_01/DOC/java.816/a81354/basic4.htm#1007114 <code>

for more info. Ok, so since we know we need 2 connections -- one to read and one to write, it can look like this. I have a table

IMAGE( id number, image long raw );

In the 7.3 instance. I have a table

BLOB_DEMO( x number, y blob )

in the 8.1 instance. I have a database link from 8.1 to 7.3 set up. The java code could look like this:

import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;


public class movelr
{
public static void main(String[] args) throws Exception
{
int length;

DriverManager.registerDriver (new OracleDriver());

Connection conn73 = DriverManager.getConnection
("jdbc:oracle:oci8:@ora8idev","scott","tiger");
Connection conn81 = DriverManager.getConnection
("jdbc:oracle:oci8:@ora8idev","scott","tiger");

conn73.setAutoCommit (false);
conn81.setAutoCommit (false);
System.out.println( "connected..." );

// Create statements
PreparedStatement pstmt = conn73.prepareStatement
("select image from image@slack" );

System.out.println( "prepared statement for v7.3..." );

PreparedStatement insertStmt = conn81.prepareStatement
("insert into blob_demo ( x, y ) " +
"values ( ?, empty_blob() )");
System.out.println
( "prepared statement for v8.x BLOB insert..." );

PreparedStatement getStmt = conn81.prepareStatement
("select y from blob_demo where x = ? for update");
System.out.println
( "prepared statement for v8.x BLOB select..." );

ResultSet rset73 = pstmt.executeQuery();
System.out.println
( "executed statement for v7.3 select..." );

for( int i = 0; rset73.next(); i++ )
{
System.out.println( "looping..." );
InputStream is = rset73.getBinaryStream(1);
System.out.println( "Got v7.3 LONG RAW stream...");

// Insert a new row and get select
// back the BLOB column
insertStmt.setInt(1, i);
insertStmt.executeUpdate();
getStmt.setInt(1, i);
OracleResultSet rset81 =
(OracleResultSet) getStmt.executeQuery();

rset81.next();
OutputStream os =
rset81.getBLOB(1).getBinaryOutputStream();
System.out.println( "Got v8.x BLOB stream..." );

System.out.println( "Moving data from IS to OS...");
byte buffer[] = new byte[1024];
while ((length = is.read (buffer,0,1024)) != -1)
{
os.write( buffer, 0, length );
}

is.close();
os.close();
rset81.close();
}
rset73.close();
insertStmt.close();
getStmt.close();
pstmt.close();
conn81.commit();
conn73.close();
conn81.close();
}
}



Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about the other way around

Tom Best, February 21, 2003 - 5:01 pm UTC

I'm using this with great success. Thanks.

Is it possible to read in the blob and convert it/write into a long raw column?

Tom Kyte
February 21, 2003 - 6:46 pm UTC

most likely -- they are just streams both ways. Never tried it as that would be taking a huge step backwards.

Converting from clob to long.

Shailesh, June 17, 2003 - 2:49 am UTC

Hello,
You said "most likely -- they are just streams both ways. Never tried it as that would be
taking a huge step backwards."
Please consider our case.
We are running Oracle 9.0.1.4 on HP-UX 11i. We have five tables in schema "PROD" having datatype as LONG. Couple of days ago application team requested to create new schema for storing historical data. They scheduled a job which inserted the data into the new schema "CSSARCH" from the "PROD" schema and deleted the same from "PROD" after successful completion. Everything was fine except when we tried to apply the logs generated at the production server at the disaster site. The recovery failed. We raised the TAR and got the reply that this is a known bug 2322620. Also we found out that application team, withouit informing us, had changed the datatype of one of the columns in all five tables from LONG in schema "PROD" to CLOB in schema "CSSARCH".
Now our question is how do we revert back? i.e how to get data from clob column to long column because our historical data is in "CSSARCH" schema where one of the datatype is CLOB and other data is in "PROD" schema. Our disaster site is not available as logs are not getting applied. If we take cold database backups, they are restored, however we cant take cold backups daily. we do not want to loose data. Please help.
Also if we do manage to revert back i.e get data back to LONG column, our concern is will long datatype will be supported in future versions? Should we insist on application team to change the datatype to CLOB from LONG? Will it effect the existing application?

Please advise.

Regards
Shailesh

Tom Kyte
June 17, 2003 - 7:27 am UTC

why don't you backup and re-instantiate the standby?


Very good JAVA Example!

Solfrid, August 07, 2003 - 9:11 am UTC

This article(JAVA Example) helped us to have correct insert and update and filling of a Blob. This was a a show stopper for us in our eKlima system, for the user to order more than one report(blob) at a time.
Only thing I changed was that I dropped the class casting for ResultSet and casted the java.sql.BLOB to Oracle.sql.Blob instead.

Thanx!

A reader, December 17, 2003 - 4:43 pm UTC


Got Some Thoughts.......

sravan, February 18, 2005 - 2:45 am UTC

Thanks a lot, I have been working over this for more thatn 20 hrs.
Sra1

on insertion of large images(pdf) in to database

pavan kumar.s, November 21, 2005 - 9:41 am UTC

hai tom,

i saw the arical on how to insert large objects into tables using java.it is very nice and useful to us who are working oracle

here i have a problem to insert pdf files into oracle database.i am using forms6i.i have 10000 records.how i can do it with in short time/effictively.

i have to get it back when ever i pressed the button ?
i am new to this technology. could you please help to me tom

ragards

Tom Kyte
November 21, 2005 - 9:54 am UTC

forms questions are best asked on otn.oracle.com => discussion forums

My last form was in March of 1995.




More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here