I've had great success using BLOBs in Oracle for
storing binary documents. A couple of tips;
- store the mime-type as well (i.e. application/pdf)
so you can easily retrieve this as a CGI script
- remember the binmode statement for Windows
- set an upper size limit for LongReadLen (i.e. 2MB)
Tom Hilbig
# Standard CGI & DBI setup
$ENV{'ORACLE_HOME'}="/home/oracle/OraHome1/"; #
Unix client
use DBI;
use DBD::Oracle qw(:ora_types);
# The filename must be the first parameter if provided
if ($#ARGV < 0) {
die "Usage: blobload.pl <filename>\n" ;
} else {
$filename = $ARGV[0] ;
}
# Read the binary file and store in in a scalar
variable
local $BLOBDATA ;
open (FILEIN, "$filename") ;
binmode FILEIN ; # a must-have for Windows
while (<FILEIN>) {
$BLOBDATA = $BLOBDATA . $_ ;
}
close (FILEIN) ;
# Sample for PDF
$mimetype = 'application/pdf' ;
# Connect to Oracle
$dbh =
DBI->connect("dbi:Oracle:host=myhost;sid=mysid;port=myport","username",
"password",
{LongReadLen=>2000000, AutoCommit => 0}) || die
"Database Connect Failed: $! \n $DBI::errstr\n" ;
# Prepare and execute the SQL to update the document
content (MIMETYPE and FILENAME are varchar2, CONTENTS
is a BLOB)
$SQL = "INSERT INTO MYSCHEMA.BINDATA
(MIMETYPE, FILENAME, CONTENTS)
VALUES
(\'$mimetype\', \'$filename\', :BLOBDATA ) " ;
$cur = $dbh->prepare($SQL) ;
$cur->bind_param(":BLOBDATA", $BLOBDATA, { ora_type
=> ORA_BLOB });
$cur->execute;
$rc = $dbh->commit;
# Disconnect from Oracle
$cur->finish() ;
$rc = $dbh->disconnect ;
exit ;
--- "Nguyen, David M" <[EMAIL PROTECTED]> wrote:
> Is is possible to store PDF files in oracle database
> to allow user accesses
> the files, open it and read it? If so, please
> advise how. I am using
> oracle8i with DBI running on Solaris8.
>
> Thanks,
> David
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
