Heverly, Bryan C. wrote:
I am new to this list server and am hoping that I can get some help with
inserting arrays into an existing table. I am using Oracle 8i concurrently
with LabVIEW 6.1. LabVIEW is used to insert and query data in the Oracle
DB. All commands to the DB are PL / SQL statements. The problem that I am
having is that I need to insert an array of data into the database. The SQL
that I am using for single line insertion is:
insert into test_measurements
(parameter_id,date_time,fraction_of_second,measured_value)
values (85,to_date('01/14/2003 05:39:49', 'MM/DD/YYYY
HH24:MI:SS'),0.014,1.298)
I need to insert multiple rows of data (1000-20000 range). Is there a way
to do this with a single SQL statement?
I have tried the following code using TOAD:
INSERT INTO TEST_MEASUREMENTS VALUES (:COL_1a, to_date(:COL_2a,'MM/DD/YYYY HH24:MI:SS'), :COL_3a, :COL_4a)
When I execute this SQL, I am prompted to enter a value for each of the 4
columns of data. After I enter each of the 4 columns, and click OK, the row
is inserted. Is there a way to declare multiple values for each of the 4
columns prior to these lines of code?
Any help would be appreciated. Thank you,
<mailto:[EMAIL PROTECTED]> Bryan C. Heverly
Associate Engineer
<http://www.nns.com> Northrop Grumman Newport News
E37, Data Acquisition Systems / Special Instrumentation
Phone: (757) 380-2026
Fax: (757) 688-3191
package OraConnect;
use strict;
use nwengine::config;
use DBI;
my $debug = 1;
my %DBHandler;
$DBHandler{$$} = 0;
sub init {
my ($proto,%connect_info) = @_;
my $class = ref($proto) || $proto;
my $self = {};
$self->{DBTYPE} = $connect_info{dbtype} || "Oracle";
$self->{ORA_SID} = $connect_info{ora_sid} || UIS_SID;
$self->{USER} = $connect_info{user} || UIS_username;
$self->{PASSWORD} = $connect_info{password} || UIS_password;
my $cinfo = join ":", $self->{DBTYPE},$self->{ORA_SID};
unless ($DBHandler{$$}){
$self->{DBH} = DBI->connect("DBI:$cinfo",
$self->{USER},
$self->{PASSWORD},
{ RaiseError => 0, PrintError=>$debug, LongReadLen =>
4*1024*1024, LongTruncOk => 0, AutoCommit => 0 });
$DBHandler{$$} = $self->{DBH};
}
else{
if ($DBHandler{$$}->ping){
$self->{DBH} = $DBHandler{$$};
}
else{
$self->{DBH} = DBI->connect("DBI:$cinfo",
$self->{USER},
$self->{PASSWORD},
{ RaiseError => 0, PrintError=>$debug, LongReadLen =>
4*1024*1024, LongTruncOk => 0, AutoCommit => 0 });
$DBHandler{$$} = $self->{DBH};
}
}
$self->{ERR} = 0;
$self->{ERRSTR} = "";
if (! $self->{DBH}) {
$self->{ERR} = $DBI::err;
$self->{ERRSTR} = $DBI::errstr;
print STDERR "OraConnect: Cannot connect to database: $self->{ERRSTR}\n" if
$debug;
}
bless ($self, $class);
}
sub do_select {
my ($self,$rsql,$where_bind_val,$result_bind_val,$limit_start,$limit_end) = @_;
# return $self->{ERR} if $self->{ERR};
my $sth = $self->{DBH}->prepare($rsql);
if (!defined $sth) {
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
if (!$sth->execute(@{$where_bind_val})) {
$self->{ERR} = $sth->err;
$self->{ERRSTR} = $sth->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
my @rt = $sth->fetchall_arrayref();
if ($sth->err) {
$self->{ERR} = $sth->err;
$self->{ERRSTR} = $sth->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
# MySQL LIMIT clause emulation
if ((defined $limit_end)){
@{$rt[0]}=@{$rt[0]}[$limit_start..$limit_end];
}
# LIMIT end
return \@rt;
}
sub do_not_select {
my ($self,$rsql,$where_bind_val,$result_bind_val,$bind_types,$not_commit) = @_;
# return $self->{ERR} if $self->{ERR};
my $rv = 0;
if(!defined $bind_types){
$rv = $self->{DBH}->do($rsql,undef,@{$where_bind_val});
if (!defined $rv) {
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
}
else{
my $sth = $self->{DBH}->prepare($rsql);
if (!defined $sth){
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
for (my $i = 1; $i<= $#{$where_bind_val}+1;$i++){
if (@{$bind_types}[$i-1] == 1){
$sth->bind_param($i,@{$where_bind_val}[$i-1],{ora_type => 112});
}
elsif(@{$bind_types}[$i-1]==2){
$sth->bind_param($i,@{$where_bind_val}[$i-1],{ora_type => 113});
}
else{
$sth->bind_param($i,@{$where_bind_val}[$i-1]);
}
}
$rv = $sth->execute;
if (!defined $rv){
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
}
unless($not_commit){
$self->do_commit;
}
return $rv;
}
sub do_multi_not_select {
my ($self,$rsql,$where_bind_val_array,$result_bind_val,$bind_types,$not_commit) = @_;
# return $self->{ERR} if $self->{ERR};
my $sth = $self->{DBH}->prepare($rsql);
my $rv=0;
if (!defined $sth) {
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
for my $i (@{$where_bind_val_array}) {
if (!defined $bind_types){
if (!$sth->execute(@{$i})) {
$self->{ERR} = $sth->err;
$self->{ERRSTR} = $sth->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
$rv += $sth->rows;
}
else{
for (my $k = 1; $k <= $#{$bind_types}+1; $k++){
if (@{$bind_types}[$k-1] == 1){
$sth->bind_param($k,@{$i}[$k-1],{ora_type => 112});
}
elsif(@{$bind_types}[$k-1]==2){
$sth->bind_param($k,@{$i}[$k-1],{ora_type => 113});
}
else{
$sth->bind_param($k,@{$i}[$k-1]);
}
}
if (!$sth->execute) {
$self->{ERR} = $sth->err;
$self->{ERRSTR} = $sth->errstr;
print STDERR $rsql."\n" if $debug;
return undef;
}
$rv += $sth->rows;
}
}
unless ($not_commit){
$self->do_commit;
}
return $rv;
}
sub do_commit{
my ($self) = @_;
if (!$self->{DBH}->commit) {
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
return undef;
}
return 1;
}
sub do_rollback{
my ($self) = @_;
if (!$self->{DBH}->rollback) {
$self->{ERR} = $self->{DBH}->err;
$self->{ERRSTR} = $self->{DBH}->errstr;
return undef;
}
return 1;
}
sub close {
my $self = shift;
$self->{ERR} = -1;
$self->{ERRSTR} = "Connection in closed state";
my $rc = $self->{DBH}->rollback();
}
1;
