How can I insert a blob and timestamp into a table in Oracle using Perl?

user_8507504 0
Viewed 86

I have an Oracle table as shown below. I need to insert a record in this table using perl. The data to be inserted includes varchar, number, blob, and timestamp. I do not insert attr1 as it defaults to zero. Also, is there a way to get the number of rows inserted?

I came up with some code, but it's incomplete. I'm looking for help on how to code it and will appreciate any help especially since I'm a complete newbie to perl.

table
    attr1 INT DEFAULT 0 NOT NULL,
    attr2 VARCHAR(255) NOT NULL,
    attr3 NUMBER NOT NULL,
    attr4 BLOB NOT NULL,
    attr5 TIMESTAMP NOT NULL,
    CONSTRAINT table_pk PRIMARY KEY (attr1, attr2)

use DBD::Oracle qw(:ora_types);

sub blob_and_other_data {
    my $attr2 = shift; # varchar data looks like '38573985-45643756283'
    my $attr3 = shift; # number data looks like '-9394857384' 
    my $attr4 = shift; # blob data
    my $attr5 = shift; # timestamp data looks like '03-Jun-21 4:38:34 pm'
    my $sql_statement = "insert into table (attr2, attr3, attr4, attr5) VALUES (?, ?, ?, ?)";

    my $sth = $dbh->prepare($sql_statement);

    $sth->bind_param(1, $attr2);
    $sth->bind_param(2, $attr3, { ora_type => ? }); # not even sure if this is needed
    $sth->bind_param(3, $attr4, { ora_type => ORA_BLOB });
    $sth->bind_param(4, $attr5, { ora_type => ? }); # not even sure if this is needed

    $sth->execute();
}
dbi perl oracle sql
2 Answers

For timestamp I use this one, which is in line with OTHER DATA TYPES:

my $sql_statement = "insert into table (attr5) VALUES (TO_TIMESTAMP(? ,'DD-Mon-RR HH:MI:SS AM', 'NLS_DATE_LANGUAGE=AMERICAN'))";
my $sth = $dbh->prepare($sql_statement);
$sth->bind_param(1, $attr5);
$sth->execute();

For LOB's I found Binding for Updates and Inserts for CLOBs and BLOBs, but I never used it.

my $in_clob = "<document>\n";
$in_clob .= "  <value>$_</value>\n" for 1 .. 10_000;
$in_clob .= "</document>\n";
my $in_blob ="0101" for 1 .. 10_000;
 
$SQL='insert into test_lob3@tpgtest (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';
$sth=$dbh->prepare($SQL );
$sth->bind_param(1,3);
$sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});
$sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});
$sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});
$sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
$sth->execute();
user_30272660

The following worked for me:

eval{blob_and_other_data($attr2, $attr3, $attr4, $attr5)};

sub blob_and_other_data {
    my $attr2 = shift;
    my $attr3 = shift;
    my $attr4 = shift;
    my $attr5 = shift;
    my $sql_statement = "insert into table (attr2, attr3, attr4, attr5) VALUES (?, ?, ?, ?)";
    
    my $sth = $dbh->prepare($sql_statement);

    $sth->bind_param(1, $attr2);
    $sth->bind_param(2, $attr3);
    $sth->bind_param(3, $attr4, { ora_type => ORA_BLOB } );
    $sth->bind_param(4, $attr5);

    $sth->execute();
}
user_85075040
Related