PERL SCRIPT ERROR (DBD ERROR: error possibly near <*> indicator atrror possibly near <*> indicator at char 53 in 'insert

Question

i am getting below error while running below perl script. Can anybody help to figure it out?

    $ENV{PATH}= '/appl/OMS/scripts:/etc:/usr/bin:/usr/sbin:/b...';
    $ENV{PATH}= '/appl/OMS/scripts:/etc:/usr/bin:/usr/sbin:/bin:/usr/local/bin:/usr/local/opt/oracle/client/11.2.0.4/bin';
    $ENV{ORACLE_HOME} ='/usr/local/opt/oracle/client/11.2.0.4';
    $ENV{NLSPATH} = '/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat';
    $ENV{CLASSPATH} = 'CLASSPATH=/usr/local/opt/oracle/client/11.2.0.4/jdbc/lib';
    $ENV{JAVA_HOME} = '/appl/OMS/Software/java';
    $ENV{PERL5LIB} = '/appl/OMS/perl/lib';

    use lib "/appl/OMS/perl/lib";

}

use DBI;
use DBD::Oracle;
use Data::Dumper;
use POSIX qw/strftime/;
use Switch;
use Term::ANSIColor;

print " Input the environment name:\n";
chomp($env=<STDIN>);

print " Input attuid:\n";
chomp($attuid=<STDIN>);

print " Input first name:\n";
chomp($fname = <STDIN>);

print " Input last name:\n";
chomp($lname= <STDIN>);
#chomp($lname);

my $dbInst="t1oms4d8.db.att.com";
my $dbUser="OMS1AT01utils";
my $dbPass="pswd4conn";
my $host = "t1oms5c1.sldc.sbc.com";
#$dsn= "dbi:oracle:T2OMS1D4.db.att.com:t2oms1c1.hydc.sbc.com:1521";
#$DBIconnect= DBI->connect($dsn,OMS0BT08utils,Pwd0wner1);

my $dbh=DBI->connect("dbi:Oracle:$dbInst", $dbUser, $dbPass);
my $sth = $dbh->prepare('create table temp_table1 as (select * from users where user_id like '%SR508W%')');
$sth-> execute();

print "test1";

my $sth = $dbh->prepare("update temp_table1 set user_id= ".$attuid.",  first_nm= ".$fname.", last_nm= ".$lname." where user_id like '%SR508W%' " );
$sth-> execute();

print "test2";

my $sth = $dbh->prepare("insert into users (select * from temp_table1)");
$sth-> execute();

my $sth = $dbh->prepare("insert into user_password(user_id,password1) values (".$attuid." ,(select PASSWORD1 FROM user_password where user_id like '%SR508W%'))");
$sth-> execute();

my $sth = $dbh->prepare("insert into user_role(user_id,role_id) values (".$attuid." ,'1')");
$sth-> execute();

my $sth = $dbh->prepare("select * from temp_table1");
$sth-> execute();

my $sth = $dbh->prepare("drop table temp_table1");
$sth->execute();

$sth->finish();

$dbh->commit or die $DBI::errstr;

I am running the script as below:

  $ ./dbtrial.pl
     Input the environment name:
    oms1at01
     Input attuid:
    sm501u
     Input first name:
    swapnil
     Input last name:
    mahindrakar

The error message is the following:

DBD::Oracle::st execute failed: ORA-00904: "MAHINDRAKAR": invalid identifier (DBD ERROR: error possibly near <*> indicator at char 69 in 'update temp_table1 set user_id= sm501u,  first_nm= swapnil, last_nm= <*>mahindrakar where user_id like '%SR508W%' ') [for Statement "update temp_table1 set user_id= sm501u,  first_nm= swapnil, last_nm= mahindrakar where user_id like '%SR508W%' "] at ./dbtrial.pl line 45, <STDIN> line 4.
DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error possibly near <*> indicator at char 53 in 'insert into user_password(user_id,password1) values (<*>sm501u ,(select PASSWORD1 FROM user_password where user_id like '%SR508W%'))') [for Statement "insert into user_password(user_id,password1) values (sm501u ,(select PASSWORD1 FROM user_password where user_id like '%SR508W%'))"] at ./dbtrial.pl line 50, <STDIN> line 4.
DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error possibly near <*> indicator at char 47 in 'insert into user_role(user_id,role_id) values (<*>sm501u ,'1')') [for Statement "insert into user_role(user_id,role_id) values (sm501u ,'1')"] at ./dbtrial.pl line 52, <STDIN> line 4.
commit ineffective with AutoCommit enabled at ./dbtrial.pl line 58, <STDIN> line 4.
test1test2----- websphe cldv0011 /appl/OMS/scripts/trials ---

Answer 1

Apart from missing a #! /usr/bin/perl (or similar) line, the most obvious thing wrong with your script is that you're not properly quoting your variables when you use them in SQL statemnents.

The easiest way to fix that is to use placeholders (?) in your $dbh->prepare() statements. And then supply the actual values in the $sth-execute()

For example:

my $sth = $dbh->prepare("update temp_table1 set user_id=?,
                         first_nm=?, last_nm=?
                         where user_id like '%SR508W%'");
$sth->execute($attuid,$fname,$lname);

This will automagically quote the variables that need quoting, which seems to be all of them for this example as they're all string values.

Also, you only have to define the scope of your variables once. You don't need to (and shouldn't) say my $sth= every time you set it to a new value. Either declare it separately before using it at all (with just my $sth;, or only declare it the very first time you use it.

And add a blank line or two to separate each different section of the script. That will make it MUCH easier to read than the ugly wall of text it is now.

Finally, you might want to consider taking the input from the command line. This will make testing much easier as you can use your shell's command-line history and recall features (i.e. hit up arrow and enter to execute the same command again) rather than having to enter the same four values into the script every time you run it. e.g.

# environment name is first arg
my $env = shift;

# attuid is second arg
my $attuid = shift;

# First name is third arg
my $fname = shift;

# Last name is fourth arg
my $lname = shift;

Run it like this:

$ ./dbtrial.pl oms1at01 sm501u swapnil mahindrakar

(Note: this is really primitive argument handling. Use Getopt::Std or Getopt::Long if you need something better than just taking the first four args from the command line).

Personally, I'd be inclined to rewrite your script to something more like this (with better formatting, removal of variables and modules that aren't used, better usage of DBI features, and improved sql string construction):

#! /usr/bin/perl

use strict;
use warnings;

use lib "/appl/OMS/perl/lib";

use DBI;
use DBD::Oracle;

my ($env,$attuid,$fname,$lname);

# environment name is first arg
$env = shift;

# attuid is second arg
$attuid = shift;

# First name is third arg
$fname = shift;

# Last name is fourth arg
$lname = shift;

my ($dbInst, $dbUser, $dbPass, $host, $dbh, $sth, $sql, $where);

$dbInst="t1oms4d8.db.att.com";
$dbUser="OMS1AT01utils";
$dbPass="pswd4conn";

$dbh=DBI->connect("dbi:Oracle:$dbInst",$dbUser,$dbPass);

$where=' where user_id like \'%SR508W%\'';

$dbh->do('create table temp_table1 as (select * from users ' . $where . ')');

print "test1";
$sql = 'update temp_table1 set user_id=?,  first_nm=?, last_nm=?' . $where;
$sth = $dbh->prepare($sql);
$sth->execute($attuid,$fname,$lname);

print "test2";
$dbh->do('insert into users (select * from temp_table1)');

$sql = 'insert into user_password(user_id,password1) values (?,(select PASSWORD1 FROM user_password' . $where . ' ))';
$sth = $dbh->prepare($sql);
$sth->execute($attuid);

$sql = 'insert into user_role(user_id,role_id) values (?,?)';
$sth = $dbh->prepare($sql);
# does '1' need to be quoted here?  if field is a varchar or similar then yes, otherwise no.  assuming yes as in the original script.
$sth->execute($attuid,'1');

$dbh->do('select * from temp_table1');

$dbh->do('drop table temp_table1');

$sth->finish();
$dbh->commit or die $DBI::errstr;
$dbh->finish();