My first "try to do a weekly interesting post" (and serious obviously): Java for read and write to blob
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 12:46:10 -0400
Hi what do you think about this, this is a idea about the "try to do one
weekly..."
Maybe you could prefer to give more explanation, I think this is enough for
the point.
Personally I want to learn tips and don't have problems sharing my own tips.
My experience readin and writing blobs in Oracle 9.2 windows 2000
Using utl_file to write there is a bug in windows, it add an additional
character to one character, when it writes.
Aditionally you have to define a directory.
I tried to do all in java, but I had a problem becaues to read you had to
give a connection, so I did it read using utl_file, adn write using java.
Here is the package to read (utl_file), write (java) and read (java witout a
connection I got after, still not implemented)
As you see I didn't went to ask tom and copied it from there, it is what I
(searched, copied from asktom), but implemented and tested, so you have here
sometihng
working in a database)
FUNCTION LeeArchivo( cFile VARCHAR2, cDirectory VARCHAR2, bArchivo IN OUT
BLOB) RETURN VARCHAR2 IS
b_blob BLOB;
b_bfile BFILE;
cPath VARCHAR2(150);
cReturn VARCHAR2(2000);
BEGIN
cReturn := 'Open File, debe darse GRANT READ/WRITE ON DIRECTORY TO DAZ.';
b_bfile := BFILENAME( cDirectory , cFile );
cReturn := 'Creating temporary.';
DBMS_LOB.CREATETEMPORARY(b_blob,TRUE);
cReturn := 'Opening lobs.';
DBMS_LOB.OPEN(b_blob,DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(b_bfile, DBMS_LOB.LOB_READONLY);
cReturn := 'Loading file.';
DBMS_LOB.LOADFROMFILE (b_blob,b_bfile, DBMS_LOB.GETLENGTH( b_bfile ));
cReturn := 'Close.';
DBMS_LOB.CLOSE(b_bfile);
DBMS_LOB.CLOSE(b_blob);
bArchivo :=b_blob;
cReturn := 'Free temporary.';
DBMS_LOB.FREETEMPORARY(b_blob);
RETURN 'T';
EXCEPTION WHEN OTHERS THEN
RETURN 'F-'||cReturn || SQLERRM;
END;
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "WriteBLOB"
AS import java.lang.*;
import java.io.*;
import java.sql.*;
import oracle.sql.*;
public class WriteBLOB
{
public static void do_export(BLOB p_blob, String p_file) throws Exception
{
// create file output stream
File l_file = new File(p_file);
FileOutputStream l_out = new FileOutputStream(l_file);
// get an input stream from the blob
InputStream l_in = p_blob.getBinaryStream();
// get buffer size from blob and use this to create buffer for stream
int l_size = p_blob.getBufferSize();
byte[] l_buffer = new byte[l_size];
int l_length = -1;
// write the blob data to the output stream
while ((l_length = l_in.read(l_buffer)) != -1)
{
l_out.write(l_buffer, 0, l_length);
l_out.flush();
}
// close the streams
l_in.close();
l_out.close();
}
};
/
-- write using java (got from ask tom)
connect sys as sysdba;
grant javauserpriv to scott;
begin
dbms_java.grant_permission('SCOTT',
'java.io.FilePermission','c:\temp\blob.txt', 'read');
end;
/
connect scott/tiger;
create table blob_test
(
short_desc varchar2(32),
the_blob blob,
constraint blob_test_pk primary key (short_desc)
);
create or replace and compile java source named "importBLOB" as
import java.lang.*;
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class importBLOB
{
public static void do_import(String p_file) throws Exception
{
PreparedStatement l_pstmt = null;
BLOB l_blob = null;
// get a connection using Oracle's internal server-side jdbc driver
Connection l_conn = new OracleDriver().defaultConnection();
// turn off autocommit - required for this example but something I do
anyway
l_conn.setAutoCommit(false);
// put an 'empty' row in the table
l_pstmt = l_conn.prepareStatement("insert into blob_test(short_desc,
the_blob) values (?, empty_blob())");
l_pstmt.setString(1, "TestBlob");
l_pstmt.execute();
l_conn.commit();
// select the 'empty' row from the table for update
// this requires autocommit to be false
l_pstmt = l_conn.prepareStatement("select short_desc, the_blob from
blob_test where short_desc = ? for update");
l_pstmt.setString(1, "TestBlob");
ResultSet l_rs = l_pstmt.executeQuery();
// get the blob from the resultset
if (l_rs.next())
{
l_blob = (BLOB) l_rs.getBlob(2);
}
// open the file to load into the blob
File l_inputFile = new File(p_file);
FileInputStream l_inputStream = new FileInputStream(l_inputFile);
// get output stream from the blob
OutputStream l_outputStream = l_blob.getBinaryOutputStream();
// create a buffer of the appropriate size
int l_chunkSize = l_blob.getChunkSize();
byte[] l_buffer = new byte[l_chunkSize];
// read the file into the blob
int l_readLength = -1;
while ((l_readLength = l_inputStream.read(l_buffer)) != -1)
{
l_outputStream.write(l_buffer, 0, l_readLength);
}
// close the streams
l_outputStream.close();
l_inputStream.close();
}
};
/
create or replace procedure importBLOB (p_file in varchar2) as
language java name 'importBLOB.do_import(java.lang.String)';
/
-- a small test of the above
begin
importBLOB('c:\temp\blob.txt');
end;
/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » My first "try to do a weekly interesting post" (and serious obviously): Java for read and write to blob
