Hello,
I am working on a small script that pulls user metrics from databases on a number of
different servers and pushes them into a 'metrics warehouse' of sorts for use in trend
analysis among other things. I am having trouble with my insert statement and would
like some help from all of you.
I am using this statement to try and insert the data:
*-----------------------------------
my ($userid, $year, $week, $cpu, $io);
$source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);
my $insert_sql = qq{ insert into user_history_stats (database, server, year, week,
username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?) };
my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert
statement: " . $target_dbh->errstr;;
*-------------------------------------
$orasid and $server are loaded from a text file that I loop through to get the list of
databases that I'm pulling metrics from.
My problem is this: When I put the single quotes around $orasid and $server, I get
the following error:
*-----------------------------------
DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement "
insert into user_history_stats (database, server, year, week, username, cpu, io)
values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with
ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at
./collect_user_metrics.pl line 64, <GEN0> line 1.
DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement "
insert into user_history_stats (database, server, year, week, username, cpu, io)
values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with
ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at
./collect_user_metrics.pl line 64, <GEN0> line 1.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
*-------------------------------------
The Bind variables aren't being set properly for some reason. If I take the single
quotes out, I get this:
*-------------------------------------
DBD::Oracle::st execute failed: ORA-00984: column not allowed here
(DBD ERROR: error possibly near <*> indicator at char 121 in ' insert into
user_history_stats (database, server, year, week, username, cpu, io)
values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for
Statement " insert into user_history_stats (database, server, year, week, username,
cpu, io)
values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues:
:p4='187.31', :p5='8', :p1='2004', :p2='16', :p3='AGDBA']
at ./collect_user_metrics.pl line 64, <GEN0> line 1.
DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error
possibly near <*> indicator at char 121 in ' inse
rt into user_history_stats (database, server, year, week, username, cpu, io)
values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for
Statement " insert into user_history_stats (data
base, server, year, week, username, cpu, io)
values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues:
:p4='187.31', :p5='8', :p1='2004', :p2='16', :p3=
'AGDBA'] at ./collect_user_metrics.pl line 64, <GEN0> line 1.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
*--------------------------------------
As you can see here, the database is rightly rejecting the first two values for not
being enclosed in quotes, but the bind variables are all set correctly.
I have not been able to find the right way to load all of this data. Can anyone see
something I'm doing wrong, or perhaps suggest a better way to set up the INSERT
statement?
Thanks in advance,
Bart
Bart Kersteter
Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED]
(651) 361-5796
**************************************************************************************
This e-mail message and all attachments transmitted with it may contain legally
privileged and/or confidential information intended solely for the use of the
addressee(s). If the reader of this message is not the intended recipient, you are
hereby notified that any reading, dissemination, distribution, copying, forwarding or
other use of this message or its attachments is strictly prohibited. If you have
received this message in error, please notify the sender immediately and delete this
message and all copies and backups thereof.
Thank you.
**************************************************************************************
