CODEWITHSUNDEEP
perloracle-databaseoracle10g
Lazloman
Lazloman

Reputation: 1347

Insert blob into oracle DB using perl

Is this possible at all? I've seen references on the 'net indicating that a stored procedure should be used, but I have a script which needs to insert gzipped data into the DB. How can I go about this, if at all? Thx

Upvotes: 0

Views: 2838

Answers (2)

usr-bin-drinking
usr-bin-drinking

Reputation: 312

I couldn't get Miguel's example to work since my installation of Perl doesn't have an ORA_LOB oracle type. Here's an example that works for me.

For the sake of brevity I'm using hard coded values and internal db subroutines so you'll obviously need to integrate the code into your environment.

use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
require "lib.pl";    #contains getDBConnection() and myExit()

our $dbh = getDBConnection();

my $lob_value;
open FILE, "D:/Inet/wwwroot/tmpcharts/data.xls" or myExit("Failed to open input file: $!\n");
binmode FILE;
$lob_value .= $_ while(<FILE>);
close FILE;

my $sth = $dbh->prepare("update x_trl_test_files set doc=? where file_id=6");
$sth->bind_param(1, $lob_value, { ora_type => ORA_BLOB });
$sth->execute;
$dbh->commit;

myExit();

Upvotes: -1

Miguel Prz
Miguel Prz

Reputation: 13792

You need to use DBD::Oracle module,

use DBD::Oracle qw(:ora_types);

and when you bind the params don't forget tho specify the ora_type

$sth = $dbh->prepare("insert ...");
$sth->bind_param($field_num, $lob_value, { ora_type => ORA_LOB });
$sth->execute

The $lob_value is a scalar variable with the contents of your file.

Upvotes: 11

Related Questions