如何使用Perl在Oracle中的表中插入blob和时间戳

Question

我有一个Oracle表，如下所示。我需要使用perl在这个表中插入一条记录。要插入的数据包括varchar、数字、blob和时间戳我不插入attr1，因为它默认为零。还有，有没有办法获得插入的行数

我想出了一些代码，但它不完整。我正在寻找关于如何编码它的帮助，并将感谢任何帮助，特别是因为我是一个完全的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();
}

Answer 1

对于时间戳，我使用这个，它与其他数据类型一致：

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();

对于LOB，我找到了CLOB和BLOB的更新和插入绑定，但我从未使用过它

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();

Answer 2

以下对我有效：

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();
}