From perl program query is not executed.

ramkumar15 · March 3, 2015, 5:11am

I have tried executing one particular query through perl.But I am unable to get the result. When I tried to execute the sysdate query its working when I executed my perl code.

The below query doesn't work.
QUERY 1:

my $sql ="select name from tab where rownum <6";

Received ora error : ORA-12705: Cannot access NLS data files or invalid environment specified

QUERY 2:
The below query works out.

my $sql ="select sysdate from dual";

When I tried to manually execute the 1st query without perl I am getting the output.

 
SQL> select tname from tab;
TNAME
------------------------------
BIN$Di6O8Q1CAc7gVAAwbvSoEg==$0
BIN$Di6O8Q1FAc7gVAAwbvSoEg==$0
BIN$Di6O8Q1IAc7gVAAwbvSoEg==$0
BIN$Di6O8Q1LAc7gVAAwbvSoEg==$0
BIN$Di6vfFnAb2PgVAAwbvSoEg==$0

radoulov · March 3, 2015, 8:27am

The error indicates that the Perl script is executed in a different environment (most probably via cron). Make sure all necessary Oracle related variables are set before invoking the Perl script: ORACLE_HOME, PATH etc.

ORACLE_HOME=<your_oracle_home>
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME PATH
<run you script>

ramkumar15 · March 4, 2015, 1:22am

do I need to invoke all the above oracle variables to invoke inside the perl script.

radoulov · March 4, 2015, 2:47am

Just put them on top of your script. See orafaq "Setting the Oracle environment" for more.

ramkumar15 · March 4, 2015, 3:08am

I have included the below variable in my perl code.

Please confirm

#!/usr/bin/perl
use strict;
use DBI;
ORACLE_HOME = "/oracle/product/10.2.0";
PATH="$ORACLE_HOME/bin";
export ORACLE_HOME PATH;

radoulov · March 4, 2015, 3:15am

No, you need:

my $ORACLE_HOME = "/oracle/product/10.2.0";
#You may also need to set the ORACLE SID
#my $ORACLE_SID="your_sid";

$ENV{ORACLE_HOME}=$ORACLE_HOME;
# Uncomment if you set ORACLE SID above
#$ENV{ORACLE_SID}=$ORACLE_SID;
$ENV{PATH}="$ORACLE_HOME/bin";

ramkumar15 · March 4, 2015, 3:26am

$ENV{ORACLE_HOME} = "/oracle/product/10.2.0";
$ENV{PATH}="$ORACLE_HOME/bin";
export ORACLE_HOME PATH;

I am getting the below eror when executing.

Global symbol "$ORACLE_HOME" requires explicit package name at test.pl line 5.
Bareword "PATH" not allowed while "strict subs" in use at test.pl line 6.
Execution of test.pl aborted due to compilation errors.

radoulov · March 4, 2015, 3:35am

export is a shell builtin, remove the line that starts with "export" and change the declaration like this:

my $ORACLE_HOME = "/oracle/product/10.2.0";
$ENV{ORACLE_HOME} = $ORACLE_HOME;

ramkumar15 · March 4, 2015, 4:29am

I have did as per request but the still the error throws as below.

DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select tname from tab where tname='CQC_TEST_CASES'"] at testDB.pl line 19.
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select tname from tab where tname='CQC_TEST_CASES'"]

Could you please confirm whether any NLS_LANG or LANG settings need to be changed.

---------- Post updated at 04:29 AM ---------- Previous update was at 04:27 AM ----------

#!/usr/bin/perl
use strict;
use DBI;
my $ORACLE_HOME = "/oracle/product/10.2.0";
$ENV{ORACLE_HOME} = $ORACLE_HOME;
my $user= "user";
my $passwd= "passwd";
my $sid = "TMT";
my $host = "SS.com";
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=1588",$user,$passwd) or die "Couldn't able to connect to database $@\n";

my $sql ="select tname from tab where rownum <6";

my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $row = $sth->fetchrow_array)
{
  print $row . "\n";
}
$dbh->disconnect;

radoulov · March 4, 2015, 4:29am

Try addning the ORACLE SID:

my $ORACLE_SID="your_sid";
$ENV{ORACLE_SID}=$ORACLE_SID;

ramkumar15 · March 4, 2015, 4:31am

I Use the same code and its gets connected in the 11g schema.

but when I tried with 10g database its not connected.

radoulov · March 4, 2015, 4:37am

Do you get the error while executing the script interactively - on the command line - or you get it on a scheduled execution (via cron)?

ramkumar15 · March 4, 2015, 7:47am

Its not getting scheduled via crontab.

I tried interactively through on the command line by executing the perl script & it throws error.

Perl program.pl

The sql database is connected when I tried connecting manually.

sqlplus  user/passwd@db

---------- Post updated at 06:28 AM ---------- Previous update was at 05:13 AM ----------

In the below script while printing the date its works.

 
#!/usr/bin/perl
use strict;
use DBI;
my $ORACLE_HOME = "/oracle/product/10.2.0";
$ENV{ORACLE_HOME} = $ORACLE_HOME;
my $ORACLE_SID="bpmardbd";
$ENV{ORACLE_SID}=$ORACLE_SID;
my $user= "userc";
my $passwd= "passwd";
my $sid = "dba";
my $host = "king.info.com";
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=1588",$user,$passwd) or die "Couldn't able to connect to database $@\n";
my $sql ="select sysdate from dual";
#my $sql ="select tname from tab where rownum <6";
#my $sql ="select tname from tab where tname='CQC_TEST_CASES'";
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $row = $sth->fetchrow_array)
{
  print $row . "\n";
}
$dbh->disconnect;

When I tried to execute the below query I am getting the below error.

 
#my $sql ="select tname from tab where rownum <6";

---------- Post updated at 06:58 AM ---------- Previous update was at 06:28 AM ----------

[error]

DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select tname from tab where rownum <6"] at testDB.pl line 21.
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select tname from tab where rownum <6"] at testDB.pl line 22.

---------- Post updated at 07:47 AM ---------- Previous update was at 06:58 AM ----------

i am not sure whether any character set need to be changed. since due to the below query its not connected in the perl program.

#my $sql ="select tname from tab where rownum <6";

It works if i print sysdate as below.

my $sql ="select sysdate from dual";

radoulov · March 4, 2015, 8:08am

I suppose you'll need to change you LD_LIBRARY_PATH when switching between Oracle versions.
I would use a wrapper shell script to invoke the Perl script correctly,something like this:

#!/bin/bash

ORACLE_HOME=$1
PATH=$ORACLE_HOME/perl/bin:$PATH
PERL5LIB=$ORACLE_HOME/perl/lib:$ORACLE_HOME/perl/lib/site_perl
LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_HOME PATH PERL5LIB LD_LIBRARY_PATH
/path/perl_script

I would define the shebang of the Perl script like this: #!/usr/bin/env perl

And I would execute it like this:

./script_name /oracle/product/10.2.0

or:

./script_name /oracle/product/11.2.x

radoulov · March 4, 2015, 8:14am

Double check the SQL statement:

select tname from tab where rownum <6

Try to execute it from some other clinet (like sqlplus) with the user defined in the Perl script.

ramkumar15 · March 4, 2015, 8:25am

please find the below by using sql plus.

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 4 07:24:10 2015
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select tname from tab where rownum <6;
TNAME
------------------------------
BIN$Di6O8Q1CAc7gVAAwbvSoEg==$0
BIN$Di6O8Q1FAc7gVAAwbvSoEg==$0
BIN$Di6O8Q1IAc7gVAAwbvSoEg==$0
BIN$Di6O8Q1LAc7gVAAwbvSoEg==$0
BIN$Di6vfFnAb2PgVAAwbvSoEg==$0

Does any character set need to be changed or NLS settings need to be changed.

radoulov · March 4, 2015, 8:26am

No, as far as I know ...

ramkumar15 · March 4, 2015, 8:35am

anything else need to be tested ?
I could see if this query is invoked from perl its not working.

---------- Post updated at 08:35 AM ---------- Previous update was at 08:28 AM ----------

when this query is called from the perl program its not working.
not sure whether my coode is correct.

#!/usr/bin/perl
use strict;
use DBI;
my $ORACLE_HOME = "/oracle/product/10.2.0";
$ENV{ORACLE_HOME} = $ORACLE_HOME;
my $ORACLE_SID="bpmardbd";
$ENV{ORACLE_SID}=$ORACLE_SID;
my $user= "user";
my $passwd= "passwd";
my $sid = "dbd";
my $host = "info.com";
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=1588",$user,$passwd) or die "Couldn't able to connect to database $@\n";

my $sql ="select tname from tab where tname='BIN$Di6O8Q1CAc7gVAAwbvSoEg==$0';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $row = $sth->fetchrow_array)
{
  print $row . "\n";
}
$dbh->disconnect;

radoulov · March 4, 2015, 8:59am

Could you post the exact output from your terminal from both the successful and the failing execution?

ramkumar15 · March 4, 2015, 9:16am

error when running query through perl :

DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select tname from tab where rownum <6"] at /dhome/dsmum/AutoQC/scripts/testDB.pl line 20.
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select tname from tab where rownum <6"] at /dhome/dsmum/AutoQC/scripts/testDB.pl line 21.

Successs output :

 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select tname from tab where tname='C_TST_CA';
TNAME
------------------------------
C_TST_CA

---------- Post updated at 09:16 AM ---------- Previous update was at 09:14 AM ----------

error :

DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select tname from tab where tname='CQC_TEST_CASES'"] at testDB.pl line 20.
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select tname from tab where tname='CQC_TEST_CASES'"] at testDB.pl line 21.