String variable to numeric conversion in perl

Shell Programming and Scripting
1

Hi guys

I am having this strange issue.Well my requirement is like below
Compare two values between flat file and oracle DB

Via perl script I am easily getting the rowcount
Now I connect sql plus via perl and the column value that returns is string

 
my $sqlplus_settings = ''; 
my $result = qx { sqlplus $connect_string <<EOF 
$sqlplus_settings select (rcrd_cnt) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where data_acq_cyc_cntl_id = (select max(data_acq_cyc_cntl_id) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where srce_id=1 and srce_fl_id = 109 ); 
exit; 
EOF };

But the value returning in $result is string type and can't compare with the flat file row count

 
its shwoing below error 
"\nSQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 2..." isn't numeric in numeric eq (==) at ./perl5.pl line 52, <FILE> line 13

I also tried

 
$result = int(chomp($result));

but still no luck

Kindly share your suggestions

2

Well. Everything is right. You are using the string "\nSQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 2..." in numeric comparison and get this message (with use warnings). And it's not an error, it's a warning.

Why you get it is another question. Debug and tune your request.

3

Not sure if I understood the issue correctly, but I wonder why you compare the DB reply with the row count instead of the value stored in that row.

4

Try -s option of sqlplus to suppress any additional information. sqlplus -s

5

thanks for your reply actually the comparison does not work and I tried to change the string value to numeric value ..can you please let me know how to do the same

Like if the flat file returns 9 and $result also has value 9

By doing comparison with == is not giving exact result

6

It works (you don't even have to "chomp" the value):

perl -we 'print "9\n" == 9, "\n"'
1
7

Hi hfreyer ,

i want to compare the value stored in rcrd_cnt with wc -l of flat file.
at the time of comparing its not giving the exact result throwing string is used warning

---------- Post updated at 08:37 AM ---------- Previous update was at 08:02 AM ----------

To be more specific please find my code and the o/p from unix

 
my $sqlplus_settings = ''; 
my $result = qx { sqlplus $connect_string <<EOF 
select (rcrd_cnt) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where data_acq_cyc_cntl_id = (select max(data_acq_cyc_cntl_id) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where srce_id=1 and srce_fl_id = 109 ); 
exit; 
EOF };
print "$result \n";
if ( $result == 9)
{
print "\nValues are same\n";
}
else 
{
print "\n values are different \n";
}

Output

 
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 24 08:35:58 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
    RCRD_CNT
------------
           9
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Argument "\nSQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 2..." isn't numeric in numeric eq (==) at ./perl4.pl line 12.
 
 values are different
8

The comparison test fails because the value of $result has a lot more irrelevant characters besides the column value. For example, you do not need the blurb, the heading, the feedback message etc.

Change the following:

 
... 
my $result = qx { sqlplus $connect_string <<EOF 
select (rcrd_cnt) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where data_acq_cyc_cntl_id = (select max(data_acq_cyc_cntl_id) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where srce_id=1 and srce_fl_id = 109 ); 
...

to this:

...
my $result = qx { sqlplus -s $connect_string <<EOF 
set pages 0 feed off
select (rcrd_cnt) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where data_acq_cyc_cntl_id = (select max(data_acq_cyc_cntl_id) from rptg.r_sb_stg_data_fl_acq_cyc_cntl where srce_id=1 and srce_fl_id = 109 ); 
...

tyler_durden