Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Select multiple values from an Oracle database and assign it to two dimensional array
Top Forums Shell Programming and Scripting Select multiple values from an Oracle database and assign it to two dimensional array Post 302412965 by aemunathan on Wednesday 14th of April 2010 06:19:08 AM
Old 04-14-2010
Select multiple values from an Oracle database and assign it to two dimensional array
hi

I have two tables in oracle DB and am using a joining query which will result in the output as follows.

Quote:
RESOURCE_MSISDN CRITERIA RANGE X Y
919191919191 IN 500 13.021543 80.267197
919292929292 IN 1000 10.554761 75.883016
919393939393 IN 1500 10.554761 75.883016
i need to assign it to a two dimensional array and use it for my further calculations.

the way i tried is as follows.

Code:
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
export PATH=$PATH:$ORACLE_HOME/bin
set -A SQL_RESULTS_ARRAY $(
sqlplus -S <<EOF
USER/PWD@SID
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF ECHO OFF SERVEROUT ON TIME OFF TIMING OFF
select l.RESOURCE_MSISDN,l.CRITERIA,l.RANGE,lm.X,lm.Y from LANDMARK_ALARM_CONF l, LANDMARK lm where l.LANDMARK_ID=lm.LANDMARK_ID;
EOF
)

SQL_ARRAYCOUNT=${#SQL_RESULTS_ARRAY[*]}
SQL_ARRAYIDX=0

while (( $SQL_ARRAYIDX < $SQL_ARRAYCOUNT ))
do
 print "SQL_ARRAY[$SQL_ARRAYIDX]=(${SQL_RESULTS_ARRAY[$SQL_ARRAYIDX]})"
 SQL_ARRAYIDX=$(($SQL_ARRAYIDX+1))
done

exit 0

this returns the output as
Quote:
SQL_ARRAY[0]=(9.1919E+11)
SQL_ARRAY[1]=(IN)
SQL_ARRAY[2]=(500)
SQL_ARRAY[3]=(13.021543)
SQL_ARRAY[4]=(80.267197)
SQL_ARRAY[5]=(9.1929E+11)
SQL_ARRAY[6]=(IN)
SQL_ARRAY[7]=(1000)
SQL_ARRAY[8]=(10.554761)
SQL_ARRAY[9]=(75.883016)
SQL_ARRAY[10]=(9.1939E+11)
SQL_ARRAY[11]=(IN)
SQL_ARRAY[12]=(1500)
SQL_ARRAY[13]=(10.554761)
SQL_ARRAY[14]=(75.883016)
i want the output to be like
Quote:
SQL_ARRAY[0,0]=(9.1919E+11)
SQL_ARRAY[0,1]=(IN)
SQL_ARRAY[0,2]=(500)
SQL_ARRAY[0,3]=(13.021543)
SQL_ARRAY[0,4]=(80.267197)
SQL_ARRAY[1,0]=(9.1929E+11)
SQL_ARRAY[1,1]=(IN)
SQL_ARRAY[1,2]=(1000)
SQL_ARRAY[1,3]=(10.554761)
SQL_ARRAY[1,4]=(75.883016)
SQL_ARRAY[2,0]=(9.1939E+11)
SQL_ARRAY[2,1]=(IN)
SQL_ARRAY[2,2]=(1500)
SQL_ARRAY[2,3]=(10.554761)
SQL_ARRAY[2,4]=(75.883016)
so that i can do some calculations based on
this assignment to array....i will come to this forum again if i have a doubt in my calculations which is going to happen based on this success first.
PLS help me out...
Last edited by aemunathan; 04-14-2010 at 07:34 AM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Select entries between two dates by converting Unix timestamp in Oracle Database.

Hi, I need to select the entries between two dates from an Oracle db. The Oracle db has a column with Unix timestamps. I use the following querry, but it doesnt seem to be working as desired. select count(*) from reporter_status where to_char(FIRSTOCCURRENCE, 'mm-dd-yy') between ('08-07-06')... (1 Reply)
Discussion started by: amitsayshii
1 Replies

2. UNIX for Dummies Questions & Answers

to assign cut values to an array

i need to seperate values seperated by delimiters and assign it to an array.. can u plz help me on that. Variables = "asd,rgbh,(,rty,got,),sroe,9034," i need to assign the variables into arrays.. like.. var=asd var=rgbh.. and so on how do i do this. i need to reuse the values stored in... (6 Replies)
Discussion started by: Syms
6 Replies

3. Shell Programming and Scripting

how to assign multiple values in a pl/sql script

Hello friends, This query is with regards to a script (pl/sql) which returns multiple values. Please see below script wherein the query returns a single value and is assigned to a single variable DB_VALID_CDR=`sqlplus -s user/pass<<!EOF | grep -v "^Connected" 2>&1 set termout off echo... (2 Replies)
Discussion started by: vivek_damodaran
2 Replies

4. Shell Programming and Scripting

PHP: Search Multi-Dimensional(nested) array and export values of currenly worked on array.

Hi All, I'm writing a nagios check that will see if our ldap servers are in sync... I got the status data into a nested array, I would like to search key of each array and if "OK" is NOT present, echo other key=>values in the current array to a variable so...eg...let take the single array... (1 Reply)
Discussion started by: zeekblack
1 Replies

5. Shell Programming and Scripting

Read variables names from array and assign the values

Hi, I have requirement to assign values to variables which are created dynamically. Below is the code which i am using to achieve above requirement. #!/bin/ksh oIFS="$IFS"; IFS=',' STR_FAIL_PARENT_IF_FAILS="WF_F_P_IF_FAILS1,WF_F_P_IF_FAILS2,WF_F_P_IF_FAILS3" set -A... (1 Reply)
Discussion started by: tmalik79
1 Replies

6. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

7. 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

8. Shell Programming and Scripting

Assign two dimensional array in bash at once

Hi, I have a 10*10 two dimensional array. How do I assign value to all it's 100 elements at once? I don't want to open two for loops and assign one by one. Thanks, Amit (2 Replies)
Discussion started by: amit14august
2 Replies

9. Shell Programming and Scripting

Assign Two Dimensional Array In Bash At Once

Hi, I have a 10*10 two dimensional array. How do I assign value to all it's 100 elements at once? I don't want to open two for loops and assign one by one. Thanks, Shuri (1 Reply)
Discussion started by: shurimano
1 Replies

10. UNIX for Beginners Questions & Answers

How To Read a File and Assign the line values to an Array?

i have this basic code that i wrote to read a file and place it's values to an array. the source/input file will have multiple strings on it that is separated by a whitespace. sample_list.txt file contents: ACCT1 TABLE1 ACCT2 TABLE2 ACCT3 TABLE3 script file: sample_list.sh ... (3 Replies)
Discussion started by: wtolentino
3 Replies

LEARN ABOUT DEBIAN

dbix::class::storage::dbi::oracle::wherejoins

DBIx::Class::Storage::DBI::Oracle::WhereJoins(3pm)	User Contributed Perl Documentation	DBIx::Class::Storage::DBI::Oracle::WhereJoins(3pm)

NAME

       DBIx::Class::Storage::DBI::Oracle::WhereJoins - Oracle joins in WHERE syntax support (instead of ANSI).

PURPOSE

       This module is used with Oracle < 9.0 due to lack of support for standard ANSI join syntax.

SYNOPSIS

       DBIx::Class should automagically detect Oracle and use this module with no work from you.

DESCRIPTION

       This class implements Oracle's WhereJoin support.  Instead of:

	   SELECT x FROM y JOIN z ON y.id = z.id

       It will write:

	   SELECT x FROM y, z WHERE y.id = z.id

       It should properly support left joins, and right joins.	Full outer joins are not possible due to the fact that Oracle requires the entire
       query be written to union the results of a left and right join, and by the time this module is called to create the where query and table
       definition part of the SQL query, it's already too late.

METHODS

       See DBIx::Class::SQLMaker::OracleJoins for implementation details.

BUGS

       Does not support full outer joins.  Probably lots more.

SEE ALSO

       DBIx::Class::SQLMaker
       DBIx::Class::SQLMaker::OracleJoins
       DBIx::Class::Storage::DBI::Oracle::Generic
       DBIx::Class

AUTHOR

       Justin Wheeler "<jwheeler@datademons.com>"

CONTRIBUTORS

       David Jack Olrik "<djo@cpan.org>"

LICENSE

       This module is licensed under the same terms as Perl itself.

perl v5.14.2							    2011-05-10			DBIx::Class::Storage::DBI::Oracle::WhereJoins(3pm)
All times are GMT -4. The time now is 10:45 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy