Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Read multiple values from sqlplus in perl

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read multiple values from sqlplus in perl
# 1  
Old 08-25-2011
Read multiple values from sqlplus in perl
Hello

Need your kind support to solve the below issue

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

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

Code:
 
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  
Old 08-25-2011
you should really use the Perl DBI interface. dbi.perl.org
Last edited by frank_rizzo; 08-25-2011 at 09:54 AM.. Reason: fix URL
# 3  
Old 08-25-2011
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  
Old 08-25-2011
Code:
...
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  
Old 08-26-2011
Thanks a ton Tyler again !!!YOu rock
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk GSUB read field values from multiple text files

My program run without error. The problem I am having. The program isn't outputting field values with the column headers to file.txt. Each of the column headers in file.txt has no data. MEMSIZE SECOND SASFoundation Filename The output results in file.txt should show: ... (1 Reply)
Discussion started by: dellanicholson
1 Replies

2. UNIX for Dummies Questions & Answers

Read in Multiple log files and output selected variables and values to cvs file

I have several problems with my problems: I hope you can help me. 1) the If else statement I am getting an error message. My syntax must be incorrect because the entire statement is throwing an error. For example in filew.log if these items don't exist Memsize, SASFoundation and also if... (0 Replies)
Discussion started by: dellanicholson
0 Replies

3. Shell Programming and Scripting

Read multiple values into one variable

Hello everybody, I am trying to assign multiple values from text into a single variable in bash. Source TXT: 1 THIS IS THE ENTITY1 NAME1 2 THIS IS THE ENTITY2 NAME2 3 THIS IS THE ENTITY3 NAME3 while read id entity name do printf "$id" "$entity" "$name" done <... (2 Replies)
Discussion started by: mrcrowley
2 Replies

4. Shell Programming and Scripting

Read record from the text file contain multiple separated values & assign those values to variables

I have a file containing multiple values, some of them are pipe separated which are to be read as separate values and some of them are single value all are these need to store in variables. I need to read this file which is an input to my script Config.txt file name, first path, second... (7 Replies)
Discussion started by: ketanraut
7 Replies

5. Shell Programming and Scripting

[SOLVED] UNIX FOR loop to read a variable with multiple values

Hi, I have a variable which stores file names as a result of find command. I need to delete all these files one by one, i.e. by a loop. Can anyone tell me how can it be done? The variable f2d has the file names like these abc.txt bcd.txt fff.txt gef.txt Now I have used a loop as... (12 Replies)
Discussion started by: jhilmil
12 Replies

6. Shell Programming and Scripting

Return multiple values using for loop in perl

I am using a for loop to copy files from say DIR1 and DIR2 to DIR3.I have to check whether files are copied from DIR1 and DIR2 and print the respective message. @path=("$DIR1","$DIR2"); foreach (@path) { $rc=system("cp $_/*xml $DIR3"); if ($rc == 0) { print "Files were copied... (1 Reply)
Discussion started by: liyakathali
1 Replies

7. UNIX for Dummies Questions & Answers

How to store/read multiple values from a varible

Hi, when I enter 'ps -ef| grep process_name'/'psu | grep process_name', i am getting multiple number of lines output( i mean multiple no of processes).how can i store it one by one and echo it in the same way(one by one). part of script is var1=$(remsh hostname -l username ps -ef|grep... (2 Replies)
Discussion started by: jeanzibbin
2 Replies

8. Programming

Perl SNMP set pdu with multiple values

Hi, I'm trying to set two OID's in the same PDU (so both OIDs and values are sent in the same packet) at present I am only able to send one at a time which does not trigger the hardware response as the two specific OID's need to be set simultaneously in order to work. I'm using perl along... (0 Replies)
Discussion started by: MCLASS
0 Replies

9. Shell Programming and Scripting

read and match multiple lines in perl

Could any one tell me how to read and match multiple lines in perl? Did this code below still work in this situation? while (<FILE>) { if (/ /) { } } Thanks a lot! (5 Replies)
Discussion started by: zx1106
5 Replies

10. Shell Programming and Scripting

Perl program to read from multiple files

Hi, I need to generate a test data file by reading inputs from multiple files. A sample pseudo code for this program to read from three files and write to a output file would be like: open(OUTPUTFILE, "data"); open(INFILE1, "data1"); open(INFILE2, "data2"); open(INFILE3, "data3"); ... (1 Reply)
Discussion started by: jyotipg
1 Replies
Login or Register to Ask a Question