Read multiple values from sqlplus in perl

Shell Programming and Scripting
1

Hello

Need your kind support to solve the below issue

I am returning oracle column value via sqlplus in the below code

 
my $sqlplus_settings = ''; 
my $newval = qx { sqlplus -s $connect_string <<EOF 
set pages 0 feed off
$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 = $input ); 
exit; 
EOF };

i want to return another column value in the same query .
is it possible or do I have to re iterate the 4 lines again

Like I want to execute the below query

 
select to_date(to_char(data_acq_cyc_ts,'dd.mm.yyyy'),'dd.mm.yyyy'),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)

How can I store the additional value to_date(to_char(data_acq_cyc_ts,'dd.mm.yyyy'),'dd.mm.yyyy') in the variable

Thanks for taking out your time to solve it

2

you should really use the Perl DBI interface. dbi.perl.org

3

Thanks Frank

Is there any way to concat the two column values and later split it and store it in different variables.

Is it a possibility?

4 
...
my $result = qx {sqlplus -s $connect_string <<EOF
set pages 0 feed off time off timing off
select to_char(data_acq_cyc_ts,'dd.mm.yyyy')||','||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)
EOF};
chomp($result);
($date, $count) = split(/,/,$result);
# Now, $result is a comma-delimited string consisting of date and count
# $date is the first token of $result
# $count is the second token of $result
print "Value of \$result = $result\n";
print "Value of \$date   = $date\n";
print "Value of \$count  = $count\n";
...

tyler_durden

5

Thanks a ton Tyler again !!!YOu rock