Here's a little java client utility that I use for 8.1.7. Look at the usage string. query_file should contain a query that returns a single lob locator. By default it reads from Oracle and writes to lob_file. Pass -write to read from lob_file and write to Oracle. By default it assumes a clob, pass -blob for binary data.

// file LobUtils.java
import java.io.InputStream;
import java.io.OutputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Blob;
import oracle.sql.BLOB;
import java.sql.SQLException;

import java.sql.Clob;
import oracle.sql.CLOB;

import java.io.FileReader;
import java.io.BufferedReader;
import java.io.StringWriter;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.Writer;
import java.io.InputStreamReader;

public class LobUtils
{
final static int bBufLen = 4 * 8192;
String query;
String connectString;
String outFile;
Connection conn;

public LobUtils(String connectString, String query, String outFile) {
this.connectString = connectString;
this.query = query;
this.outFile = outFile;
this.conn = null;
}

public static void main(String[] args)
throws FileNotFoundException, IOException, SQLException {
if (args.length < 5) usage();
int ii = 0;
String connString = args[ii++];
String queryFile = null;
String outputFile = null;

boolean read = true;
boolean isBinary = false;

for (; ii < args.length; ii++) {
if (args[ii].equals("-write"))
read = false;

if (args[ii].equals("-blob"))
isBinary = true;

if (args[ii].equals("-qf") && ii < args.length - 1)
queryFile = args[++ii];

if (args[ii].equals("-lf") && ii < args.length - 1)
outputFile = args[++ii];

}

if (queryFile == null || outputFile == null) usage();

// all set
if (read) {
BufferedReader freader = new BufferedReader(new FileReader(queryFile));
StringWriter swriter = new StringWriter();
int bufLen = 1024;
char[] cbuf = new char[bufLen];
int length = -1;
while ((length = freader.read(cbuf, 0, bufLen)) != -1) {
swriter.write(cbuf, 0, length);
}
freader.close();
swriter.close();
String query = swriter.toString();

LobUtils lutils = new LobUtils(connString, query, outputFile);
if (isBinary) {
Blob blob = lutils.getBlob();
long wrote = lutils.writeBlobToFile(blob);
System.out.println("Wrote " + wrote + " bytes to file " + outputFile);
} else {
Clob clob = lutils.getClob();
long wrote = lutils.writeClobToFile(clob);
System.out.println("Wrote " + wrote + " bytes to file " + outputFile);
}
} else {
BufferedReader freader = new BufferedReader(new FileReader(queryFile));
StringWriter swriter = new StringWriter();
int bufLen = 1024;
char[] cbuf = new char[bufLen];
int length = -1;
while ((length = freader.read(cbuf, 0, bufLen)) != -1) {
swriter.write(cbuf, 0, length);
}
freader.close();
swriter.close();
String query = swriter.toString();

LobUtils lutils = new LobUtils(connString, query, outputFile);
Clob clob = lutils.getClob();
InputStream creader = new FileInputStream(outputFile);
long wrote = lutils.writeToOraClob(clob, creader);
System.out.println("Wrote " + wrote + " bytes from file " + outputFile);
}
}

public Clob getClob()
throws SQLException {
conn = ConnUtil.getConnection(connectString);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Clob clob = null;
if (rs.next()) {
clob = rs.getClob(1);
}
return clob;
}

public Blob getBlob()
throws SQLException {
conn = ConnUtil.getConnection(connectString);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Blob blob = null;
if (rs.next()) {
blob = rs.getBlob(1);
}
return blob;
}

public long writeClobToFile(Clob clob)
throws IOException, SQLException {
long wrote = 0;
BufferedWriter fwriter = new BufferedWriter(new FileWriter(outFile));
wrote = readFromClob(clob, fwriter);
fwriter.close();
conn.commit();
conn.close();
return wrote;
}

public long writeBlobToFile(Blob blob)
throws IOException, SQLException {
long wrote = 0;
OutputStream fwriter = new FileOutputStream(outFile);
wrote = readFromBlob(blob, fwriter);
fwriter.close();
conn.commit();
conn.close();
return wrote;
}

private static void usage() {
System.err.println("Usage: java LobUtils user/passwd@sid [-write] [-blob] -qf query_file -lf lob_file");
System.exit(1);
}

public static long writeToOraBlob(Blob blob, InputStream in)
throws SQLException, IOException {
BLOB oblob = (BLOB)blob;
OutputStream out = oblob.getBinaryOutputStream();
int length = -1;
long wrote = 0;
int chunkSize = oblob.getChunkSize();
byte[] buf = new byte[chunkSize];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
wrote += length;
}
out.close();
return wrote;
}

public long writeToOraClob(Clob clob, InputStream in)
throws SQLException, IOException {
CLOB oclob = (CLOB)clob;
OutputStream out = oclob.getAsciiOutputStream();
int length = -1;
long wrote = 0;
int chunkSize = oclob.getChunkSize();
byte[] buf = new byte[chunkSize];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
wrote += length;
}
out.close();
conn.commit();
return wrote;
}

public static long readFromBlob(Blob blob, OutputStream out)
throws SQLException, IOException {
InputStream in = blob.getBinaryStream();
int length = -1;
long read = 0;
byte[] buf = new byte[bBufLen];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}

public static long readFromClob(Clob clob, Writer out)
throws SQLException, IOException {
BufferedReader in = new BufferedReader(new InputStreamReader(clob.getAsciiStream()));
int length = -1;
long read = 0;
char[] buf = new char[bBufLen];
while ((length = in.read(buf, 0, bBufLen)) != -1) {
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}
}

Isnt it a bit surprising that using Oracle packages , I can successfully write to a BLOB from a file, whereas we cannot read the BLOB into a file ?
I am using VB, thereby wanted to do all processing on the server side , hence tried to have a solution .

I am currently searching through the Oracle documentation for UTL_FILE looking for a proc that will allow me to write BLOBs to a file. Unfortuately i don't see a proc that will work. The BLOBs we are working with are scanned images not text. Is there a UTL_FILE.PUT_BLOB ( that i tried - what about an equivalent or alternative). I have also considered the Java alternative but would prefer a PL/SQL solution as that is where the group expertise is for support and maintenance. Any assistance will be greatly appreciated. thanks, Fred

Tom,

Do you by any chance have an example on how to get this done in 9iR2?

Thanks

Thanks Tom for you response.

This is what i was trying and was getting the following error, if the length exceeds 32K.

declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 375
ORA-06512: at "SYS.UTL_FILE", line 990
ORA-06512: at line 35

declare
i1 blob;
i2 number;
amt number := 10000;
len number;
my_vr raw(10000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'TEST_DIR';
p_file varchar2(30) default 'gs_test1';
begin
-- get the blob locator
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
for l_cur in (SELECT record#, xsl mylob FROM docxsl where rownum = 1 order by record#)
loop
len := DBMS_LOB.GETLENGTH(l_cur.mylob);
i1 := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));
i2 := 1;
if len < 10000 then
DBMS_LOB.READ(i1,len,i2,my_vr);
utl_file.put(l_output, my_vr );
else
DBMS_LOB.READ(i1,amt,i2,my_vr);
utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
end if;

i2 := i2 + amt;
while (i2 < len) loop -- loop till entire data is fetched
dbms_output.put_line('i2 : ' || to_char(i2));
DBMS_LOB.READ(i1,amt,i2,my_vr);
utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
utl_file.fflush(l_output);
i2 := i2 + amt ;
end loop;
utl_file.fclose(l_output);
end loop;
end ;
/

Note: I combine the example from your site and metalink.

I have XSL files stored as BLOB in the DB. I wanted to retrieve them, the way was they were stored. I don't want to put a "new line" after every 32K. Can I do this using PL/SQL?

--I thought it is related to the question asked, hence I am not submitting a new one.

Thanks very much.

I trying to write a dbf file straight out of the database. Right after the header I then start to output the rows by looping through them thus:

for i in 1..vRow.count loop

utl_file.put_raw(v_outputfile,vRow(i),TRUE);

end loop;

Now, this is fine for a small table but for anything bigger I get the write file error - ora-29285. Is there really no way around this? Surely there must be!

The code is an extended version of your dbf file extractor and it also uses your str2tbl function!  Not complete yet as it can't handle dates properly or decimals, but it demonstrates the problem:

CREATE OR REPLACE package dbase_pkg
as

    -- procedure to a load a table with records
    -- from a DBASE file.
    --
    -- Uses a BFILE to read binary data and dbms_sql
    -- to dynamically insert into any table you
    -- have insert on.
    --
    -- p_dir is the name of an ORACLE Directory Object
    --       that was created via the CREATE DIRECTORY
    --       command
    --
    -- p_file is the name of a file in that directory
    --        will be the name of the DBASE file
    --
    -- p_tname is the name of the table to load from
    --
    -- p_cnames is an optional list of comma separated
    --          column names.  If not supplied, this pkg
    --          assumes the column names in the DBASE file
    --          are the same as the column names in the
    --          table
    --
    -- p_show boolean that if TRUE will cause us to just
    --        PRINT (and not insert) what we find in the
    --        DBASE files (not the data, just the info
    --        from the dbase headers....)

    procedure load_Table( p_dir    in varchar2,
                          p_file   in varchar2,
                          p_tname  in varchar2,
                          p_cnames in varchar2 default NULL,
                          p_show   in BOOLEAN default FALSE);
                          
    -- procedure to dump a file to disk in DBASE file format.
    --
    -- in addition to the above, p_where_clause is the filter 
    -- which if null will dump the entire table.  
                          
    procedure dump_table (p_dir          in varchar2,
                            p_file         in varchar2,
                          p_tname        in varchar2,
                          p_cnames       in varchar2 default NULL,
                          p_where_clause in varchar2 default ' 1=1 ');
end;
/
CREATE OR REPLACE package body dbase_pkg
as

-- Might have to change depending on platform!!!
-- Controls the byte order of binary integers read in
-- from the dbase file
BIG_ENDIAN      constant boolean default TRUE;

type dbf_header is RECORD
(
    version    varchar2(25), -- dBASE version number
    year       int,          -- 1 byte int year, add to 1900
    month      int,          -- 1 byte month
    day        int,             -- 1 byte day
    no_records VARCHAR2(8),             -- number of records in file,
                             -- 4 byte int
    hdr_len    VARCHAR2(4),             -- length of header, 2 byte int
    rec_len    VARCHAR2(4),             -- number of bytes in record,
                             -- 2 byte int
    no_fields  int           -- number of fields
);


type field_descriptor is RECORD
(
    name     varchar2(11),
    fname    varchar2(30),
    type     char(1),
    length   int,   -- 1 byte length
    decimals int    -- 1 byte scale
);

type field_descriptor_array
is table of
field_descriptor index by binary_integer;


type rowArray
is table of
varchar2(4000) index by binary_integer;


g_cursor binary_integer default dbms_sql.open_cursor;


function ite( tf in boolean, yes in varchar2, no in varchar2 )
        return varchar2
is
begin
    if ( tf ) then
       return yes;
    else
       return no;
    end if;
end ite;




-- Function to convert a binary unsigned integer
-- into a PLSQL number

function to_int( p_data in varchar2 ) return number
is
    l_number number default 0;
    l_bytes  number default length(p_data);
begin
    if (big_endian)
    then
        for i in 1 .. l_bytes loop
            l_number := l_number +
                              ascii(substr(p_data,i,1)) *
                                           power(2,8*(i-1));
        end loop;
    else
        for i in 1 .. l_bytes loop
            l_number := l_number +
                         ascii(substr(p_data,l_bytes-i+1,1)) *
                         power(2,8*(i-1));
        end loop;
    end if;

    return l_number;
end;


-- Routine to parse the DBASE header record, can get
-- all of the details of the contents of a dbase file from
-- this header

procedure get_header
(p_bfile        in bfile,
 p_bfile_offset in out NUMBER,
 p_hdr          in out dbf_header,
 p_flds         in out field_descriptor_array )
is
    l_data            varchar2(100);
    l_hdr_size        number default 32;
    l_field_desc_size number default 32;
    l_flds            field_descriptor_array;
begin
    p_flds := l_flds;

    l_data := utl_raw.cast_to_varchar2(
                       dbms_lob.substr( p_bfile,
                                        l_hdr_size,
                                        p_bfile_offset ) );
    p_bfile_offset := p_bfile_offset + l_hdr_size;

    p_hdr.version    := ascii( substr( l_data, 1, 1 ) );
    p_hdr.year       := 1900 + ascii( substr( l_data, 2, 1 ) );
    p_hdr.month      := ascii( substr( l_data, 3, 1 ) );
    p_hdr.day        := ascii( substr( l_data, 4, 1 ) );
    p_hdr.no_records := to_int( substr( l_data,  5, 4 ) );
    p_hdr.hdr_len    := to_int( substr( l_data,  9, 2 ) );
    p_hdr.rec_len    := to_int( substr( l_data, 11, 2 ) );
    p_hdr.no_fields  := trunc( (p_hdr.hdr_len - l_hdr_size)/
                                           l_field_desc_size );


    for i in 1 .. p_hdr.no_fields
    loop
        l_data := utl_raw.cast_to_varchar2(
                         dbms_lob.substr( p_bfile,
                                          l_field_desc_size,
                                          p_bfile_offset ));
        p_bfile_offset := p_bfile_offset + l_field_desc_size;

        p_flds(i).name := rtrim(substr(l_data,1,11),chr(0));
        p_flds(i).type := substr( l_data, 12, 1 );
        p_flds(i).length  := ascii( substr( l_data, 17, 1 ) );
        p_flds(i).decimals := ascii(substr(l_data,18,1) );
    end loop;

    p_bfile_offset := p_bfile_offset +
                          mod( p_hdr.hdr_len - l_hdr_size,
                               l_field_desc_size );
end;


function build_insert
( p_tname in varchar2,
  p_cnames in varchar2,
  p_flds in field_descriptor_array ) return varchar2
is
    l_insert_statement long;
begin
    l_insert_statement := 'insert into ' || p_tname || '(';
    if ( p_cnames is NOT NULL )
    then
        l_insert_statement := l_insert_statement ||
                              p_cnames || ') values (';
    else
        for i in 1 .. p_flds.count
        loop
            if ( i <> 1 )
            then
               l_insert_statement := l_insert_statement||',';
            end if;
            l_insert_statement := l_insert_statement ||
                            '"'||  p_flds(i).name || '"';
        end loop;
        l_insert_statement := l_insert_statement ||
                                           ') values (';
    end if;
    for i in 1 .. p_flds.count
    loop
        if ( i <> 1 )
        then
           l_insert_statement := l_insert_statement || ',';
        end if;
        if ( p_flds(i).type = 'D' )
        then

            l_insert_statement := l_insert_statement ||
                     'to_date(:bv' || i || ',''yyyymmdd'' )';
        else
            l_insert_statement := l_insert_statement ||
                                                ':bv' || i;
        end if;
    end loop;
    l_insert_statement := l_insert_statement || ')';

    return l_insert_statement;
end;


function get_row
( p_bfile in bfile,
  p_bfile_offset in out number,
  p_hdr in dbf_header,
  p_flds in field_descriptor_array ) return rowArray
is
    l_data     varchar2(4000);
    l_row   rowArray;
    l_n     number default 2;
begin
    l_data := utl_raw.cast_to_varchar2(
                   dbms_lob.substr( p_bfile,
                                    p_hdr.rec_len,
                                    p_bfile_offset ) );
    p_bfile_offset := p_bfile_offset + p_hdr.rec_len;

    l_row(0) := substr( l_data, 1, 1 );

    for i in 1 .. p_hdr.no_fields loop
        l_row(i) := rtrim(ltrim(substr( l_data,
                                        l_n,
                                        p_flds(i).length ) ));
        if ( p_flds(i).type = 'F' and l_row(i) = '.' )
        then
            l_row(i) := NULL;
        end if;
        l_n := l_n + p_flds(i).length;
    end loop;
    return l_row;
end get_row;


procedure show( p_hdr    in dbf_header,
                p_flds   in field_descriptor_array,
                p_tname  in varchar2,
                p_cnames in varchar2,
                p_bfile  in bfile )
is
    l_sep varchar2(1) default ',';

    procedure p(p_str in varchar2)
    is
        l_str long default p_str;
    begin
        while( l_str is not null )
        loop
            dbms_output.put_line( substr(l_str,1,250) );
            l_str := substr( l_str, 251 );
        end loop;
    end;
begin
    p( 'Sizeof DBASE File: ' || dbms_lob.getlength(p_bfile) );

    p( 'DBASE Header Information: ' );
    p( chr(9)||'Version = ' || p_hdr.version );
    p( chr(9)||'Year    = ' || p_hdr.year   );
    p( chr(9)||'Month   = ' || p_hdr.month   );
    p( chr(9)||'Day     = ' || p_hdr.day   );
    p( chr(9)||'#Recs   = ' || p_hdr.no_records);
    p( chr(9)||'Hdr Len = ' || p_hdr.hdr_len  );
    p( chr(9)||'Rec Len = ' || p_hdr.rec_len  );
    p( chr(9)||'#Fields = ' || p_hdr.no_fields );

    p( chr(10)||'Data Fields:' );
    for i in 1 .. p_hdr.no_fields
    loop
        p( 'Field(' || i || ') '
             || 'Name = "' || p_flds(i).name || '", '
             || 'Type = ' || p_flds(i).Type || ', '
             || 'Len  = ' || p_flds(i).length || ', '
             || 'Scale= ' || p_flds(i).decimals );
    end loop;

    p( chr(10) || 'Insert We would use:' );
    p( build_insert( p_tname, p_cnames, p_flds ) );

    p( chr(10) || 'Table that could be created to hold data:');
    p( 'create table ' || p_tname );
    p( '(' );

    for i in 1 .. p_hdr.no_fields
    loop
        if ( i = p_hdr.no_fields ) then l_sep := ')'; end if;
        dbms_output.put
        ( chr(9) || '"' || p_flds(i).name || '"   ');

        if ( p_flds(i).type = 'D' ) then
            p( 'date' || l_sep );
        elsif ( p_flds(i).type = 'F' ) then
            p( 'float' || l_sep );
        elsif ( p_flds(i).type = 'N' ) then
            if ( p_flds(i).decimals > 0 )
            then
                p( 'number('||p_flds(i).length||','||
                              p_flds(i).decimals || ')' ||
                              l_sep );
            else
                p( 'number('||p_flds(i).length||')'||l_sep );
            end if;
        else
            p( 'varchar2(' || p_flds(i).length || ')'||l_sep);
        end if;
    end loop;
    p( '/' );
end;


procedure load_Table( p_dir in varchar2,
                      p_file in varchar2,
                      p_tname in varchar2,
                      p_cnames in varchar2 default NULL,
                      p_show in boolean default FALSE )
is
    l_bfile      bfile;
    l_offset  number default 1;
    l_hdr     dbf_header;
    l_flds    field_descriptor_array;
    l_row      rowArray;
begin
    l_bfile := bfilename( p_dir, p_file );
    dbms_lob.fileopen( l_bfile );

    get_header( l_bfile, l_offset, l_hdr, l_flds );

    if ( p_show )
    then
        show( l_hdr, l_flds, p_tname, p_cnames, l_bfile );
    else
        dbms_sql.parse( g_cursor,
                        build_insert(p_tname,p_cnames,l_flds),
                        dbms_sql.native );

        for i in 1 .. l_hdr.no_records loop
            l_row := get_row( l_bfile,
                              l_offset,
                              l_hdr,
                              l_flds );

            if ( l_row(0) <> '*' ) -- deleted record
            then
                for i in 1..l_hdr.no_fields loop
                    dbms_sql.bind_variable( g_cursor,
                                            ':bv'||i,
                                            l_row(i),
                                            4000 );
                end loop;
                if ( dbms_sql.execute( g_cursor ) <> 1 )
                then
                    raise_application_error( -20001,
                                 'Insert failed ' || sqlerrm );
                end if;
            end if;
        end loop;
    end if;

    dbms_lob.fileclose( l_bfile );
exception
    when others then
        if ( dbms_lob.isopen( l_bfile ) > 0 ) then
            dbms_lob.fileclose( l_bfile );
        end if;
        RAISE;
end;

procedure put_header (p_tname        in varchar2,
                        p_cnames       in varchar2 DEFAULT NULL,
                      l_hdr          in out dbf_header,
                      vFlds          in out field_descriptor_array)
                      

                      
is

v_value_list  strTableType;

vCursor   varchar2(2000);
type rc IS ref cursor;
col_cur rc;
i  INTEGER:=0;

begin

IF p_cnames IS NOT NULL THEN
    v_value_list:=str2tbl(UPPER(p_cnames));
    vCursor:='select substr(column_name,1,11), 
                     case data_type 
                     when ''DATE'' then ''D''
                     when ''NUMBER'' then ''N''
                     else ''C'' end ,
                     case data_type
                     when ''NUMBER'' then NVL(data_precision,22)
                     when ''DATE'' then 8
                     else data_length end,
                     case data_type
                     when ''NUMBER'' then data_scale
                     end ,
                     column_name   from all_tab_cols 
                where column_name IN (select * from TABLE (cast(str2tbl(UPPER('''||p_cnames||''')) as strTableType)))
                and   table_name='''||upper(p_tname)||'''
                order by column_id';

else

    vCursor:='select SUBSTR(column_name,1,11), 
                     case data_type 
                     when ''DATE'' then ''D''
                     when ''NUMBER'' then ''N''
                     else ''C'' end ,
                     case data_type
                     when ''NUMBER'' then NVL(data_precision,22)
                     when ''DATE'' then 8
                     else data_length end,
                     case data_type
                     when ''NUMBER'' then data_scale
                     end ,
                     column_name
              from all_tab_cols
              where table_name='''||upper(p_tname)||'''
              order by column_id';
END IF;

open col_cur for vCursor;

loop
    i:=i+1;
    fetch col_cur into vFlds(i).name,vFlds(i).type,vFlds(i).length,vFlds(i).decimals,vFlds(i).fname;
    exit when col_cur%notfound;
    
end loop;

close col_cur;

l_hdr.version    :='03';
l_hdr.year       :=to_number(to_char(sysdate,'yyyy'))-1900;
l_hdr.month      :=to_number(to_char(sysdate,'mm'));
l_hdr.day        :=to_number(to_char(sysdate,'dd'));
l_hdr.rec_len    :=1; -- to be set later 
l_hdr.no_fields  :=vFlds.COUNT;
l_hdr.hdr_len    :=to_char((l_hdr.no_fields*32)+33,'FM000x');


end;


procedure put_rows (p_tname IN  varchar2,
                      p_where_clause in varchar2 default '1=1 ',
                    vRow in out rowarray,
                    vFlds in field_descriptor_array)

is
type rc is ref cursor;

cur rc;

i  integer:=0;
vSelectList  VARCHAR2(32767);

v_cur VARCHAR2(32767);

begin


for l in 1..vFlds.count loop

    vSelectList:=vSelectList||ite(l!=1,'||','')||'utl_raw.cast_to_raw(rpad(NVL('|| case when vFlds(l).type='N' then 'to_char(' end ||vFlds(l).fname||case when vFlds(l).type='N' then ')' end ||','' ''),'||vFlds(l).length||','' ''))';

end loop;

v_cur:='select '||vSelectList||' from '||p_tname||' where '||p_where_clause;

open cur for v_cur;

loop

    i:=i+1;
    fetch cur into vRow(i);
    exit when cur%notfound;
    
end loop;

close cur;

end;


procedure dump_table (p_dir          in varchar2,
                        p_file         in varchar2,
                      p_tname        in varchar2,
                      p_cnames       in varchar2 default NULL,
                      p_where_clause in varchar2 default ' 1=1 ')
                      
    

is
l_hdr dbf_header;
vFlds field_descriptor_array;
vRow  rowarray;
v_cnames  VARCHAR2(4000);
v_outputfile UTL_FILE.FILE_TYPE;

vCount int;

vStartTime  DATE;
vEndTime    DATE;
begin

    vStartTime:=sysdate;

    put_header(p_tname,p_cnames,l_hdr,vFlds);
    
    
    put_rows(p_tname,p_where_clause,vRow,vFlds);
    
    v_outputfile := utl_file.fopen(p_dir,p_file,'w',32767);
    
    for i in 1..vFlds.count loop
    
        l_hdr.rec_len:=l_hdr.rec_len+vFlds(i).length;
        
    end loop;
    
    l_hdr.rec_len    :=to_char(to_number(l_hdr.rec_len),'FM000x');
    
    l_hdr.rec_len   :=substr(l_hdr.rec_len,-2)||
                       substr(l_hdr.rec_len,1,2);
                      
    l_hdr.no_records :=to_char(vRow.count,'FM0000000x'); 
    
    l_hdr.no_records:=substr(l_hdr.no_records,-2)||
                       substr(l_hdr.no_records,5,2)||
                      substr(l_hdr.no_records,3,2)||
                      substr(l_hdr.no_records,1,2);
                      
    
    l_hdr.hdr_len:=substr(l_hdr.hdr_len,-2)||
                       substr(l_hdr.hdr_len,1,2);


    utl_file.put_raw(v_outputFile,
              rpad(l_hdr.version||to_char(l_hdr.year,'FM0x')||to_char(l_hdr.month,'FM0x')||
              to_char(l_hdr.day,'FM0x')||l_hdr.no_records||l_hdr.hdr_len||
              l_hdr.rec_len,64,'0'));
              
    for i in 1..vFlds.count loop
    
        utl_file.put_raw(v_outputFile,utl_raw.cast_to_raw(vFlds(i).name)||replace(rpad('00',12-length(vFlds(i).name),'#'),'#','00')||
                                      utl_raw.cast_to_raw(vFlds(i).type)||'00000000'||
                                      to_char(vFlds(i).length,'FM0x')||'000000000000000000000000000000');
    end loop;
    
    -- terminator for the field names 
    utl_file.put_raw(v_outputFile,'0D');
    
    
    for i in 1..vRow.count loop
    
            
        utl_file.put_raw(v_outputfile,'20'||vRow(i),TRUE);
        
            end loop;
    
    if utl_file.IS_OPEN(v_outputFile ) then
        UTL_FILE.FCLOSE(v_outputFile);
    end if;
    
    vEndTime:=sysdate;
    
    dbms_output.put_line('Started - '||to_char(vStartTime,'HH24:MI'));
    dbms_output.put_line('Finished - '||to_char(vEndTime,'HH24:mi'));
    dbms_output.put_line('Elapsed - '||to_char((vEndTime-vStartTime),'hh24:mi'));
exception
when others then
    utl_file.fclose(v_outputFile);
    raise;
    
end;

end;
/

SQL>  exec dbase_pkg.dump_table('c:\dbf','test.dbf','all_objects','owner,object_name,subobject_name,
object_id')
BEGIN dbase_pkg.dump_table('c:\dbf','test.dbf','all_objects','owner,object_name,subobject_name,objec

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "STEVE.DBASE_PKG", line 564
ORA-06512: at line 1


SQL> 

But if I limit the number of rows:

SQL> exec dbase_pkg.dump_table('c:\dbf','test.dbf','all_objects','owner,object_name,subobject_name,o
bject_id','rownum<100')

PL/SQL procedure successfully completed.

SQL> 
 

Thanks Tom - I wondered whether closing and re-opening the file would work but thought that I must be missing something. I'll prepare a test case for support and post here if they come up with a solution.

Once again, thanks

Steve

I am trying to convert an oracle table to dbf format. and am not able to do that. Did steves code work? Just curious, I dont want to reinvent the wheel!

Thanks

although you're still constrained by the 32k limit - Oracle support suggested making sure that I had a utl_file.new_line every so often but his, of course, added a couple of unwanted bytes to the file - ok for an image but not for dbf. So, in the end instead of using utl_file.put_raw, I had to use a small java proc to output the file - it works a treat but didn't need 9iR2 to work!!

that Oracle say that the 32k limit is fixed in 10i but that there is no backport for it.

The first time I tried to do it on my own and messed it up, and then the copy paste worked fine :)
Thanks the conversion to dbf worked perfect. added the code for date and the workaround for utl_file.

Got a a good understanding of utl_raw alsow :)

One question that bothers me is the header length is 32 byte, but why do we have to allocate 64 bytes. (assumed because its rpad'ed with 64 0's; Is it because 32 bytes in hex is 64 in ascii?

Thanks to steve too :)

-dharma

Hi, I am using an above snippet (modified)

declare
vblob blob;
vstart number:=1;
bytelen number := 32000;
len number;
my_vr raw(32000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'f:\gol\ora_vscan';
p_file varchar2(30) default 'CMsitegarden.pdf';
begin
-- get the blob locator
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
for l_cur in (SELECT DOCUMENT_CONTENT mylob FROM gol_attached_document where SUPP_DOCUMENT_NUMBER = 4 and rownum =1)
loop
len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));
vstart := 1;
while (vstart < len) loop -- loop till entire data is fetched
dbms_output.put_line('vstart : ' || to_char(vstart));
DBMS_LOB.READ(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
vstart := vstart + bytelen ;
end loop;
utl_file.fclose(l_output);
end loop;
end ;

This outputs my file fine. But it will not open in the software associated.

IE a .doc word cannot open
a .pdf acrobat cannot open
the file sizes match the source.

Can you tell me what I am doing wrong.

for .txt files in the blob do I have to do anything different?

tia
Ray

More info on the above.

I looked at the hex of both the source and the after upload and write. Every time the source had a 0A , the resulting file had a 0D0A. the resulting file also had a 0D0A at the end.

Tom,
We are facing something very strange here. We have two identical databases one on Windows 2000 and the other on Sun OS. Both are having utf8 characterset. Our developers coded an application using getCharacterStream class to access a CLOB column and it worked fine on the database running on Windows 2000, but it did not show the right data when run against the database running on SunOS, rather showed some garbage.
When we changed to getAsciiStream class, it works fine for both the databases. However, we are concerned for the multilingual data (like Chinese etc.) which will be stored eventually in this column and retrieving that like Ascii will not give us the right result ( since Chinese will use multi-byte storage for characters unlike Ascii which uses single byte).
Could you point out where are we missing something? It is surprising that operating system has a bearing on something within oracle database, I still do not beleve that to be true. What is your opinion and solution if any?

Regards.

Tom,
We are running the application through BEA weblogic server(on Windows 2000). We just changed the database connection string for either database while keeping the database "client" (weblogic server) constant. The results were different though, as mentioned(i.e. garbage comes when we connect to database running on Sun OS). We tried setting NLS_LANG as Windows environmental parameter to utf8 and also we tried by setting nls_lang to utf8 in the weblogic startup parameter. No difference in the outcome.
Pleaae help.

Regards.

Tom,
I have just compared the two, they are identical.
One more piece of information. I had set nls_lang=.utf8 and then exported the data from database on Windows(characterset utf8) and then imported into the schema in database running on Sun OS having characterset utf8. Will it corrupt the data for LOB columns if it is more than 4000 bytes, since it will be stored outside the database? Does that have to do anything with the problem? However, problem is only when we want to access it using getCharacterStream and not when using getAsciiStream class.
I know it might be little confusing but there is definitely something to do with character encoding which behaves differently for data being accessed from two different databases, which are identical but running on different OS.
Any help will be highly appreciated, Tom.

Regards.


tester@TEST_Win8i> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

tester@TEST_Win8i> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ --------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 8.1.7.0.0

18 rows selected.

tester@TEST_SUN8i> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

tester@TEST_SUN8i> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ --------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT dd-mon-rrrr hh24:mi:ss
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 8.1.7.0.0

18 rows selected.

Tom,
Good news is that I figured out the problem and everything is working, the bad news is that I still do not understand the reason clearly.
As I had mentioned before, that I have two identical database with utf8 character set running on Windows 2000 and Sun OS. I logged in to Windows server and first set nls_lang=.utf8 and then exported the entire schema (the dump file was created on the Windows platform from the databse running on Windows). Then during the same session, I imported the data into the database running on the Sun OS. Everything worked fine (including some data having Chinese characters) in the database running on Sun OS, except the CLOB data, which was showing as garbage.

Dump from database running on Sun OS:
DUMP(DBMS_LOB.SUBSTR(JOB_OFFER_DESC,10,1),1016)
--------------------------------------------------------------------------------
--------------------
Typ=1 Len=20 CharacterSet=UTF8:
c5,8c,c5,a1,c4,a0,c5,b4,c5,a9,c5,b4,c5,b5,c5,ac,c5,a1,c5,a9


Dump from database running on Windows OS:
DUMP(DBMS_LOB.SUBSTR(JOB_OFFER_DESC,10,1),1016)
--------------------------------------------------------------------------------
--------------------
Typ=1 Len=10 CharacterSet=UTF8: 4c,61,20,74,69,74,75,6c,61,69

However, I did little different this time, I ftp'd the dump file from Windows platform to Sun box in the binary mode. Then logged on to the Sun OS and imported into the database running on Sun OS. Everything works fine now!

Incidentally, when I tried to export the data from the database running on Windows, while logged on to the Sun OS, I received following error:
EXP-00056: ORACLE error 3120 encountered
ORA-03120: two-task conversion routine: intege
Export terminated successfully with warnings.


As I mentioned before, I resolved the issue in trial and error fashion but not understanding what was happening. May be you can throw some light on this.

Regards.

Tom,
I had opened a TAR already. But your response is much faster ;)
Here is the header of the import log:
*****************************************************************
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
****************************************************************

Oracle Support is indicating that it had something to do with the buffer size what I had set up and possible solution they are proposing is to reduce that number in a trial and error fashion. Here is what they said:


Solution Explanation
=====================

Specify the BUFFER parameter in the Export/Import command line or in the
Export/Import parameter file. Reduce the value of BUFFER until the error is
resolved.

For example:

If BUFFER is set to 10485760 (10 MB), this will cause Export to buffer 10MB of
data before writing it to the export dump file. This would cause Import to
buffer 10MB of data before actually sending it to the database (write to
datafiles). This becomes a problem when doing Export/Import remotely. 10MB of
data will try to be sent across the network at one time. On the average a
network packet (buffer) size is 1500 bytes. So trying to send 10MB at one time
via 1500 byte packets is causing the overflow to occur.


Tom, could you explain that what is the difference in export/import being done remotely (via SQL*Net connection) and when done locally, in terms of buffer size? Does it have any bearing on the characterset encoding?

Regards,

Sanjay

Hello- I am trying to get MIO's Java code running and have some questions about it. Can one of you contact me at 410-694-5877 or by email.

I am not a Java expert by any means... but need to get the BLOBS out of this 8.1.7 database.
I used the java code posted a while back by MIO and it would not compile properly. I grabbed one of our Java Developers for a few minutes and he modified the way the db connection was done. Still does not work. It compiles but I get a runtime error with the Oracle connection.

Command Line Syntax:
C:\Oracle_Util>java LobUtils -blob -qf query.sql -lf 1.pdf

Error Message:
Exception in thread "main" java.lang.UnsatisfiedLinkError: do_open
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
at java.sql.DriverManager.getConnection(DriverManager.java:457)
at java.sql.DriverManager.getConnection(DriverManager.java:159)
at LobUtils.GetConnection(LobUtils.java:156)
at LobUtils.getClob(LobUtils.java:124)
at LobUtils.main(LobUtils.java, Compiled Code)
Code:
// file LobUtils.java
import java.io.InputStream;
import java.io.OutputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Blob;
import oracle.sql.BLOB;
import java.sql.SQLException;

import java.sql.Clob;
import oracle.sql.CLOB;

import java.io.FileReader;
import java.io.BufferedReader;
import java.io.StringWriter;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.Writer;
import java.io.InputStreamReader;
import java.sql.DriverManager;

public class LobUtils
{
final static int bBufLen = 4 * 8192;
String query;
String connectString;
String outFile;
Connection conn;

public LobUtils(String connectString, String query, String outFile) {
this.connectString = connectString;
this.query = query;
this.outFile = outFile;
this.conn = null;
}

public static void main(String[] args)
throws FileNotFoundException, IOException, SQLException {
if (args.length < 5) usage();
int ii = 0;
String connString = args[ii++];
String queryFile = null;
String outputFile = null;

boolean read = true;
boolean isBinary = false;

for (; ii < args.length; ii++) {
if (args[ii].equals("-write"))
read = false;

if (args[ii].equals("-blob"))
isBinary = true;

if (args[ii].equals("-qf") && ii < args.length - 1)
queryFile = args[++ii];

if (args[ii].equals("-lf") && ii < args.length - 1)
outputFile = args[++ii];

}

if (queryFile == null || outputFile == null) usage();

// all set
if (read) {
BufferedReader freader = new BufferedReader(new
FileReader(queryFile));
StringWriter swriter = new StringWriter();
int bufLen = 1024;
char[] cbuf = new char[bufLen];
int length = -1;
while ((length = freader.read(cbuf, 0, bufLen)) != -1) {
swriter.write(cbuf, 0, length);
}
freader.close();
swriter.close();
String query = swriter.toString();

LobUtils lutils = new LobUtils(connString, query, outputFile);
if (isBinary) {
Blob blob = lutils.getBlob();
long wrote = lutils.writeBlobToFile(blob);
System.out.println("Wrote " + wrote + " bytes to file " +
outputFile);
} else {
Clob clob = lutils.getClob();
long wrote = lutils.writeClobToFile(clob);
System.out.println("Wrote " + wrote + " bytes to file " +
outputFile);
}
} else {
BufferedReader freader = new BufferedReader(new
FileReader(queryFile));
StringWriter swriter = new StringWriter();
int bufLen = 1024;
char[] cbuf = new char[bufLen];
int length = -1;
while ((length = freader.read(cbuf, 0, bufLen)) != -1) {
swriter.write(cbuf, 0, length);
}
freader.close();
swriter.close();
String query = swriter.toString();

LobUtils lutils = new LobUtils(connString, query, outputFile);
Clob clob = lutils.getClob();
InputStream creader = new FileInputStream(outputFile);
long wrote = lutils.writeToOraClob(clob, creader);
System.out.println("Wrote " + wrote + " bytes from file " +
outputFile);
}
}

public Clob getClob()
throws SQLException {
conn = GetConnection(connectString);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Clob clob = null;
if (rs.next()) {
clob = rs.getClob(1);
}
return clob;
}

public Blob getBlob()
throws SQLException {
conn = GetConnection(connectString);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Blob blob = null;
if (rs.next()) {
blob = rs.getBlob(1);
}
return blob;
}

public Connection GetConnection(String connectString) {
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");

String tns = "jdbc:oracle:oci8:dms_user/ciena@pdmaglsc";
String user = "dms_user";
String pwd = "ciena";

return DriverManager.getConnection(tns);
}
catch (Exception e) {
e.printStackTrace();
return null;
}
}

public long writeClobToFile(Clob clob)
throws IOException, SQLException {
long wrote = 0;
BufferedWriter fwriter = new BufferedWriter(new FileWriter(outFile));
wrote = readFromClob(clob, fwriter);
fwriter.close();
conn.commit();
conn.close();
return wrote;
}

public long writeBlobToFile(Blob blob)
throws IOException, SQLException {
long wrote = 0;
OutputStream fwriter = new FileOutputStream(outFile);
wrote = readFromBlob(blob, fwriter);
fwriter.close();
conn.commit();
conn.close();
return wrote;
}

private static void usage() {
System.err.println("Usage: java LobUtils user/passwd@sid [-write] [-blob] -qf query_file -lf lob_file");
System.exit(1);
}

public static long writeToOraBlob(Blob blob, InputStream in)
throws SQLException, IOException {
BLOB oblob = (BLOB)blob;
OutputStream out = oblob.getBinaryOutputStream();
int length = -1;
long wrote = 0;
int chunkSize = oblob.getChunkSize();
byte[] buf = new byte[chunkSize];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
wrote += length;
}
out.close();
return wrote;
}

public long writeToOraClob(Clob clob, InputStream in)
throws SQLException, IOException {
CLOB oclob = (CLOB)clob;
OutputStream out = oclob.getAsciiOutputStream();
int length = -1;
long wrote = 0;
int chunkSize = oclob.getChunkSize();
byte[] buf = new byte[chunkSize];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
wrote += length;
}
out.close();
conn.commit();
return wrote;
}

public static long readFromBlob(Blob blob, OutputStream out)
throws SQLException, IOException {
InputStream in = blob.getBinaryStream();
int length = -1;
long read = 0;
byte[] buf = new byte[bBufLen];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}

public static long readFromClob(Clob clob, Writer out)
throws SQLException, IOException {
BufferedReader in = new BufferedReader(new
InputStreamReader(clob.getAsciiStream()));
int length = -1;
long read = 0;
char[] buf = new char[bBufLen];
while ((length = in.read(buf, 0, bBufLen)) != -1) {
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}
}


scover@ciena.com

Any Help is good at this point!

I have the PATH setup to include the the path $ORACLE_HOME/jdbc/lib/classes12.zip but no CLASSPATH on the Windows machine running this program. SQLPLUS runs fine. Something is missing not sure what though. Is there another way I can connect to the database using thinclient technology?

Do you have the "ConnUtil" code specified in the original code?

Steve
scover@ciena.com

Using 8.1.7 thick driver to connect to 8.1.6 DB on NT:
</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=191101.999

Missing PATH and CLASSPATH environment variables:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=205999.999 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=130121.1 <code>

All-
I am not having any luck with the JDBC connection. Driving me nuts. I am under the gun to get some sample documents out of this database by COB today.

Can you provide any assistance with a complete solution to pull the files (BLOBS) out. I have the below procedure compiled in the database, it runs, but nothing happens. How can I WRITE THE FILE to disk in the SQL.

None of these solutions posted is straightforward enough for a semi-literate developer like me.
I need some additional detail if possible.

procedure file_download(p_file_id tl_docs.obj_id%type)
as
l_lob tl_docs.blob_obj%type; l_name te_sachnr.obj_id%type;
begin
select f.blob_obj, a.obj_id
into l_lob, l_name
from tl_docs f, te_sachnr a
where f.obj_id = p_file_id;
owa_util.mime_header('application/pdf');
owa_util.http_header_close;
wpg_docload.download_file(l_lob);
end;

scover@ciena.com

I had our DBA import this schema with the tables containing BLOBS into a 9i database. After reading many of these pages it "appears" although I am not entirely sure, that in 9i pulling OUT Blob data is much easier. Is this true?

I seem to remember reading an official answer from Tom that indicates BLOBs cannot be pulled out of an 8.1.7 db with just SQL.

Thanks,
Steev
scover@ciena.com

I am now using a PROCEDURE to extract files from a 9i db. I have an expert SQL programmer and a DBA helping me with this. We have not been able to extract more that one file at a time. When using the cursor in the below proc we get the following error:

Error in file: C10000007-A01~PCB AFE~CA1-C10000001---paneldwg---01.pdf
Error #: 100
Error Message: ORA-01403: no data found
End of BLOB
End Time: 16-JUN-04 10:13:25


Help!!
-----------------------------------------------------------

PROCEDURE RET_LOBS IS

temp_blob BLOB;
data_buffer RAW (1);
temp_buffer VARCHAR2(1);
amount BINARY_INTEGER := 1;
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(500);
length_count INTEGER;
obj_id VARCHAR2(500);
l_filename VARCHAR2(500);

cursor c_blob is
select
ltrim(tsa.obj_id,'001000')||'~'||
tsa.COMP_NAME||'~'||
ltrim(replace(trd.compdoc_id,':','---'),'11000')||'.'||
tdc.FILETYPE blob_file,
tdc.obj_id
from
tr_doc trd,
te_sachnr tsa,
tl_docs tdc
where
tdc.obj_id = trd.compdoc_id and
trd.catena_doc_id = tsa.obj_id and
tdc.filetype ='pdf'
and tdc.obj_id is not null;

BEGIN

dbms_output.put_line('Start Time: ' || to_char(sysdate,'DD-MON-RR HH24:MI:SS'));


FOR v_blob IN c_blob LOOP

begin
dbms_output.put_line('==================================================================');
dbms_output.put_line('File : '||v_blob.blob_file);
SELECT BLOB_OBJ INTO temp_blob FROM tl_docs WHERE obj_id = v_blob.obj_id and rownum = 1;

length_count := dbms_lob.getlength(temp_blob);

dbms_output.put_line('Internal LOB size is: ' || length_count);

l_filename := v_blob.blob_file;


filehandle := utl_file.fopen('/opensource/dms_user',l_filename,'W',32767);
WHILE length_count <> 0 LOOP

dbms_lob.read (temp_blob, amount, position, data_buffer);

temp_buffer := utl_raw.cast_to_varchar2(data_buffer);

utl_file.put(filehandle, temp_buffer);

utl_file.fflush(filehandle);

position := position + 1;

length_count := length_count - 1;

data_buffer := null;

END LOOP;

dbms_output.put_line('Close the file');
utl_file.fclose(filehandle);

--end if;

exception
when others then
dbms_output.put_line('Error in file: '||l_filename);
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose(filehandle);

end;

END LOOP; --c_blob

dbms_output.put_line('End of BLOB');

dbms_output.put_line('End Time: ' || to_char(sysdate,'DD-MON-RR HH24:MI:SS'));

EXCEPTION
WHEN NO_DATA_FOUND then
dbms_output.put_line('Error in file: '||l_filename);
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;

Thanks, Steve Cover
scover@ciena.com

Trying to retrieve a clob from Java for writing
(92 and 10g - same problems)

--schema
scott@ORA92I> drop table clob_table;
drop table clob_table
*
ERROR at line 1:
ORA-00942: table or view does not exist


scott@ORA92I> create table clob_table
2 (
3 clob_col clob
4 );

Table created.

scott@ORA92I>
scott@ORA92I> insert into clob_table (clob_col)
2 values ( 'A clob example' );

1 row created.

scott@ORA92I>
scott@ORA92I> commit;

Commit complete.

scott@ORA92I>
scott@ORA92I> select clob_col from clob_table;

A clob example
----- Java program

// importing standard JDBC classes under java.sql class hierarchy
import java.sql.*;
import java.io.*;

//import oracle specific classes
import oracle.jdbc.*;
import oracle.sql.*;

class Bug
{
public static void main(String args[])
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver( new OracleDriver());

String thinDriverURL = "jdbc:oracle:thin:@my_host:1521:ora92i";
conn = DriverManager.getConnection ( thinDriverURL, "scott",
"tiger" );
String stmtString =
"select clob_col from clob_table";
pstmt = conn.prepareStatement( stmtString );

pstmt.execute();
rset = pstmt.getResultSet();

while( rset.next() )
{
Clob clob = rset.getClob( 1 );
Writer writer= ((CLOB) clob).setCharacterStream(1L);
writer.close();
}
}
catch (Exception e)
{
// handle the exception properly - in this case, we just
// print a message and stack trace.
e.printStackTrace();
}
finally
{
// release resources associated with JDBC
// in the finally clause.
try
{
if( conn != null) { conn.close();}
}
catch( Exception e) {}
}
}
}
-------
two questions:
1- Why do I get Unsupported feature exception
when I run this program - the documentation
says that setCharacterStream() is supported?
2. if I change the select to have a "for update"
I get a "fetch out of sequence" in 9i..Why?

well, for 10g - i can use getCharacterOutputStream()
and make it work (with a for update clause)..
...

// importing standard JDBC classes under java.sql class hierarchy
import java.sql.*;
import java.io.*;

//import oracle specific classes
import oracle.jdbc.*;
import oracle.sql.*;

class Bug
{
public static void main(String args[])
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver( new OracleDriver());

String thinDriverURL = "jdbc:oracle:thin:@my_host:1521:ora92i";
conn = DriverManager.getConnection ( thinDriverURL, "scott",
"tiger" );
String stmtString =
"select clob_col from clob_table";
pstmt = conn.prepareStatement( stmtString );

pstmt.execute();
rset = pstmt.getResultSet();

while( rset.next() )
{
Clob clob = rset.getClob( 1 );
Writer writer= ((CLOB) clob).setCharacterStream(1L);
writer.close();
}
}
catch (Exception e)
{
// handle the exception properly - in this case, we just
// print a message and stack trace.
e.printStackTrace();
}
finally
{
// release resources associated with JDBC
// in the finally clause.
try
{
if( conn != null) { conn.close();}
}
catch( Exception e) {}
}
}
}
...

But for 9i - this still gives fetch out of sequence
error - and if I dont have the "for update" it says
that the row contianing the lob value is not locked!:)

the autocommit must be the issue - I completely
forgot about it - yes it is a terrible default
to have - but I guess as with the case with
defaults - once you have it - it is not easy to change it:)

Yup they ended up following the odbc model which
does not make sense from a transaction perspective:)

btw, I got the above error from the 10g doc...
( 9i doc shows the correct usage of getCharacterOutputStream()...)

but 10g doc gives the following example...
// write the array of character data to a CLOB
writer = ((CLOB)my_clob).setCharacterStream();
...

This does not compile and I assumed that they
meant setCharacterStream( long pos) method which
gave the Unsupported feature error..

Thank you!

Tom,

The BLOB is holding different types of documents such as .xls, .doc, .jiff etc in one column. I need to retrieve the contents of the blob in appropriate format. For eg. A Word document should be retrieved as .Doc, A Excel should be .Xls etc. With your C program we could retrieve the first 40 bytes which gives us what kind of document it is but still we are not able to catch all the types. what is the best way to do it?

Thanks,

Tom,
If the bfile points to a text file and I try
to use the bfile's asciiStreamValue() method to
get it as a stream of ascii characeters, it still
seems to return binary stream (as bytes.) Objective
is to print the characters as ascii text back from the
bfile using JDBC. I was able to do it using
dbms_lob - but if I try to use JDBC's stream functions
I am not able to get it done...Of course one way
is to use dbms_lob from JDBC..

Any ideas?
Thank you!

You use getBinaryStream() - then you cast each byte
into char to print it out...found an example in the
Oracle JDBC guide..
---
bfile.openFile();
// get the BFILE data as a binary stream
InputStream in = bfile.getBinaryStream();
int length ;
// read the bfile data in 6-byte chunks
byte[] buf = new byte[6];
while ((length = in.read(buf)) != -1)
{
// append and display the bfile data in 6-byte chunks
StringBuffer sb = new StringBuffer(length);
for (int i=0; i<length; i++)
sb.append( (char)buf[i] );
System.out.println(sb.toString());
}
---

Thanx!

You are too fast! - while I was typing above response
you were typing the same answer:)

Anyways one small addition - instead of using
the StringBuffer() in a loop, one can also use the
the String overloaded constructor as follows..
(orset = OracleResultSet variable)
----
while( orset.next() )
{
BFILE bfile = orset.getBfile( 1 );
bfile.openFile();
InputStream in = bfile.getBinaryStream();

while (( length = in.read( buffer)) != -1 )
{
System.out.print( new String( buffer, 0,
length ) );
}
}

yes - external tables is definitely another option...

on second thoughts external tables are perhaps more
suited for "structured" data - for unstructured data
such as a free format text - perhaps it does not
offer any advantages. In fact LOBs would be a better
alternative since they can be read piece-wise/randomly
in a natural fashion...If your data is structured
and follows a "queriable" format - then external tables
might be a better option (again depends on your
requirements.)

you can not do stream operations such as seek/piecewise
fetch etc easily on the external tables/result set...plus if you declare a column of varchar2(4000), the client has to pre-allocate that much for each row in the prefetch
(for example, if you prefetch 15 rows, you have to
preallocate 15*4000 bytes (even if each row
consists of only 1 character say for an extreme
example)...So there may be performance issues...
Having said that I do see your point of 4000 being
ok for most cases...

I am a fan of PL/SQL butwhat if we need to select chunks of clob data from database in JDBC layer? :) (Come on Tom
you can have someone else have a last word once in a while,
eh?;))

where can i find this ftp.pkb?

This code worked very well for me extracting BLOBS directly connected to a 9i database on a UNIX server. The UTL_FILE setting needs to be setup on the database your extracting from. Thanks to all who helped me get to this...
Steve


PROCEDURE RET_CC0A_PDF IS

temp_blob BLOB;
data_buffer RAW (1);
temp_buffer VARCHAR2(1);
amount BINARY_INTEGER := 1;
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(500);
length_count INTEGER;
obj_id VARCHAR2(500);
l_filename VARCHAR2(500);

cursor c_blob is

SELECT
substr(a.obj_id, 7)||'--'|| --PN
substr(a.her, 7)||'--'|| --MFGR
a.katalog||'--'|| --MFG_PART_NUM
replace(substr(a.doc_id, 7),':',' ')||'.'|| --DOC_ID
b.filetype blob_file, -- FILE_EXT
b.obj_id
FROM tr_her_snr a,
tl_docs b
WHERE -- PDF FILE EXTRACTIONS
a.doc_id = b.obj_id
and a.doc_ID LIKE '%datasheet%'
and (a.obj_id like '%CC0%' ) -- 5733 files to extract
and b.filetype ='pdf'
and substr(a.her, 7) like 'A%';

BEGIN

dbms_output.put_line('Start Time: ' || to_char(sysdate,'DD-MON-RR HH24:MI:SS'));

FOR v_blob IN c_blob LOOP

begin

dbms_output.put_line('==================================================================');
dbms_output.put_line('File : '||v_blob.blob_file);
SELECT BLOB_OBJ INTO temp_blob FROM tl_docs WHERE obj_id = v_blob.obj_id and rownum = 1;

length_count := dbms_lob.getlength(temp_blob);

dbms_output.put_line('Internal LOB size is: ' || length_count);

l_filename := v_blob.blob_file;


filehandle := utl_file.fopen('/opensource/dms_user/pdfs/cc0/a',l_filename,'W',32767);
data_buffer := null;
temp_buffer := null;
amount := 1;
position := 1;
WHILE length_count <> 0 LOOP

dbms_lob.read (temp_blob, amount, position, data_buffer);

temp_buffer := utl_raw.cast_to_varchar2(data_buffer);

utl_file.put(filehandle, temp_buffer);

utl_file.fflush(filehandle);

position := position + 1;

length_count := length_count - 1;

data_buffer := null;


END LOOP;

dbms_output.put_line('Close the file');
utl_file.fclose(filehandle);

--end if;

exception
when others then
dbms_output.put_line('Error in file: '||l_filename);
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose(filehandle);

end;

END LOOP; --c_blob

dbms_output.put_line('End of BLOB');

dbms_output.put_line('End Time: ' || to_char(sysdate,'DD-MON-RR HH24:MI:SS'));

EXCEPTION
WHEN NO_DATA_FOUND then
dbms_output.put_line('Error in file: '||l_filename);
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;

as pink floyd likes to sing "careful with that axe eugene"


o data streams that continue for more than 32k characters without a newline will blow this up

o data streams including nastiness like binary zero'es and who knows what else -- bamm.

o newline added to end of file...


that is not a reliable routine for binary data at all.

consider these two binary objects:

ops$tkyte@ORA9IR2> declare
  2          l_raw  raw(256);
  3          l_blob blob;
  4  begin
  5          for i in 0 .. 255
  6          loop
  7                  l_raw := l_raw || hextoraw( to_char(i,'fm0X') );
  8          end loop;
  9          insert into t values ( l_raw );
 10          insert into t values ( l_raw ) returning x into l_blob;
 11
 12          l_raw := null;
 13          for i in 1 .. 255
 14          loop
 15                  l_raw := l_raw || hextoraw( 'fe' );
 16          end loop;
 17
 18          for i in 1 .. 200
 19          loop
 20                  dbms_lob.writeappend( l_blob, 255, l_raw );
 21          end loop;
 22          commit;
 23  end;
 24  /
 
PL/SQL procedure successfully completed.
 


Now, using your procedure to unload those two we find:

ops$tkyte@ORA9IR2> exec RET_CC0A_PDF
Internal LOB size is:  256
Close the file
Internal LOB size is:  51256
BEGIN RET_CC0A_PDF; END;
 
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 993
ORA-06512: at "OPS$TKYTE.RET_CC0A_PDF", line 30
ORA-06512: at line 1


The first extracted 256 bytes -- but:

 
ops$tkyte@ORA9IR2> !ls -l /tmp/foo.dat.1
-rw-rw-r--    1 ora9ir2  ora9ir2       257 Jul  8 15:20 /tmp/foo.dat.1
 

it is 257 bytes in the file system and od shows:


ops$tkyte@ORA9IR2> !od -c /tmp/foo.dat.1
0000000  \0 001 002 003 004 005 006  \a  \b  \t  \n  \v  \f  \r 016 017
0000020 020 021 022 023 024 025 026 027 030 031 032 033 034 035 036 037
0000040       !   "   #   $   %   &   '   (   )   *   +   ,   -   .   /
0000060   0   1   2   3   4   5   6   7   8   9   :   ;   <   =   >   ?
0000100   @   A   B   C   D   E   F   G   H   I   J   K   L   M   N   O
0000120   P   Q   R   S   T   U   V   W   X   Y   Z   [   \   ]   ^   _
0000140   `   a   b   c   d   e   f   g   h   i   j   k   l   m   n   o
0000160   p   q   r   s   t   u   v   w   x   y   z   {   |   }   ~ 177
0000200 200 201 202 203 204 205 206 207 210 211 212 213 214 215 216 217
0000220 220 221 222 223 224 225 226 227 230 231 232 233 234 235 236 237
0000240 240 241 242 243 244 245 246 247 250 251 252 253 254 255 256 257
0000260 260 261 262 263 264 265 266 267 270 271 272 273 274 275 276 277
0000300 300 301 302 303 304 305 306 307 310 311 312 313 314 315 316 317
0000320 320 321 322 323 324 325 326 327 330 331 332 333 334 335 336 337
0000340 340 341 342 343 344 345 346 347 350 351 352 353 354 355 356 357
0000360 360 361 362 363 364 365 366 367 370 371 372 373 374 375 376 377
0000400  \n
0000401

newline added to the end.  

The second issue is a problem -- no newline = no output.....


I would not use this routine for binary data.

Also, the exception blocks without a RAISE!!! that's a bug.  error messages are way to easy to just read over -- you want a real error raised (i chopped them all out for this example)
 

works fine in pl/sql as shown above..Any idea why
it does not print characters when executed from
sql in a select statement?

Thanx!

sorry it does work!:)

read binary files? I know we have other ways of
doing this - but I am just curious...

Tom
Interesting discussion on bfile versus
external tables..

I did a small benchmark to compare reading
from bfile to that with external table.
file had 10K lines - total 198894 characters -
an average of around 20 characters per line.

Took care to open and prepare statements
only once in both cases..

In the first run:
For external table - I used normal result set based
select (default prefetch of 10 records)

For bfile - I used standard functionality with a
buffer of (prefetch * average row size) = 10*20
to hopefully make the comparison fair. Bfile was
first inserted in a simple table and then there was
a select of bfile column in the JDBC becnhmark.

Following were the results:
Bfile: took an average of 54 millisecs to read 10K records
External table: took an average of 958 ms to read 10K
records.

After this I changed the setting of external table
select to prefetch 500 records at a time (instead
of the default of 10). Also set the Bfile buffer
size to 500*20

The results were:

Bfile: took an average of 53 millisecs to read 10K records
External table: took an average of 321 ms to read 10K
records.

From the benchmark results, it seems bfiles
are faster by a huge margin (at least in above cases:))
- and it is at least worth benchmarking in your particular application before making a choice taking into consuderation ease of use, performance etc in your particular case.

"what do you mean by reading "10k records from a bfile"? Not really sure what you were comparing here.
"
Sorry I was not clear.
I created a file containing 10K *lines* that looked like:
my line number 1
my line number 2
..
my line number 10000

-----
I then inserted a bfile column in a table pointing
to this file. I also created an external table
(with "records delimited by newline" option.

Then from JDBC I tried to select all lines
as mentioned above using bfile and
external table..
Hope I am clear now..

Thanx!

"a) retrieving 10k lines of data using an external table"

Correct

"b) retrieving a bfile lob locator of a couple of bytes in size. "

Nopes -> I retrieved the locator and the data in
a loop (verified that the data is actually
being retrieved...
--something like:
while( orset.next() )
{
bfile = orset.getBfile( 1 );
bfile.openFile();
in = bfile.getBinaryStream();
numOfCharacersRead = 0;

while (( length = in.read( buffer, 0, buffer.length )) != -1 )
{
...
}
...
I actually mentioned above the logic of buffer
size as well (prefetch size for external table
case * number of characters per row)

let me know if i missed anything...

Thanx!

I had to edit it to make it in one program...I have
some suspicions on the results also... that I
detail at the end of this post...

First the schema....
-------
benchmark@ORA10G> -- NOTE: You need to replace the directory and file with
benchmark@ORA10G> -- a real directory in your machine for all of
benchmark@ORA10G> -- these examples to work.
benchmark@ORA10G>
benchmark@ORA10G> create or replace directory my_dir as '/private/ora92/book/ch09';

Directory created.

benchmark@ORA10G>
benchmark@ORA10G>
benchmark@ORA10G> -- the following writes to a file creating the
benchmark@ORA10G> -- files that will be used in benchmarking reads
benchmark@ORA10G>
benchmark@ORA10G> declare
2 l_buffer varchar2(32767);
3 l_file_handle utl_file.file_type;
4 begin
5 -- open the file in write mode -- create one if
6 -- file does not exist.
7
8 l_file_handle := utl_file.fopen( 'MY_DIR', 'benchmark_input.txt', 'W',
9 256 );
10 for i in 1 .. 10000
11 loop
12 utl_file.put_line( l_file_handle, 'my line number ' || i );
13 end loop;
14 utl_file.fclose( l_file_handle );
15 exception
16 when others then
17 raise;
18 end;
19 /

PL/SQL procedure successfully completed.

benchmark@ORA10G> drop table bfile_table;

Table dropped.

benchmark@ORA10G> create table bfile_table
2 (
3 x varchar2(30),
4 id number,
5 bfile_col bfile
6 );

Table created.

benchmark@ORA10G> insert into bfile_table( x, id, bfile_col )
2 values ( 'benchmark text file', 1,
3 bfilename( 'MY_DIR', 'benchmark_input.txt' ));

1 row created.

benchmark@ORA10G> commit;

Commit complete.

benchmark@ORA10G>
benchmark@ORA10G> -- create an external table that points to the
benchmark@ORA10G> -- benchmark_input.txt file - note that
benchmark@ORA10G> -- we specify that records are delimited
benchmark@ORA10G> -- by newlines top indicate a "free format
benchmark@ORA10G> -- text file
benchmark@ORA10G>
benchmark@ORA10G> drop table et_table;

Table dropped.

benchmark@ORA10G> create table et_table
2 (
3 data varchar2(4000)
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory my_dir
9 access parameters
10 (
11 records delimited by newline
12 )
13 location( 'benchmark_input.txt' )
14 );

Table created.

benchmark@ORA10G>
benchmark@ORA10G> -- verify the count of records in external table
benchmark@ORA10G>
benchmark@ORA10G> select count(*) from et_table;

10000

benchmark@ORA10G> spool off


Then the JDBC program...
-------------------

/*
* IMPORTANT: You need to modify the connection information
* to point to your own database for this example to work.
*
*/


// importing standard JDBC classes under java.sql class hierarchy
import java.sql.*;
import java.io.*;

//import oracle specific classes
import oracle.jdbc.*;
import oracle.sql.*;

class CompareReadUsingBfileAndExternalTable
{
public static void main(String args[])
{
_checkProgramUsage( args );
Connection conn = null;
try
{
conn = _getConnection("benchmark", "benchmark",
args[0]);

_prepareBenchmarkStatements( conn );
_runBenchmark( BFILE_DESC , conn );
_runBenchmark( EXTERNAL_TABLE_DESC , conn );
}
catch (Exception e)
{
// handle the exception properly - in this case, we just
// print a message and stack trace.
e.printStackTrace();
}
finally
{
// release resources associated with JDBC
// in the finally clause.
try
{
_closeBenchmarkStatements( conn );
conn.close();
}
catch (Exception e) { }
}
}

private static void _runBenchmark( String benchmarkDesc,
Connection conn )
throws SQLException, IOException
{
System.out.println("In _runBenchmark for " + benchmarkDesc);

// find out how much time a single run takes
long startTime = System.currentTimeMillis();
long numOfCharactersRead = 0;
if( BFILE_DESC.equals( benchmarkDesc ) )
numOfCharactersRead = _readUsingBfile( conn );
else
numOfCharactersRead = _readUsingExternalTable( conn );

long endTime = System.currentTimeMillis();

// find out the number of runs that would consume 5 minutes

int numOfRuns = (int)((5 * 60 * 1000)/( endTime-startTime ));
System.out.println( benchmarkDesc + ": time for first run = " +
(endTime-startTime) );
System.out.println( benchmarkDesc + ": num of runs = " +
numOfRuns );

// if num of runs = 0 - print a message and reset it to 1
if( numOfRuns == 0 )
{
System.out.println( "One run took more than 5 minutes." );
numOfRuns = 1;
}

startTime = System.currentTimeMillis();

for(int i=0; i < numOfRuns; i++ )
{
if( BFILE_DESC.equals( benchmarkDesc ) )
numOfCharactersRead = _readUsingBfile( conn );
else
numOfCharactersRead = _readUsingExternalTable( conn );
}

endTime = System.currentTimeMillis();

long averageRunTime = (endTime-startTime)/numOfRuns;
System.out.println( benchmarkDesc + " took " +
averageRunTime + " ms for reading " +
numOfCharactersRead + " characters\n\n");
}

/*
demos how to read from a text file from the database as an ascii
file using bfile. returns number of characters read
*/

private static long _readUsingBfile( Connection conn )
throws SQLException, IOException
{
OracleResultSet orset = null;
BFILE bfile = null;
InputStream in = null;
long numOfCharacersRead = 0;
try
{
_bfilePstmt.setInt( 1, 1 );
orset = (OracleResultSet) _bfilePstmt.executeQuery();
byte[] buffer = new byte[ _bfileBufferSize];
int length = -1;
while( orset.next() )
{
bfile = orset.getBfile( 1 );
bfile.openFile();
in = bfile.getBinaryStream();
numOfCharacersRead = 0;

while (( length = in.read( buffer, 0, buffer.length )) != -1 )
{
//System.out.print( new String( buffer, 0, length ) );
numOfCharacersRead += length;
}
//System.out.println("Bfile: num of chars read: " + numOfCharacersRead);
}
}
finally
{
if( in != null )
in.close();
if( bfile != null )
bfile.closeFile();
try
{
orset.close();
}
catch (Exception e) { }
}

return numOfCharacersRead;
}

/*
demos how to read from a text file from the database as an ascii
file.
*/

private static long _readUsingExternalTable( Connection conn )
throws SQLException, IOException
{
ResultSet rset = null;
long numOfCharacersRead = 0;
try
{
rset = _externalTablePstmt.executeQuery();
numOfCharacersRead = 0;
while( rset.next() )
{
String line1 = rset.getString(1);
numOfCharacersRead += line1.length();
}
//System.out.println("External Table: num of chars read: " + numOfCharacersRead);
}
finally
{
try
{
rset.close();
}
catch (Exception e) { }
}
return numOfCharacersRead;
}

private static void _prepareBenchmarkStatements( Connection conn )
throws SQLException
{
String stmtString = "select data from et_table";
_externalTablePstmt = conn.prepareStatement( stmtString );
((OraclePreparedStatement)_externalTablePstmt).setRowPrefetch(
_prefetchSize );

stmtString = "select bfile_col from bfile_table "+
" where id = ?";
_bfilePstmt = conn.prepareStatement( stmtString );
}

private static void _closeBenchmarkStatements( Connection conn )
throws SQLException
{

try
{
_bfilePstmt.close();
_externalTablePstmt.close();
}
catch (Exception e) { }
}

private static Connection _getConnection( String username,
String password, String dbName)
throws SQLException
{
DriverManager.registerDriver( new OracleDriver());

String thinDriverURL =
"jdbc:oracle:thin:@usunrat24:1521:"+ dbName;
Connection connection =
DriverManager.getConnection ( thinDriverURL, username, password);
connection.setAutoCommit( false );

return connection;
}

private static void _checkProgramUsage( String[] args )
{
if( args.length != 1 )
{
System.out.println(" Usage: java <program_name> <database_name>");
System.exit(1);
}
}

private static final String BFILE_DESC = "Using Bfile";
private static final String EXTERNAL_TABLE_DESC =
"Using external table";

private static PreparedStatement _bfilePstmt;
private static PreparedStatement _externalTablePstmt;

private static int _prefetchSize = 20;
private static int _bfileBufferSize = _prefetchSize * 20;
}
-----
then the somewhat suspicious results
------

usunrat24 ora10g 873> java CompareReadUsingBfileAndExternalTable ora10g
In _runBenchmark for Using Bfile
Using Bfile: time for first run = 250
Using Bfile: num of runs = 1200
Using Bfile took 56 ms for reading 198894 characters


In _runBenchmark for Using external table
Using external table: time for first run = 1167
Using external table: num of runs = 257
Using external table took 937 ms for reading 188894 characters
.......

And finally some comments...
1. Notice that the character count is off
by the number of lines in the file - most
likely a newline is not getting counted...

This is most likely because in bfile you actually
read the file whereaas in the select from external
table since the separator is new line - it is
not returned as the data by the select...

2. The first run of bfile takes around 250 ms..
but subsequent runs are suspiciously fast (this is not due
to parsing) - May be some kind of optimization
or may be there is a bug in the program. Perhaps
related to the way file is read (if there is no bug)

So hopefully all mysteries will be solved
once you have gone through the program :-)

btw, the reason I take an average is to
get the JVM to a steady state - this is a
better way of measuring performance when
you use only elapsed time...

PS: Thanx for this wonderful site - just finished
explaining to a disbelieving colleague about
your site and the fact there is only one
tom that handles almost all these questions
on this site (and that it is not your full
time job!)!:)

HI TOM

I am facing a problem i.e
A table is having column clob and its szie get increse 46gb in one hour.
Its really bothering me.
Lob tablespace inital and next extent are 32 mb
data tablespace ( table segemnt) initial and next extents 4 mb

please suggest


i have doubt as cout of table is 52 million is it a reason or something wrong with lob

that I posted above - I know you are on vacation - but
I am curious on your thoughts...(Don't mean to hurry you
of course..)

Thanx!

Using the code below I have successfully extracted 6500 BLOBS from a 9i database.
The BLOBS for the most part are all good valid complete files. However, I have a small number "250" ,that have not extracted properly (the files are incomplete). Only part of the file was pulled from the database. This test is repeatable in re-testing on a bad file and always stops at the same byte count. 
There has been some discussion as to whether the "UTL_FILE method" is the right way to do these type of binary file extracts. If not what is the best way. The code I am using is originally off of METALINK so should be OK ???
Thanks,
Steve

CODE:
 dbms_output.put_line('File : '||v_blob.blob_file);
    SELECT BLOB_OBJ INTO temp_blob FROM tl_docs WHERE obj_id = v_blob.obj_id and 
rownum = 1;

    length_count := dbms_lob.getlength(temp_blob);

    dbms_output.put_line('Internal LOB size is:  ' || length_count);

    l_filename := v_blob.blob_file;


    filehandle := utl_file.fopen('/opensource/dms_user',l_filename,'W',32767);
    WHILE length_count <> 0 LOOP

       dbms_lob.read (temp_blob, amount, position, data_buffer);

       temp_buffer := utl_raw.cast_to_varchar2(data_buffer);

       utl_file.put(filehandle, temp_buffer);

       utl_file.fflush(filehandle);

       position := position + 1;

       length_count := length_count - 1;

       data_buffer := null;

    END LOOP;

    dbms_output.put_line('Close the file');
    utl_file.fclose(filehandle);

ERROR:
SQL> execute ret_tst
Start Time: 01-SEP-04 11:22:02
==================================================================
File : CC0000008~AMD~1237661~AM29LV640DU90WHI~StatA~Doc1~AM29LV640DU90 datasheet
01.pdf
Internal LOB size is:  1237661
Error in file: CC0000008~AMD~1237661~AM29LV640DU90WHI~StatA~Doc1~AM29LV640DU90
datasheet 01.pdf
Error #: -29285
Error Message: ORA-29285: file write error
==================================================================
File : CC0000008~AMD~1237661~AM29LV640DU90RWHI~StatA~Doc1~AM29LV640DU90
datasheet 01.pdf
Internal LOB size is:  1237661
Error in file: CC0000008~AMD~1237661~AM29LV640DU90RWHI~StatA~Doc1~AM29LV640DU90
datasheet 01.pdf
Error #: -29285
Error Message: ORA-29285: file write error
End of BLOB
End Time:  01-SEP-04 11:51:47

PL/SQL procedure successfully completed. 

I have modified the timing of a job (Materialized view) through this command.

SQL>exec dbms_job.next_date(173,sysdate+12/24);

it completed successfully.
when i reweived through dba_jobs .

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER                    LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC TOTAL_TIME B
-------- ---------- -
INTERVAL
--------------------------------------------------------------------------------
  FAILURES
----------
WHAT
--------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------
MISC_ENV                                                           INSTANCE
---------------------------------------------------------------- ----------
       173 CALLCENTER                     CALLCENTER
CALLCENTER                     02-SEP-04 14:33:38                    03-SEP-04
05:30:45       2831 N
sysdate + 24/24
         0
dbms_refresh.refresh('"CALLCENTER"."CMSS_BILL_INFO"');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000                                                          0

and when i checked it through some other machine it shown no changes in the schedule...
i am really confused why is it so ??

Why dba_jobs is showing different output when fired from  differnet terminal even though i am connecting to same user in the same database .....

Looking for ur reply... sorry i am puting my query in the review part as i didnot get any other way to put it ... plz let me know if any other way is there ..

 

Sir ,
i m unable to understand .. can you describe it plz ...
( i m not deleting anything )

I deleted -- that is what I said "instead of me just deleting this again".  You posted this (much like here) in a thread that has nothing whatsoever to do with dbms_job.


the one word is "commit"

in session one you did an "update" in effect.  This update is visible to session 1.


in session two, you did a query. This query cannot see uncommitted work in progress by other sessions.


So, the one word is "commit", commit session 1.  Then session 2 will be able to "see" it.


consider the following, the autonomous transactions represent exactly "your other session"


ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'null;', sysdate+1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select job, what, next_date from user_jobs where job = :n;
 
       JOB WHAT                           NEXT_DATE
---------- ------------------------------ ---------
         2 null;                          04-SEP-04
 
<b>Now you see it...</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          dbms_output.put_line( 'In another transaction....' );
  5          for x in ( select * from user_jobs where job = :n )
  6          loop
  7                  dbms_output.put_line( x.job || ', ' || x.what || x.next_date );
  8          end loop;
  9          dbms_output.put_line( 'Done with another transaction....' );
 10          commit;
 11  end;
 12  /
In another transaction....
Done with another transaction....
 
PL/SQL procedure successfully completed.
 
<b>Now you don't, so COMMIT:</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          dbms_output.put_line( 'In another transaction....' );
  5          for x in ( select * from user_jobs where job = :n )
  6          loop
  7                  dbms_output.put_line( x.job || ', ' || x.what || x.next_date );
  8          end loop;
  9          dbms_output.put_line( 'Done with another transaction....' );
 10          commit;
 11  end;
 12  /
In another transaction....
2, null;04-SEP-04
Done with another transaction....
 
PL/SQL procedure successfully completed.

<b>and now you see it..  Use next date:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_job.next_date( :n, sysdate+5 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select job, what, next_date from user_jobs where job = :n;
 
       JOB WHAT                           NEXT_DATE
---------- ------------------------------ ---------
         2 null;                          08-SEP-04

<b>and it is changed, but:</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          dbms_output.put_line( 'In another transaction....' );
  5          for x in ( select * from user_jobs where job = :n )
  6          loop
  7                  dbms_output.put_line( x.job || ', ' || x.what || x.next_date );
  8          end loop;
  9          dbms_output.put_line( 'Done with another transaction....' );
 10          commit;
 11  end;
 12  /
In another transaction....
2, null;04-SEP-04
Done with another transaction....
 
PL/SQL procedure successfully completed.

<b>No one else can SEE THAT CHANGE, until you use that one word:</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          dbms_output.put_line( 'In another transaction....' );
  5          for x in ( select * from user_jobs where job = :n )
  6          loop
  7                  dbms_output.put_line( x.job || ', ' || x.what || x.next_date );
  8          end loop;
  9          dbms_output.put_line( 'Done with another transaction....' );
 10          commit;
 11  end;
 12  /
In another transaction....
2, null;08-SEP-04
Done with another transaction....
 
PL/SQL procedure successfully completed.


<b>and now we can all see it...</b>
 

-- this assumes the passing of the plsql table assembled within UpdateClientSubmitDate function ...
-- Merge the 2 processes and NOCOPY pass ....
DECLARE

CURSOR C_ADT IS
SELECT
adt.rowid adt_rid
FROM
account_detail_transactions adt;


i pls_integer;

type tRid is
table of rowid
index by pls_integer;


vRid tRid; -- this has to be in a persistent memory structure
vRows NUMBER := 1000000;
v_clientsubmit_date DATE := sysdate;
v_rec VARCHAR2(4000);
f1 utl_file.file_type;
k_max_line_size BINARY_INTEGER := 32767;

BEGIN
f1 := utl_file.fopen('/util/lola/output','tmp_fivepercent.dat','w',k_max_line_size);


IF C_ADT%ISOPEN
THEN
CLOSE C_ADT;
END IF;

OPEN C_ADT;
FETCH C_ADT BULK COLLECT INTO vRid LIMIT vRows;

FOR i in 1..vRid.count LOOP
SELECT
adt.transaction_date ||
adt.transaction_amount ||
adt.currency_code ||
adt.external_transaction_id ||
nvl(adt.start_date,adt.TRANSACTION_DATE) ||
nvl(adt.end_date,adt.TRANSACTION_DATE) ||
adt.rate_code ||
pcd.code ||
adt.quantity ||
adt.transaction_amount_local_total ||
pc.code INTO v_rec
FROM
program_codes pc,
portfolio_codes pcd,
account_detail_transactions adt
WHERE
adt.transaction_orig_pc4 = pc.ID (+)
AND adt.ineligible_reason = pcd.ID (+)
AND adt.rowid =vRid(i);


utl_file.put_line(f1,v_rec);
utl_file.fflush(f1);
IF (mod(i,5000) = 0) THEN -- known oracle buffering issue
utl_file.fclose( f1 ); -- close file and reopen in Append mode
f1 := utl_file.fopen('/util/lola/output','tmp_fivepercent.dat','a',k_max_line_size);
END IF;
ENd LOOP;

EXCEPTION
WHEN OTHERS THEN
raise;
IF C_ADT%ISOPEN
THEN
CLOSE C_ADT;
END IF;
utl_file.fclose( f1 );

END;


I tried all the suggestions as you can see (opening and closing every 5000 rows written to file). But it always seems to die at about 889,000 rows.

  1* select 2*1024*1024*1024/889000 from dual
ops$tkyte@ORA9IR2> /
 
2*1024*1024*1024/889000
-----------------------
             2415.61715



how big are your average rows there?  perhaps you are exceeding an OS limit at this point?

(also, why the "get rowids", run another query???? this should be a single query, period) 

However, I am hitting BUG 2546782 (9iv2). Basically I am trying to write out a binary file that I have uploaded and I can't because UTL_FILE.PUT_RAW writes wrong number of bytes. According to metalink, ~It has been fixed in 10i where the new file open modes like "RB", "WB", "AB" are introduced. If the binary file is opened in these modes then the <CR><LF> characters will not be appended to the file.~

Hi, I have got several thousand attachments stored as blobs in an Oracle 8.1.7 instance. They need to be written to files. Some of the attachments span multiple rows (ie, first chunk of data is a blob in one rowid, second chunk is in a separate row, etc.)

I have got two questions. I realize Tom probably doesn't cover #1 but maybe a reader could help me.

1) A java util like the one Mio posted would be a lifesaver for this case. I could adapt it myself to handle the row spanning. I got Mio's code to compile and execute. But, it produces files that are much shorter than they should be (742 bytes vs. 2595, etc), and unreadable. Does anyone know how to correct this, or how I might even troubleshoot it? I am running it with Java 1.4.1_07 on Windows.
Incidentally, I found another pathway below that also works, but produces garbled files.

2) Would it be worth it to move the data to an Oracle 9 or 10 instance instead uaing exp, and then use one of the packages discussed in this page to write the blobs to files? could that work for the multiple-row attachments, or even just the single-rows?

Thanks much,
Natasha


From:
</code> http://www.oracle-base.com/articles/8i/ExportBlob.php <code>

CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler" AS
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;

public class BlobHandler
{
public static void ExportBlob(String myFile, BLOB myBlob) throws Exception
{
// Bind the object to the database object
// Open streams for the output file and the blob
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);
InputStream inStream = myBlob.getBinaryStream();

// Get the optimum buffer size and use this to create the read/write buffer
int size = myBlob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

// Transfer the data
while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}
// Close everything down
inStream.close();
outStream.close();
}
};
/

ALTER java source "BlobHandler" compile;
show errors java source "BlobHandler"

CREATE OR REPLACE PROCEDURE ExportBlob (p_file IN VARCHAR2,
p_blob IN BLOB)
AS LANGUAGE JAVA
NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';
/

EXEC Dbms_Java.Grant_Permission( -
'USER', -
'SYS:java.io.FilePermission', -
'C:\temp\*', -
'read,write,execute,delete');

EXEC dbms_java.grant_permission('USER','SYS:java.lang.RuntimePermission','writeFileDescriptor', null);
EXEC dbms_java.grant_permission('USER','SYS:java.lang.RuntimePermission','readFileDescriptor', null);
EXEC dbms_java.grant_permission('USER','SYS:java.util.PropertyPermission','C:\test\*', 'read,write');

DECLARE
v_blob BLOB;
cursor c1 is select detail from blobtable where row_id = 1006;
BEGIN
open c1;
fetch c1 into v_blob;
ExportBlob('c:\temp\test.txt',v_blob);
close c1;

END;
/

Thanks so much for the response!!

I couldn't agree more, spanning single blobs across multiple rows is @&#%?!!. That's the way the data is stored by an evil legacy app. We brought in a new, better app and it is my job to extract / import the legacy data.

The ftp method is really cool. I will use it on the non-rowspanned blobs.

Just curious, Tom, as to whether dbms_lob is to be preferred over JDBC getCLOB + putString. I'm using the Oracle 9i JDBC Developer's Guide (which only mentions dbms_lob in passing), though I notice from Expert 1-on-1 that you have examples using dbms_lob. Does it just come down to ease of coding, or is there an inherent difference in the way they function?

Hi Tom,

The requirement is same... to get the data from BLOB and store it into some external file location.  The server is Windows 2000..... The database is 9i.  Please note that I have created the package FTP in Scott schema.  

When I use this I am getting Error :

 declare
   l_conn  UTL_TCP.connection;
 BEGIN
   l_conn := ftp.login('rac192', '21', 'scott', 'tiger');
 end;

SQL> /
declare
*
ERROR at line 1:
ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.UTL_TCP", line 410
ORA-06512: at "SYS.UTL_TCP", line 252
ORA-06512: at "SCOTT.FTP", line 39
ORA-06512: at line 4

--- This is the status of listener :

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 29-NOV-2004 12:33:55

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
Start Date                29-NOV-2004 10:29:04
Uptime                    0 days 2 hr. 4 min. 50 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac192.atl.mdc.apar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac192.atl.mdc.apar.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac192.atl.mdc.apar.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ora92" has 2 instance(s).
  Instance "ora92", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ora92", status READY, has 1 handler(s) for this service...
Service "ora92XDB" has 1 instance(s).
  Instance "ora92", status READY, has 1 handler(s) for this service...
The command completed successfully
-----------------------------------

Could you please tell me where I am going wrong?
 

Tom,

I am trying to access dba_jobs from a stored procedure and I get "ORA-00942: table or view does not exist". But when I do a "select * from dba_jobs" I get the desired results. What could be the problem?

Regards,
Murali

Hello Tom,
But I am able to do a select * from dba_jobs from sqlplus but am not able to compile any procedure or view having dba_jobs as a reference.

Could you please help me out of this problem?

Regards,
Murali

Hello Tom !

we have a table with structure like:

create table mytab( a varchar2(10), pic blob);

and images are stored in this table. My problem is how to get this image displayed in Oracle Form ???

I am using Oracle 9i Rel 2 EE and Developer 6i.

Regards

hi all,

after reading the pages regarding the BLOBS I finally
made a package called "BLOBIT" ;) which works... 
thanks to Tom and the readers:

-- create a directory in the database called "MY_FILES"

CREATE OR REPLACE DIRECTORY MY_FILES AS '/app/oracle/admin/FMS9_C/utl';
GRANT READ, WRITE ON DIRECTORY  MY_FILES TO CFMS9 WITH GRANT OPTION;
GRANT READ, WRITE ON DIRECTORY  MY_FILES TO PUBLIC WITH GRANT OPTION;

-- create a table that holds a BLOB column:

CREATE TABLE DEMO
(
  ID           INTEGER,
  DESCRIPTION  VARCHAR2(100 BYTE)               DEFAULT 'description:',
  THEBLOB      BLOB
)
TABLESPACE FAXOUT2
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       505
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
  LOB (THEBLOB) STORE AS 
      ( TABLESPACE  FAXOUT2 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        STORAGE    (
                    INITIAL          10M
                    NEXT             10M
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL;

ALTER TABLE DEMO ADD (
  PRIMARY KEY (ID)
    USING INDEX 
    TABLESPACE FAXOUT2
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       505
                PCTINCREASE      50
                FREELISTS        1
                FREELIST GROUPS  1
               ));

CREATE OR REPLACE PUBLIC SYNONYM DEMO FOR DEMO;

-- package "BLOBIT"

CREATE OR REPLACE package BLOBIT as

/*
|| Package to manage BLOBS - rjh 2005
|| 1: load_a_file  - loads a binary file into BLOB column of table
|| 2: save_to_file - saves a BLOB from table to a file
|| rjh 2005
*/

procedure load_a_file  ( p_id in number, p_filename in varchar2 );
procedure save_to_file ( vid in integer, p_filename in varchar2 );

end BLOBIT;
/


CREATE OR REPLACE package body BLOBIT AS

procedure LOAD_A_FILE
( p_id in number, p_filename in varchar2 )
  as
      l_clob     blob;
      l_bfile    bfile;
  begin
      insert into demo values ( p_id, default, empty_blob() )
      returning theblob into l_clob;
      l_bfile := bfilename( 'MY_FILES', p_filename );
      dbms_lob.fileopen( l_bfile );
      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
      dbms_lob.fileclose( l_bfile );

  commit;

end LOAD_A_FILE;

procedure SAVE_TO_FILE
( vid in integer, p_filename in varchar2 )
  as

    vblob blob;
    i2 number;
    amt number := 10000;
    len number;
    my_vr raw(10000);
    l_output utl_file.file_type;
    p_dir varchar2(30) default 'MY_FILES';
    p_file varchar2(30) := p_filename;
begin

    l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
    for l_cur in (SELECT theblob mylob FROM demo where id = vid)
    loop

        len :=  DBMS_LOB.GETLENGTH(l_cur.mylob);
        vblob := l_cur.mylob ;
        dbms_output.put_line('Length of the Column :  ' || to_char(len));
        i2 := 1;
        if len < 10000 then
        DBMS_LOB.READ(vblob,len,i2,my_vr);
        utl_file.put(l_output, my_vr );
        else
            DBMS_LOB.READ(vblob,amt,i2,my_vr);
            utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
        end if;

        i2 := i2 + amt;
        while (i2 < len) loop
        dbms_output.put_line('i2 : ' || to_char(i2));
        DBMS_LOB.READ(vblob,amt,i2,my_vr);
        utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
        utl_file.fflush(l_output);
        i2 := i2 + amt ;

    end loop;
    utl_file.fclose(l_output);
    end loop;

   commit;

end SAVE_TO_FILE;
end;
/

grant execute on BLOBIT to public;
create or replace public synonym BLOBIT for BLOBIT;

-- usage: put a binary file in the directory:
-- cp binary_file.pdf /app/oracle/admin/FMS9_C/utl/binary_file.pdf
-- put the pdf inside the BLOB column:

SQL> exec BLOBIT.load_a_file(1,'binary_file.pdf');

-- retrieve the BLOB back to the file, delete the file in the filesystem:
-- rm /app/oracle/admin/FMS9_C/utl/binary_file.pdf

SQL> exec BLOBIT.save_to_file(1,'binary_file.pdf');

-- now, you have the file out of the database...
-- cheers

Robert Hanrahan

http://rjh.keybit.net/index2.html

Hi,

I am using UTL_SMTP package to send mails. I have created the procedure in 8i. As i found from Web-sites that "@$ORACLE_HOME/rdbms/admin/initplsj.sql" is necessary to run in DB before we can use UTL_SMTP package. I ran the sql from the path specified and could use it.

However in a different system with Oracle 9i (9.2.0.4) i am unable to find the file "initplsj.sql" in "@$ORACLE_HOME/rdbms/admin/" folder.


My question is,
1. Does Oracle 9i has the file in same name or some different name?
2. Is the Oracle folder we r trying is in-complete ? i.e. is the file actually should have been there but missing?

Thanx.

In 8i both initjvm.sql and initplsj.sql were required to be run as step 1 and step 2 to enable emails.

You specified in response to a question that initplsj.sql is not required to be run explicitly in 9i.

Could you please advise whether we need to run $ORACLE_HOME/javavm/install/initjvm.sql also or is it done automatically too.

select distinct dbms_lob.substr(INFO) "INFO"
from A

gives the error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1

How to resolve this

yeah I find out this it supports only for 4000 characters . But then what will be the solution if i want to do for more that 4000???

Thanks for your quick answer

Hi Tom,

I followed the procedure as specified above to write a blob to a file using utl_raw.put_raw. What should I save the file as and how should I open it to see if the image is displaying correctly. Now I have saved the file as .jpg and when I open it I am seeing a tiny red arrow mark (indicating invalid image). Pls help.

Thanks.

Yes the length is the same. But if I want to visually see the image how can I do it?

Hi,

I am trying to write blob data into the file using utl_file.put_raw

I am successfull till the filesize is 32767 after that I get Plsql numeric or value error. I have stripped down the procedure to its bare minimum when it gives the error

  1  select file_id,file_ticket_id,file_size from pt5_file where file_ticket_id=2979
  2* order by file_size
SQL> /

   FILE_ID FILE_TICKET_ID  FILE_SIZE
---------- -------------- ----------
       753           2979      24576
       755           2979      32256
       756           2979      35328
       754           2979      43520


SQL> declare
  2      len number;
  3  begin
  4      for l_cur in (SELECT file_data mylob
  5                      FROM pt5_file
  6                     where file_ticket_id=2979
  7                     order by file_size)
  8      loop
  9         len :=  dbms_lob.getlength(l_cur.mylob);
 10         dbms_output.put_line('length= '||len);
 11      end loop;
 12  end;
 13  /
length= 24576
length= 32256
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 4


SQL> 

Can you tell me if I am doing something wrong ? 

not sure I understand.   need help to reproduce.



ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( x clob );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_clob clob;
  3  begin
  4          insert into t values ( empty_clob() ) returning x into l_clob;
  5          dbms_lob.writeAppend( l_clob, 30000, rpad('x',30000,'x') );
  6          dbms_lob.writeAppend( l_clob,  5328, rpad('x',5328,'x') );
  7          commit;
  8
  9          for x in ( select * from t )
 10          loop
 11                  dbms_output.put_line( dbms_lob.getlength(x.x) );
 12          end loop;
 13  end;
 14  /
35328
 
PL/SQL procedure successfully completed.
 
 

Hi Tom,

What is wrong with the below code? Am i missing something?

create or replace directory MY_FILES as 'c:\Test';

Grant all on directory MY_FILES to public with grant option;

CREATE TABLE DEMO
(
ID INTEGER,
DESCRIPTION VARCHAR2(100 BYTE) DEFAULT 'description:',
THEBLOB BLOB
);

create or replace procedure LOAD_A_FILE (p_id in number, p_filename in varchar2 )
as
l_clob blob;
l_bfile bfile;
l_dir CONSTANT VARCHAR2(30) := 'MY_FILES';
l_file CONSTANT VARCHAR2(30) := 'test.txt';
l_ret INTEGER := 0;
begin
insert into demo values ( p_id, 'Sample', empty_blob() )
returning theblob into l_clob;
l_bfile := bfilename(l_dir, l_file);
begin
/* l_ret := DBMS_LOB.FILEEXISTS(l_bfile);
if l_ret = 1 then*/
dbms_lob.fileopen( l_bfile, DBMS_LOB.LOB_READONLY);
dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
commit;
/* ELSE
dbms_output.put_line('File does not exist');
dbms_output.put_line('Error ' || SQLCODE || SQLERRM);
END IF;*/
exception
when others then
dbms_output.put_line(SQLCODE || SQLERRM);
end;
if dbms_lob.fileisopen(l_bfile) = 1 then
dbms_output.put_line('File opened');
dbms_lob.fileclose( l_bfile );
else
dbms_output.put_line('File not opened');
end if;
end LOAD_A_FILE;

When I execute the procedure I am getting the below error,

-22288ORA-22288: file or LOB operation FILEOPEN failed
LFI-00108: Open expect the file [test][txt]
to exist.

No, I have created the directory and the test file.

Thanks tom, I have created the file under database server and now it is working. Many thanks for your reply.

I have succesfully run the dump_csv for regular datatypes in Oracle8i is it possible to slighty modify this to work for the CLOB datatype?

..... the line size of the xml file that I create from the following code:

declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
len INTEGER;
l_buff varchar2(4000);
amt BINARY_INTEGER := 4000;
offst INTEGER := 1;
file_not_found EXCEPTION;
clob_file_handle utl_file.file_type;
pragma exception_init(file_not_found, -302000);
begin

dbms_output.put_line('-- set up the query context...!');
queryCtx := DBMS_XMLQuery.newContext('select * from sams.personnel');

dbms_output.put_line('-- get the result..!');
result := DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it in tables/send as messages..
clob_file_handle := utl_file.fopen('c:\wip','myclob.xml','w', amt);
dbms_output.put_line('....The file is opened');
len := dbms_lob.getlength(result);
dbms_output.put_line('...The length of the clob is '||len);
if len > amt then
while ( len > offst ) loop
dbms_output.put_line('....Inside the code');
dbms_lob.read( result, amt, offst, l_buff);
utl_file.put_line(clob_file_handle, l_buff );
offst := offst + amt;
utl_file.fflush(clob_file_handle);
dbms_output.put_line('...The buffer is flushed');
end loop;
else
dbms_output.put_line('In the else statement');
dbms_lob.read( result, len, offst, l_buff);
utl_file.fflush(clob_file_handle);
end if;

utl_file.fclose(clob_file_handle);
dbms_output.put_line('The file is closed');
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
EXCEPTION
WHEN file_not_found THEN
IF UTL_FILE.IS_OPEN(clob_file_handle) THEN
UTL_FILE.FCLOSE(clob_file_handle);
END IF;
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(clob_file_handle);
RAISE_APPLICATION_ERROR(-20020, 'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(clob_file_handle);
RAISE_APPLICATION_ERROR(-20030, 'Invalid File Mode');
WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(clob_file_handle);
RAISE_APPLICATION_ERROR(-20040, 'Invalid File Operation');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('End of data');
end;
/

in my file I have lines like this:
.....<snipped for brevity>
<ROW num="11">
<BIRTH_DATE>1/1/1950 0:0:0</BIRTH_DATE>
<BLOOD_ABO>U</BLOOD_ABO>
<BLOOD_RH>U</BLOOD_RH>
<CMD_SEQ>860</CMD_SEQ>
<DEPT_SEQ>880</DEPT_SEQ>
<DIV_SEQ>881</DIV_SEQ>
<DUTY_CODE>N11</DUTY_CODE>
<FLIGHT_STATUS_IND>N</FLIGHT_STATUS_IND>
<FMPC_ID>20</FMPC_ID>
<FNAME>KNUCKLE</FNAME>
<GENDER>M</GENDER>
<HIGH_RISK_MAM_IND>N</HIGH_RISK_MAM_IND>
<HISPANIC_LATINO_IND>Y</HISPANIC_LATINO_IND>
<LMP_PROMPT_IND>N</LMP_PROMPT_IND>
<LNAME>DRAGGER</LNAME>
<MED_RE
ADY_STAT>U</MED_READY_STAT>
<PAYGRADE_ID>E04</PAYGRADE_ID>
<PERS_SEQ>882</PERS_SEQ>
.... <snipped for brevity>

as you can see above med_ready_stat element named is cut by a new line.

What causes that? I read your statements that with binary data you will get issues with utl_file. Is that the same for clobs also?

True or False:
I am limited to 4000 byte pieces when reading a clob and writing it to the file system ( via PL/SQL )?

Based on the fact that if I try to write character data to a blob it will "do something" to the data.

dbms_lob.read( if_clob_here, amt, offset, must_have_varchar_here)

I was wondering to see if there was a way to read the maximum 32k for varchar2 instead of l_var varchar2(4000);

I thought about what I said and realized my knowledge was not accurate.
varchar2 can be 32000.

I have the EXPORTBLOB package from above; works fine. The problem is that I am archiving off several million BLOBS (actually PDF files). I noticed that as this ran, the session PGA just keeps growing...and growing... and growing. By the time it had exported 1.3Million, the session PGA was over 570,000,000.

It is called from a PL/SQL procedure that opens a cursor to select from the table (few million rows can be returned), computes the destination file name, and calls this procedure

Here is the relevant loop of the program

for image_rec in image_curs(l_start_date, l_end_date) loop
EXIT WHEN image_curs%notfound;

l_row_count := image_curs%rowcount;
l_length := dbms_lob.getlength(image_rec.stmt_image);

if l_length > 0 then
-- some statements get a useful name
l_filename := null;
if image_rec.stmt_source = 'P' then
l_filename := get_filename1 (
image_rec.outlet_id,
image_rec.queue_date,
image_rec.sequence_id);
else
-- Others get a less useful name, since we know less
-- about it
l_filename := get_filename2 (
image_rec.outlet_id,
image_rec.stmt_process_dt,
image_rec.sequence_id);
end if;

begin
ExportBlob(l_filename,image_rec.stmt_image);
exception
when others then
l_sqlerrm := sqlerrm;
-- If we got the error that the directory was
-- not found, then create the directory and retry
if instr(l_sqlerrm,
'java.io.FileNotFoundException: No such file or directory')> 0
then
make_subdirectory(l_filename);
ExportBlob(l_filename,image_rec.stmt_image);
else
-- Not the "directory not found" error, so
-- propagate the error
raise;
end if;
end;
end if;
end loop;

Here is the exact java and call spec...

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "exportBLOB" AS
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;

public class exportBLOB
{
public static void export_it(String myFile, BLOB myBlob) throws Exception
{
// Bind the object to the database object
// Open streams for the output file and the blob
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);
InputStream inStream = myBlob.getBinaryStream();

// Get the optimum buffer size and use this to create the read/write buffer
int size = myBlob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

// Transfer the data
while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}

// Close everything down
inStream.close();
outStream.close();
}
};
/

CREATE OR REPLACE procedure exportBLOB (
p_file in varchar2,
p_blob in blob) as
language java name 'exportBLOB.export_it(java.lang.String, oracle.sql.BLOB)';
/

Any suggestions? (also is there a better way to trap the Java error?)

Thanks
Dan

If I remove the call to EXPORTBLOB, the memory usage does not grow significantly (and it runs a LOT faster - too bad it doesn't actually DO anything useful then...)

I suspect that each call to Java is grabbing a new chunk of memory with each call, and not releasing it until the garbage collection runs at the end of the process.

I tried using NOCOPY in the call spec, but that would not compile (which is consistent with the documentation for NOCOPY).

BTW:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Thanks for any assistance you can provide...

I have a odd distribution of data... day 1 to day 29 has a total of 20,000 rows, day 30 has 250,000 rows, so the obvious break point of day isn't of much help.

On reading the DBMS_LOB doc's, it said the memory was freed when the block was completed, so I made a small block within the loop...something like this

open cursor
loop
declare
l_blob blob
begin
fetch cursor into l_blob...,
exit when cursor not found
exportblob
end;
end loop;
close cursor;

The idea was that when the block was done (with every loop) the memory for the blob would be freed.

Didn't help - perhaps even slower... (old way processed 10,000 blobs in 2-5 minutes, new way was more like 10 minutes, but this is a busy box).

Thanks for your help

In the past I have successfully saved files using java into blob and even by using bfile.

I am trying to save a sql result set (in pl/sql) separated by commas (as in csv file) into BLOB. I am running into errors when i try to save the result set by using INTO clause inside of select or by using UTL_RAW to convert rseultset into raw format.

Is there a better way of saving the result set into blob?

Sample query

select col_a || ',' || col_b || ',' ||
col_c || ',' || col_d || col_e || ',' ||
col_f
from table_a
where condition;

PS: I am using Oracle 8.1.7 and will migrate the code to 9i in about 2 months.

This is just one type of data. BLOB will also save word doc, images, pdf files etc, and yes instead of saving excel sheets, we are simply saving the comman separated text, which we can display back on the client machine using appropriate mime-type.

Hi TOM,

We have inserted data in one particular column whose datatype is LONG RAW. Now, I want to display the contents of this column.

I want to write a visual basic program,whose results with the particular content will be displayed in Excel format.

I am using Oracle Release 9.0.1.0.1

Kindly let me know, how to go about.

Regards
Shirish

Hi Tom,

Thank you for your message.
It would be of great help , if you could send the Visual Basic code which
a) connects to oracle
b) retrieves the blob
c) writes it to a file
d) runs excel against that file

Regards
Shirish

Tom,

OS: Linux
DB: 10.1.0.3.0

I would like to do the ftp using PL/SQL .

What I know is i can do the FTP using the UTL_TCP and UTL_FILE packages that oracle provides, but in this case requirent is ftp and append "APPEND" to the destination file.


What I want to know is how to do FTP through PL/SQL?

Regards,
Dawar

Hi Tom,

In Oracle 9.2 I have stored word file in "BLOB". My database is in unix. Now I want to retrieve this file in aix, I do not want to display th file. Can you show me how to do this ?

regards & thanks
pjp

Hi Tom,

I will prefter to have pl/sql package to retrive blob data into unix as file.

regards & thanks
parag j patankar

Hi Tom,

As per link provided by you in Oracle 9.2 database I have created ftp packages and body without any error. When I am trying to run following with '21' ( assuming it is port no ) 
l_conn := ftp.login('10.192.129.200', '21', 'docs', 'docs');
 
I am getting following error


220 mumsa128200 FTP server (Version 4.1 Tue Jul 6 21:20:07 CDT 2004) ready.
331 Password required for docs.
530 Login incorrect.
DECLARE
*
ERROR at line 1:
ORA-20000: 530 Login incorrect.
ORA-06512: at "DOCS.FTP", line 124
ORA-06512: at "DOCS.FTP", line 104
ORA-06512: at "DOCS.FTP", line 50
ORA-06512: at line 7

When I am running with 1521 

DECLARE
    l_conn  UTL_TCP.connection;
    l_blob  blob;
BEGIN
    select filesave into l_blob from documents where id = 1;
 
    l_conn := ftp.login('10.192.129.200', '1521', 'docs', 'docs');
 
    ftp.binary(p_conn => l_conn);
 
    ftp.put_remote_binary_data
           (p_conn      => l_conn,
            p_file      => '/tmp/foo.doc',
            p_data      => l_blob );
    ftp.logout(l_conn);
    utl_tcp.close_all_connections;
END;
/

This is just runnning and running ( hanging ). But on server nothing is happening on server in /tmp direcotry.

  1* select * from all_directories where directory_name='MY_DOCS'
15:40:31 SQL> /

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
-----------------------------------------------------------------
SYS                            MY_DOCS
/tmp


Kindly guide me to solve the problem.

regards & thanks
pjp 

Hi Tom,

My day does not pass without reading the queries posted in your site. I am very impressed with the way you answer the questions. It gives great insight into the subject. Today, I am here to ask you a question which has been haunting me for a while. We have a requirement of writing the data collected from tables into a file(SQL query is written to get the data we want and the resultset is read to fetch the values and written to a file). The reason why we are writing to a file is that we will use this csv file to load the data into other databases(SQLserver,oracle(8i-9i) or could be anythting else). We deal with spatial data. Presently we have a java class which makes JDBC calls to execute a procedure in order to get the results. These results are then read row by row and written to a file. We have blob fields in the database. We are using file i/0 provided by java for this purpose. We use buffered streams as the i/o activity would be reduced. Our aim is to get the results very fast. I was thinking would it be good to use UTL_FILE provided by oracle for this purpose? would the response time be fast with this? Please explain incase you came across such a situation. How did you go about doing it?

Thanks,
kamesh

Could you kindly send me those routines that you have. For C routines in what mode do you open the file, the input stream is it character or buffered? There should be some place where you buffer the whole data and write to the file is it not? Otherwise, reading 1 row and writing to file would be very time consuming is it not? If it is possible to send the C or java code it would be great.

Kamesh,

Do a search on unloading data and you will find lots of stuff on this site.

Hi

When we define an unique index on a blob field what is checked for duplicates? The hex, checksum or?

show me a unique index on a blob field first :)

ops$tkyte@ORA9IR2> create table t ( x blob );

Table created.

ops$tkyte@ORA9IR2> create unique index t_idx on t(x);
create unique index t_idx on t(x)
                               *
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

 

SQL> select owner, index_name, index_type, uniqueness from dba_indexes where owner = 'DCM'
  2  and index_type = 'LOB';

OWNER                          INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ ------------------------------ --------------------------- ---------
DCM                            SYS_IL0000049837C00007$$       LOB                         UNIQUE


select dbms_metadata.get_ddl('INDEX', 'SYS_IL0000049837C00007$$', 'DCM') from dual;

  CREATE UNIQUE INDEX "DCM"."SYS_IL0000049837C00007$$" ON "DCM"."PM_OBJECTS" (
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DCM"
  PARALLEL (DEGREE 0 INSTANCES 0)


 

Hi

I tried to use that syntax to create an index but it fails complaining about missing ')'.

I was trying to understand what is lob index for?

it is an internal index WE use to find the chunks of your lob.

<quote src=expert oracle database architecture>

This implies there is a lot going on in the background with LOBs, and in fact there is. A LOB column always results in what I call a multisegment object, meaning the table will use multiple physical segments. If we had created that table in an empty schema, we would discover the following:

ops$tkyte@ORA10G> select segment_name, segment_type
  2  from user_segments;
 
SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
SYS_C0011927                   INDEX
SYS_IL0000071432C00002$$       LOBINDEX
SYS_LOB0000071432C00002$$      LOBSEGMENT
T                              TABLE

An index was created in support of the primary key constraint—that is normal—but what about the other two segments, the lobindex and the lobsegment? Those were created in support of our LOB column. The LOBSEGMENT is where our actual data will be stored (well, it might be stored in the table T also, but we’ll cover that in more detail when we get to the ENABLE STORAGE IN ROW clause). The LOBINDEX is used to navigate our LOB, to find the pieces of it. When we create a LOB column, in general what is stored in the row is a pointer, or LOB locator. This LOB locator is what our application retrieves. When we ask for “bytes 1,000 through 2,000” of the LOB, the LOB locator is used against the LOBINDEX to find where those bytes are stored, and then the LOBSEGMENT is accessed. The LOBINDEX is used to find the pieces of the LOB easily. You can think of a LOB then as a master/detail sort of relation. A LOB is stored in “chunks” or pieces, and any piece is accessible to us. If we were to implement a LOB using just tables, for example, we might do so as follows:

Create table parent 
( id int primary key, 
  other-data… 
);

Create table lob 
( id references parent on delete cascade, 
  chunk_number int, 
  data <datatype>(n), 
  primary key (id,chunk_number) 
);

Conceptually, the LOB is stored very much like that—in creating those two tables, we would have primary key on the BLOB table on the ID,CHUNK_NUMBER (analogous to the LOBINDEX created by Oracle), and we would have a table BLOB storing the chunks of data (analogous to the LOBSEGMENT). The LOB column implements this master/detail structure for us transparently. 

</quote> 

Tom,

How to display filename only from file_name string?
/export/u01/oradata/AAA/tools01.dbf

I need only tools01.dbf?
What will be the sql?

hi !!
I tried ur Java stored procedur "ExportBlob"
it is working well on my side & vvery useful to me.

I want to modify that procedure little bit can u pl help as
I am new to Java

Your procedur -
public static void ExportBlob(String myFile, BLOB myBlob) throws Exception
{
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);

----
--}

now instead of sending full path file name as a input I want to send only file name & use location as "Image"
I created directory alias as

Create Directory Image AS 'C:\MyPhoto'"


can u pl tell me how should I use Directory alias in Java stored procedure?

Tom,

Maybe you should put a message on the main page mentioning no IM lingos or whatever as there are lot of new folks visiting every day (just a thought).

Thanks.
Rahul.

Dear Tom,
I am in the planning stage of converting SQL2000 batch process to Oracle9i.
SQL2000 batch stored proc.does several calcs. and finally prepares a flat file and INSERTS INTO a Table.C1 of varchar(4000). Flat file width never exceeded 4000 but the number of lines in the flat file may be near 4000 lines. There is another stored proc. which simply selects from Table.C1 and output to VB app for formatting and display. This is in workig condition.
My quesitions
(1)Is there any limit on how much a data field can grow vertically..Meaning can I INSERT 1000+lines of flat file into ONE Column?The entire flat file is considered as Data.
Ex:
124848488499999999999999999999999444444444444444444444444
Hellow Tom You are the best
My name is Ram from Harrisburg Pennsylvania USA
----
----
End of file
(2)Is it a good design.
(3) Please sugggest any alternatives.

Thanks

Hi Tom,
I just want to tell you I tested the solution using ftp.pkb as suggested by you. I still do testing with different file formats and sizes but it's ok for now.
Thanks!

Learning a lot from other resources, I have a relatively complete FTP solution that might benefit many.

FTP_INTERFACE v.3.1.0
Req: 9iR2 +

* Local <=> Remote operations
* LOB <=> Remote operations
* Session Timeout Control automatically kills dead server connections
* Server Type identification
* RMDIR/MKDIR support for remote server
* LS/DIR commands to provide directory listing retrieval
* Oracle Applications integration
* Mainframe Compatible
* Binary/Ascii support

You can get it from </code> http://www.myoracleportal.com <code>

It is Open Source and PLSQL based

Hi Tom,

I'm new to working with Blobs and from looking at your site and Oracle Forums I created two procedures below. I can successfully insert a file (pdf, size 2453kb) to the blob but when I try to retrieve it back to a file I receive this error:

ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 140
ORA-06512: at "SYS.UTL_FILE", line 379
ORA-06512: at "SYS.UTL_FILE", line 1062
ORA-06512: at "METADATA.GET_LOB", line 135
ORA-06512: at line 7 .

And the dbms_output looks something like this:

Column Length: 2510880
Read 32767 Bytes
Read 65534 Bytes
:
:
Read 2359224 Bytes
Read 2391991 Bytes
Read 2424758 Bytes

Do you know why I’m getting this error and how can I fix it.

Thanks in advance for your feedback.

Leo

CREATE TABLE DEMO
(
TK INTEGER NULL,
THEBLOB BLOB NULL,
FILE_NAME VARCHAR2(100 BYTE) NULL,
FILE_EXT VARCHAR2(3 BYTE) NULL
)

PROCEDURE FILE_TO_BLOB( p_filename IN VARCHAR2 )
AS
v_blob BLOB;
v_bfile BFILE;
v_file_name VARCHAR2(100);
v_file_ext VARCHAR2(3);
BEGIN
--file name without extention
v_file_name := SUBSTR( p_filename, 1, INSTR( p_filename, '.')-1);
--extention of file
v_file_ext := SUBSTR( p_filename, INSTR( p_filename, '.')+1, 5);

INSERT INTO demo (theblob, file_name, file_ext)
VALUES ( EMPTY_BLOB(), v_file_name, v_file_ext)
RETURNING theBlob INTO v_blob;

v_bfile := BFILENAME( 'METADATA_DATA_DIR', p_filename );
DBMS_LOB.FILEOPEN( v_bfile );
DBMS_LOB.LOADFROMFILE( v_blob, v_bfile, Dbms_Lob.getlength( v_bfile ) );
DBMS_LOB.FILECLOSE( v_bfile );
END;

PROCEDURE BLOB_TO_FILE( p_id IN demo.tk%TYPE )
IS
v_blob BLOB;
v_file_name VARCHAR2(100);
v_file_ext VARCHAR2(3);
v_len NUMBER;
v_my_vr RAW(32767);
v_start_pos NUMBER := 1;
v_byte_len NUMBER := 32767;
v_output UTL_FILE.FILE_TYPE;

BEGIN
-- get the blob locator
SELECT theBlob, file_name, file_ext
INTO v_blob, v_file_name, v_file_ext
FROM demo
WHERE tk = p_id;

v_output := UTL_FILE.FOPEN('DATA_DIR', v_file_name||'_o.'||v_file_ext, 'w', 32767);

-- find the length of the blob column
v_len := DBMS_LOB.GETLENGTH(v_blob);
DBMS_OUTPUT.PUT_LINE('Column Length: ' || TO_CHAR(v_len));

IF v_len < v_byte_len THEN
DBMS_LOB.READ( v_blob, v_len, v_start_pos, v_my_vr);
UTL_FILE.PUT( v_output, UTL_RAW.CAST_TO_VARCHAR2(v_my_vr) );
UTL_FILE.FFLUSH( v_output);
DBMS_OUTPUT.PUT_LINE('Read ' || TO_CHAR(v_len) || 'Bytes');
ELSE
DBMS_LOB.READ( v_blob, v_byte_len, v_start_pos, v_my_vr);
UTL_FILE.PUT( v_output, UTL_RAW.CAST_TO_VARCHAR2(v_my_vr) );
UTL_FILE.FFLUSH( v_output);
DBMS_OUTPUT.PUT_LINE('Read ' || TO_CHAR(v_byte_len) || ' Bytes ');
END IF;

v_start_pos := v_start_pos + v_byte_len;

WHILE ( v_start_pos < v_len )
LOOP
-- loop till entire data is fetched
DBMS_LOB.READ( v_blob, v_byte_len, v_start_pos, v_my_vr);
DBMS_OUTPUT.PUT_LINE('Read ' || TO_CHAR( v_byte_len + v_start_pos-1) || ' Bytes ');
UTL_FILE.PUT( v_output, UTL_RAW.CAST_TO_VARCHAR2(v_my_vr) );
UTL_FILE.FFLUSH( v_output);
v_start_pos := v_start_pos + v_byte_len ;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Done!');
END;

I’m using Toad to execute the code below where METADATA is my schema name and GET_LOB is the package name in which FILE_TO_BLOB and BLOB_TO_FILE are procedures.

DECLARE
P_ID NUMBER;

BEGIN
P_ID := 61;

METADATA.GET_LOB.BLOB_TO_FILE ( P_ID );
COMMIT;
END;

I apologize for my incomplete code. The error was showing line 135 because I had some extra code and extra spaces which I removed from the procedures. The code below is exactly how it appears in my package and procedures.

ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 140
ORA-06512: at "SYS.UTL_FILE", line 379
ORA-06512: at "SYS.UTL_FILE", line 1062
ORA-06512: at "METADATA.GET_LOB", line 70
ORA-06512: at line 7 .

Error at line 7 refers to METADATA.GET_LOB.BLOB_TO_FILE ( P_ID ); which is in the pl/sql block below which I use to call my procedure.

DECLARE
P_ID NUMBER;

BEGIN
P_ID := 61;

METADATA.GET_LOB.BLOB_TO_FILE ( P_ID );
COMMIT;
END;

Error at line 70 refers to UTL_FILE.FFLUSH( v_output); inside the while loop in procedure BLOB_TO_FILE.

CREATE OR REPLACE PACKAGE BODY METADATA.Get_Lob
AS

PROCEDURE FILE_TO_BLOB( p_filename IN VARCHAR2 )
AS
v_blob BLOB;
v_bfile BFILE;
v_file_name VARCHAR2(100);
v_file_ext VARCHAR2(3);
BEGIN
--file name without extention
v_file_name := SUBSTR( p_filename, 1, INSTR( p_filename, '.')-1);
--extention of file
v_file_ext := SUBSTR( p_filename, INSTR( p_filename, '.')+1, 5);

INSERT INTO demo (theblob, file_name, file_ext)
VALUES ( EMPTY_BLOB(), v_file_name, v_file_ext)
RETURNING theBlob INTO v_blob;

v_bfile := BFILENAME( 'METADATA_DATA_DIR', p_filename );
DBMS_LOB.FILEOPEN( v_bfile );
DBMS_LOB.LOADFROMFILE( v_blob, v_bfile, DBMS_LOB.GETLENGTH( v_bfile ) );
DBMS_LOB.FILECLOSE( v_bfile );
END;

PROCEDURE BLOB_TO_FILE( p_id IN demo.tk%TYPE )
IS
v_blob BLOB;
v_file_name VARCHAR2(100);
v_file_ext VARCHAR2(3);
v_len NUMBER;
v_my_vr RAW(32767);
v_start_pos NUMBER := 1;
v_byte_len NUMBER := 32767;
v_output UTL_FILE.FILE_TYPE;

BEGIN
-- get the blob locator
SELECT theBlob, file_name, file_ext
INTO v_blob, v_file_name, v_file_ext
FROM demo
WHERE tk = p_id;

v_output := UTL_FILE.FOPEN('METADATA_DATA_DIR', v_file_name||'_o.'||v_file_ext, 'w', 32767); --v_file_name||'_o.'||v_file_ext

-- find the length of the blob column
v_len := DBMS_LOB.GETLENGTH(v_blob);
DBMS_OUTPUT.PUT_LINE('Column Length: ' || TO_CHAR(v_len));

IF v_len < v_byte_len THEN
DBMS_LOB.READ( v_blob, v_len, v_start_pos, v_my_vr);
UTL_FILE.PUT( v_output, UTL_RAW.CAST_TO_VARCHAR2(v_my_vr) );
UTL_FILE.FFLUSH( v_output);
DBMS_OUTPUT.PUT_LINE('Read ' || TO_CHAR(v_len) || 'Bytes');
ELSE
DBMS_LOB.READ( v_blob, v_byte_len, v_start_pos, v_my_vr);
UTL_FILE.PUT( v_output, UTL_RAW.CAST_TO_VARCHAR2(v_my_vr) );
UTL_FILE.FFLUSH( v_output);
DBMS_OUTPUT.PUT_LINE('Read ' || TO_CHAR(v_byte_len) || ' Bytes ');
END IF;

v_start_pos := v_start_pos + v_byte_len;

WHILE ( v_start_pos < v_len )
LOOP
-- loop till entire data is fetched
DBMS_LOB.READ( v_blob, v_byte_len, v_start_pos, v_my_vr);
DBMS_OUTPUT.PUT_LINE('Read ' || TO_CHAR( v_byte_len + v_start_pos-1) || ' Bytes ');
UTL_FILE.PUT( v_output, UTL_RAW.CAST_TO_VARCHAR2(v_my_vr) );
UTL_FILE.FFLUSH( v_output);
v_start_pos := v_start_pos + v_byte_len ;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Done!');
END;
END; --end get_lob
/

The output below remains the same as before.

Column Length: 2510880
Read 32767 Bytes
Read 65534 Bytes
:
:
Read 2359224 Bytes
Read 2391991 Bytes
Read 2424758 Bytes

Hi Tom,

Above in the thread you suggested

"it is just utl_file.put_raw. you'll have a blob, you'll dbms_lob.read or dbms_lob.substr or whatever part of the blob, and utl_file.put_raw it."

for 9iR2 databases. Please can you suggest some documentation or an example for putting the above into practice.

Thanks as always,
Regards,
Aru,

Hi Tom,

Please refer to your followup to Steve Cover in which you have done the following

------------
http://www.oracle-base.com/Articles/9i/FTPFromPLSQL9i.php
Once you install ftp.pks and ftp.pkb, you can:

tkyte@ORA8I> @z:/home/tkyte/Desktop/test
tkyte@ORA8I> DECLARE
2 l_conn UTL_TCP.connection;
3 l_blob blob;
4 BEGIN
5
6 select longname into l_blob from test_table where rownum = 1;
7
8 l_conn := ftp.login('172.16.95.2', '21', 'tkyte', 'password');
9 ftp.binary(p_conn => l_conn);
10 ftp.put_remote_binary_data
11 (p_conn => l_conn,
12 p_file => '/tmp/foo.dat',
13 p_data => l_blob );
14 ftp.logout(l_conn);
15 utl_tcp.close_all_connections;
16 END;
17 /

PL/SQL procedure successfully completed.


You can actually *ftp* the data to yourself ;)

--------------

I have followed the same example to FTP a binary file to a remote location.

I am able to establish connection ( I opened debug window through toad and saw that connection was taking place also the user was getting authenticated) but ftp.put_remote_binary_data is throwing following errors.

ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested.

Please help.

Hi,

I have to write a blob column content (basically a signature captured file) to text file? Is this possible?

hello ,

database is 10g .
sample table consists of id_no , blob ( pdf files ) .

i'm able to store PDF Files in a blob and also retrieve PDF files for given id_no . my problem is based on the content in the blob i want to retrieve id_no .

i have searched Application guide - for large objects but not much help .

waiting for your response .

thanks .
padmaja.

thanks a lot . it worked .

Dear Tom,
Hoo to store a file to database which contains id and BLOB using packages. I have to write Stored Procedures inside package and package body.
also tell me to retrieve the file using package.
Thanks and Regards,
N.ramkumar

Great Work Tom, Keep it up and thanks a lot

The complete PL/SQL code that Robert Hanrahan has provided was really helpful. I had just one day to get the files from the DB and save it to DVD and searched for hrs before I found this solution. I had never worked on file retreival process before but have PL/SQL experience. I am not a java person so this was the best solution.

Thanks, Prabhu.

Hi,
I was tring to load a blob data into a file and I was able to load the file. but the problem is instead of recieving 60 kb of text file and 68 kb of pdf file i am recieving 62 kb and 70 kb respectively.
I am able to open the text file and view the content init, but for pdf file when i try to open it i am getting the following error:

'An unrecongnized token 'gd' was found'
Is my Editor not capable of dealing with the leading bytes in an unicode file?if so what should be done for that


Below is my procedure .Please help me with this problem

create or replace procedure SAVE_TO_FILE
( p_filename in varchar2 )
as
vblob blob;
i2 number;
amt number := 10000;
len number;
my_vr raw(10000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'DIR';
p_file varchar2(30) := p_filename;
begin
l_output := utl_file.fopen(p_dir, p_file, 'a', 32760);
SELECT content into vblob FROM demo where id = 2002;

len := DBMS_LOB.GETLENGTH(vblob);

dbms_output.put_line('Length of the Column : ' || to_char(len));
i2 := 1;
if len < 10000 then
DBMS_LOB.READ(vblob,len,i2,my_vr);
utl_file.put(l_output, my_vr );
else
DBMS_LOB.READ(vblob,amt,i2,my_vr);
utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
end if;
i2 := i2 + amt;
while (i2 < len) loop
dbms_output.put_line('i2 : ' || to_char(i2));
DBMS_LOB.READ(vblob,amt,i2,my_vr);
utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
utl_file.fflush(l_output);
i2 := i2 + amt ;
end loop;
utl_file.fclose(l_output);
commit;
end SAVE_TO_FILE;

can i ftp file from server to my local system using ftp pkg.

with reference to ur reply instead of using the below statement

utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );

i used this one.

utl_file.put_raw(l_output,(my_vr) );

but still i am getting the file with a larger size than the original file size.

when i try to open the file i get the error as
file is damaged .
can u please help me with this problem

Database is 10.2.0.4

On the Unix database server:

$ echo "this is a test file" > test1.txt
$ ls -ltr
total 2
-rw-r--r--   1 eusb009  dbadev        20 Oct 16 12:45 test1.txt
$ 

Now, I run the following procedure in SQL-Plus on the client:

SQL> DECLARE
  2     l_directory       VARCHAR2(30)            := 'WPS_OUT';
  3     l_filename1       VARCHAR2(30)            := 'test1.txt';
  4     l_filename2       VARCHAR2(30)            := 'test2.txt';
  5  
  6     l_blob            BLOB;
  7     l_max_linesize    NUMBER                  := 32767;
  8     l_buffersize      NUMBER;
  9     l_buffer          RAW(32767);
 10     l_amount          NUMBER;
 11     l_offset          NUMBER := 1;
 12  
 13     l_dest_blob       BLOB;
 14     l_src_lob         BFILE;
 15     l_dst_offset      NUMBER := 1;
 16     l_src_offset      NUMBER := 1;
 17     l_lang_ctx        NUMBER := DBMS_LOB.default_lang_ctx;
 18     l_warning         NUMBER;
 19  
 20     l_fh              utl_file.file_type;
 21  BEGIN
 22  
 23  
 24     l_src_lob := bfilename ( l_directory, l_filename1 );
 25  
 26     INSERT INTO t1
 27            ( file_name, file_blob
 28            )
 29     VALUES ( l_filename1, empty_blob()
 30            )
 31            RETURNING file_blob
 32            INTO l_dest_blob;
 33  
 34     -- OPEN the source LOB to get ready to write it
 35     dbms_lob.OPEN (l_src_lob, dbms_lob.lob_readonly);
 36  
 37     dbms_lob.loadblobfromfile ( dest_lob          => l_dest_blob,
 38                                 src_bfile         => l_src_lob,
 39                                 amount            => dbms_lob.getlength(l_src_lob),
 40                                 dest_offset       => l_dst_offset,
 41                                 src_offset        => l_src_offset
 42                               );
 43  
 44     -- now close the soure lob
 45     dbms_lob.CLOSE (l_src_lob);
 46  
 47     SELECT file_blob
 48       INTO l_blob
 49       FROM t1
 50      WHERE file_name=l_filename1;
 51  
 52     -- OPEN the file handle
 53     l_fh := utl_file.fopen( location => l_directory,
 54                             filename => l_filename2,
 55                             open_mode => 'w',
 56                             max_linesize => l_max_linesize);
 57  
 58     -- get information about the LOB being used
 59     l_buffersize := dbms_lob.getchunksize( l_blob ) ;
 60     -- USE the smallest buffer we can
 61     l_buffersize := CASE WHEN l_buffersize < 32767 THEN l_buffersize ELSE 32767 END;
 62  
 63     -- get the amount variable ready for the loop
 64     l_amount := l_buffersize;
 65     -- keep writing output as long as we are getting some from the file
 66     -- we know that we still have content as long as the amount read is the same as the buffer
 67     WHILE l_amount >= l_buffersize
 68     LOOP
 69  
 70        -- READ into the buffer
 71        dbms_lob.READ( lob_loc    => l_blob,
 72                       amount     => l_amount,
 73                       offset     => l_offset,
 74                       buffer     => l_buffer);
 75  
 76        -- reset the offset based on the amount read in
 77        l_offset := l_offset + l_amount;
 78  
 79  
 80        -- now write the contents to the file
 81        utl_file.put_raw ( FILE      => l_fh,
 82                           buffer    => l_buffer,
 83                           autoflush => TRUE);
 84  
 85        -- FLUSH the contents out to the file
 86        utl_file.fflush( FILE => l_fh );
 87  
 88     END LOOP;
 89  
 90     -- CLOSE the file handle
 91     utl_file.fclose( l_fh );
 92  
 93  END;
 94  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL> 

Now, back on the Unix server:

$ ls -ltr
total 4
-rw-r--r--   1 eusb009  dbadev        20 Oct 16 12:45 test1.txt
-rw-r--r--   1 dbddw    dba           20 Oct 16 12:46 test2.txt
$ 

Looks good right?

Now, use a gzipped file:

$ rm test2.txt    
rm: test2.txt: override protection 644 (yes/no)? yes
$ gzip test1.txt
$ ls -ltr
total 2
-rw-r--r--   1 eusb009  dbadev        48 Oct 16 12:45 test1.txt.gz
$ 

And I execute the same code with a different filename:

SQL> DECLARE
  2     l_directory       VARCHAR2(30)            := 'WPS_OUT';
  3     l_filename1       VARCHAR2(30)            := 'test1.txt.gz';
  4     l_filename2       VARCHAR2(30)            := 'test2.txt.gz';
  5  
  6     l_blob            BLOB;
  7     l_max_linesize    NUMBER                  := 32767;
  8     l_buffersize      NUMBER;
  9     l_buffer          RAW(32767);
 10     l_amount          NUMBER;
 11     l_offset          NUMBER := 1;
 12  
 13     l_dest_blob       BLOB;
 14     l_src_lob         BFILE;
 15     l_dst_offset      NUMBER := 1;
 16     l_src_offset      NUMBER := 1;
 17     l_lang_ctx        NUMBER := DBMS_LOB.default_lang_ctx;
 18     l_warning         NUMBER;
 19  
 20     l_fh              utl_file.file_type;
 21  BEGIN
 22  
 23  
 24     l_src_lob := bfilename ( l_directory, l_filename1 );
 25  
 26     INSERT INTO t1
 27            ( file_name, file_blob
 28            )
 29     VALUES ( l_filename1, empty_blob()
 30            )
 31            RETURNING file_blob
 32            INTO l_dest_blob;
 33  
 34     -- OPEN the source LOB to get ready to write it
 35     dbms_lob.OPEN (l_src_lob, dbms_lob.lob_readonly);
 36  
 37     dbms_lob.loadblobfromfile ( dest_lob          => l_dest_blob,
 38                                 src_bfile         => l_src_lob,
 39                                 amount            => dbms_lob.getlength(l_src_lob),
 40                                 dest_offset       => l_dst_offset,
 41                                 src_offset        => l_src_offset
 42                               );
 43  
 44     -- now close the soure lob
 45     dbms_lob.CLOSE (l_src_lob);
 46  
 47     SELECT file_blob
 48       INTO l_blob
 49       FROM t1
 50      WHERE file_name=l_filename1;
 51  
 52     -- OPEN the file handle
 53     l_fh := utl_file.fopen( location => l_directory,
 54                             filename => l_filename2,
 55                             open_mode => 'w',
 56                             max_linesize => l_max_linesize);
 57  
 58     -- get information about the LOB being used
 59     l_buffersize := dbms_lob.getchunksize( l_blob ) ;
 60     -- USE the smallest buffer we can
 61     l_buffersize := CASE WHEN l_buffersize < 32767 THEN l_buffersize ELSE 32767 END;
 62  
 63     -- get the amount variable ready for the loop
 64     l_amount := l_buffersize;
 65     -- keep writing output as long as we are getting some from the file
 66     -- we know that we still have content as long as the amount read is the same as the buffer
 67     WHILE l_amount >= l_buffersize
 68     LOOP
 69  
 70        -- READ into the buffer
 71        dbms_lob.READ( lob_loc    => l_blob,
 72                       amount     => l_amount,
 73                       offset     => l_offset,
 74                       buffer     => l_buffer);
 75  
 76        -- reset the offset based on the amount read in
 77        l_offset := l_offset + l_amount;
 78  
 79  
 80        -- now write the contents to the file
 81        utl_file.put_raw ( FILE      => l_fh,
 82                           buffer    => l_buffer,
 83                           autoflush => TRUE);
 84  
 85        -- FLUSH the contents out to the file
 86        utl_file.fflush( FILE => l_fh );
 87  
 88     END LOOP;
 89  
 90     -- CLOSE the file handle
 91     utl_file.fclose( l_fh );
 92  
 93  END;
 94  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> 

And now, on the Unix server:

$ ls -ltr
total 4
-rw-r--r--   1 eusb009  dbadev        48 Oct 16 12:45 test1.txt.gz
-rw-r--r--   1 dbddw    dba           49 Oct 16 12:49 test2.txt.gz
$ gunzip test2.txt.gz

gunzip: test2.txt.gz: unexpected end of file
$ 

Thanks for any help on this. I suspect this may be a "support" thing, where they would likely tell me "we don't support gzipped files in BLOBS".

At line 55, I opened the filehandle 'w'. I should have opened it 'wb'.

Thanks for not answering too soon. :-)

On the unix server:

$ echo "this is a test file" > test1.txt
$ gzip test1.txt
$ ls -ltr
total 2
-rw-r--r--   1 eusb009  dbadev        48 Oct 20 17:01 test1.txt.gz
$

Now, execute the modified code, with the filehandle opened with a p_mode of 'wb':

SQL> DECLARE
  2     l_directory       VARCHAR2(30)            := 'WPS_OUT';
  3     l_filename1       VARCHAR2(30)            := 'test1.txt.gz';
  4     l_filename2       VARCHAR2(30)            := 'test2.txt.gz';
  5  
  6     l_blob            BLOB;
  7     l_max_linesize    NUMBER                  := 32767;
  8     l_buffersize      NUMBER;
  9     l_buffer          RAW(32767);
 10     l_amount          NUMBER;
 11     l_offset          NUMBER := 1;
 12  
 13     l_dest_blob       BLOB;
 14     l_src_lob         BFILE;
 15     l_dst_offset      NUMBER := 1;
 16     l_src_offset      NUMBER := 1;
 17     l_lang_ctx        NUMBER := DBMS_LOB.default_lang_ctx;
 18     l_warning         NUMBER;
 19  
 20     l_fh              utl_file.file_type;
 21  BEGIN
 22  
 23  
 24     l_src_lob := bfilename ( l_directory, l_filename1 );
 25  
 26     INSERT INTO t1
 27            ( file_name, file_blob
 28            )
 29     VALUES ( l_filename1, empty_blob()
 30            )
 31            RETURNING file_blob
 32            INTO l_dest_blob;
 33  
 34     -- OPEN the source LOB to get ready to write it
 35     dbms_lob.OPEN (l_src_lob, dbms_lob.lob_readonly);
 36  
 37     dbms_lob.loadblobfromfile ( dest_lob          => l_dest_blob,
 38                                 src_bfile         => l_src_lob,
 39                                 amount            => dbms_lob.getlength(l_src_lob),
 40                                 dest_offset       => l_dst_offset,
 41                                 src_offset        => l_src_offset
 42                               );
 43  
 44     -- now close the soure lob
 45     dbms_lob.CLOSE (l_src_lob);
 46  
 47     SELECT file_blob
 48       INTO l_blob
 49       FROM t1
 50      WHERE file_name=l_filename1;
 51  
 52     -- OPEN the file handle
 53     l_fh := utl_file.fopen( location => l_directory,
 54                             filename => l_filename2,
 55                             open_mode => 'wb',
 56                             max_linesize => l_max_linesize);
 57  
 58     -- get information about the LOB being used
 59     l_buffersize := dbms_lob.getchunksize( l_blob ) ;
 60     -- USE the smallest buffer we can
 61     l_buffersize := CASE WHEN l_buffersize < 32767 THEN l_buffersize ELSE 32767 END;
 62  
 63     -- get the amount variable ready for the loop
 64     l_amount := l_buffersize;
 65     -- keep writing output as long as we are getting some from the file
 66     -- we know that we still have content as long as the amount read is the same as the buffer
 67     WHILE l_amount >= l_buffersize
 68     LOOP
 69  
 70        -- READ into the buffer
 71        dbms_lob.READ( lob_loc    => l_blob,
 72                       amount     => l_amount,
 73                       offset     => l_offset,
 74                       buffer     => l_buffer);
 75  
 76        -- reset the offset based on the amount read in
 77        l_offset := l_offset + l_amount;
 78  
 79  
 80        -- now write the contents to the file
 81        utl_file.put_raw ( FILE      => l_fh,
 82                           buffer    => l_buffer,
 83                           autoflush => TRUE);
 84  
 85        -- FLUSH the contents out to the file
 86        utl_file.fflush( FILE => l_fh );
 87  
 88     END LOOP;
 89  
 90     -- CLOSE the file handle
 91     utl_file.fclose( l_fh );
 92  
 93  END;
 94  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.59
SQL> 

And back on the Unix server:

$ ls -ltr
total 4
-rw-r--r--   1 eusb009  dbadev        48 Oct 20 17:01 test1.txt.gz
-rw-r--r--   1 dbddw    dba           48 Oct 20 17:04 test2.txt.gz
$ gzip -d test2.txt.gz
$ cat test2.txt
this is a test file
$

Hi Tom,

Can you please post a sample code to read BLOB from a table.

Is it possible to decrypt the BLOB values in case they are encrypted. I am not very sure about the encryption and decryption part but I have heard Pega (PegaSystem Product) does it.

Hi,
This is still along the lines of the questions put up. I'm sorry if it seems I may be repeating something. I want to create a batch file (a pl/sql script) which reads all the blobs in a column and saves each one in a folder of its own on the system (giving each a diff name of course). You said earlier that PLSQL can write using UTL_FILE but this was earys ago. Has there been a simpler method created since then? Can you please direct me to or write a simple script to do what I need? I would really, REALLY appreciate your answer.
Thank you

hello Tom
I want to present oracle multimedia database in our university but I can't run this code !
I write my code in 5 sql files and execute them in oracle sql developer. first 4 files run successfully but 5.sql has errors. please help me
-------------------1.sql
create or replace directory imgdir as 'c:\demo';

---------------2.sql
drop table imgtable;
create table imgtable (id number,
Image ordsys.ordImage);

-- Insert a row with an empty BLOB.
insert into imgtable values(1,ORDSYS.ORDImage.init());

-- Insert a row with an empty BLOB.
insert into imgtable values(2,ORDSYS.ORDImage.init());
insert into imgtable values(3,ORDSYS.ORDImage.init());
commit;

DECLARE
obj ORDSYS.ORDIMAGE;
ctx RAW(64) := NULL;
BEGIN
-- This imports the image file img71.gif from the IMGDIR directory
-- on a local file system (srcType=file) and sets the properties.

select Image into obj from imgtable where id = 1 for update;
obj.setSource('file','IMGDIR','img71.gif');
obj.import(ctx);

update imgtable set image = obj where id = 1;
commit;

-----------------3.sql
-- This imports the image file img50.gif from the IMGDIR directory
-- on a local file system (srcType=file) and sets the properties.

select Image into obj from imgtable where id = 2 for update;
obj.setSource('file','IMGDIR','img50.gif');
obj.import(ctx);

update imgtable set image = obj where id = 2;
commit;

-- This imports the image file OceanFish.gif from the IMGDIR directory
-- on a local file system (srcType=file) and sets the properties.

select Image into obj from imgtable where id = 3 for update;
obj.setSource('file','IMGDIR','OceanFish.gif');
obj.import(ctx);

update imgtable set image = obj where id = 3;
commit;

END;

--------------------4.sql
DECLARE
image ORDSYS.ORDImage;
idnum integer;
properties_match BOOLEAN;

BEGIN
FOR I IN 1..3 LOOP
SELECT id, image into idnum, image from imgtable where id=I;
dbms_output.put_line('image id: '|| idnum);
properties_match := image.checkProperties();
IF properties_match THEN
DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded');
END IF;

dbms_output.put_line('image height: '|| image.getHeight());
dbms_output.put_line('image width: '|| image.getWidth());
dbms_output.put_line('image MIME type: '|| image.getMimeType());
dbms_output.put_line('image file format: '|| image.getFileFormat());
dbms_output.put_line('BLOB Length: '|| TO_CHAR(image.getContentLength()));
dbms_output.put_line('-------------------------------------------');

END loop;
END;

-------------------5.sql
DECLARE
l_file_name VARCHAR2(10) := 'Test1.gif';
l_file UTL_FILE.file_type;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT imgtbl.image.getContent()
INTO l_blob
FROM imgtable imgtbl
WHERE imgtbl.id = 1;

l_blob_len := DBMS_LOB.GETLENGTH(l_blob);

-- Open the destination file.
l_file := UTL_FILE.FOPEN('imgdir',l_file_name,'w', 32767);

-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;

-- Close the file.
UTL_FILE.FCLOSE(l_file);


END;

------------------ after run script I receive this message
Error report:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 29
ORA-06512: at "SYS.UTL_FILE", line 448
ORA-06512: at line 19
29280. 00000 - "invalid directory path"
*Cause: A corresponding directory object does not exist.
*Action: Correct the directory object parameter, or create a corresponding
directory object with the CREATE DIRECTORY command.

Hi,

Not really a question but I came here trying to find as simple a way of getting the contents of a blob as I could. We attach small image files to our finance system and I have a report that picks up any blobs over 1mb (the ones we attach should be under 100k so bigger than 1mb means someones scanned something wrong).

Anyway there are lots of great answers here but I have lazy fingers and they all have too much typing.

We got a 7mb attachment this week but it had no filename and no real way of finding out what it was. This is what I ended up doing:

sqlplus as apps

SQL> select fnd_gfm.construct_download_URL(fnd_web_config.gfm_agent,FILE_ID) FROM
 dual;

The FILE_ID is from my large objects report:

set term off
set linesize 110
set pagesize 50000
col FILE_NAME format A35
col TYPE format A25
col SIZE_MB format 9999.99
break on report
compute sum label "Total LOBS Attached This Week" of SIZE_MB on report
spool blbd.txt
select file_name, file_id,
to_char(upload_date,'dd-mon-rr hh24:mi:ss') UPLOAD_DATE,
file_content_type TYPE,
dbms_lob.getlength(file_data)/1048576 SIZE_MB
from applsys.fnd_lobs
where dbms_lob.getlength(file_data) > 1048576
-- where dbms_lob.getlength(file_data) > 102400
and upload_date > (sysdate - 7)
-- where upload_date > (sysdate - 7)
order by SIZE_MB Desc
/
exit

But you can just query it from applsys.fnd_lobs, anyway the fnd_gfm.construct_download_URL output can be pasted into a web browser that's already connected to the database. This is an example of the output.

http://SERVER.FullyQualifiedDomainName:PORT/pls/INSTANCE/fndgfm/fnd_gfm.get/NUMBER/FILE_ID/FILENAME

So if you know the file_id value from the applsys.fnd_lobs table you can get a URL link to the blob then just connect a HTML session to the database and paste the output in the address bar to get a copy of the blob.

Handy if you only have a couple to examin and don't like typing.

Hope it helps.

PS for curious types the 7mb file appears to be a report that saved itself (whilst still being created) as a blob, no one has any idea why.