Taking information from a postgres sql query and putting it into a shell script array
I have a postgres sql statement that is the following:
select age from students;
which gives me the entries:
Code:
Age
---
10
15
13
12
9
14
10
which is about 7 rows of data.
Now what I would like to do with this is use a shell script to create an array age[]. As a results age[1]=10, age[2]=15, age[3]=13, age [4]=12, age[5]=9, age[6]=14, age[7]=10.
Any ideas on how to create a shell script.
I have been able to do the following:
Code:
Unix_Array=$(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select age from students;
END TRANSACTION;
\q
EOFF
)
echo $Unix_Array
Unix_Array gives me all the ages at once like this:
Code:
10
15
13
12
9
14
10
but I cannot for example parse Unix_Array into individual variables such Unix_Array[1]=10, Unix[2]=15 and so on like I described above. Any ideas?
Thanks,
Jason
Last edited by Scrutinizer; 05-05-2014 at 07:04 AM..
Reason: code tags
First, the array values start from 0
and, there are several ways of doing this
Code:
for i in $(seq 0 $((${#age[@]} - 1)))
do
grade_${i}=$(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select grade_level from characteristcs where age=${age[$i]};
END TRANSACTION
\q
EOFF
)
done
The above approach is costly as it required one DB Connection for each value of the array age.
Rather, you can go with the below approach and run all the details into grade array
Code:
for i in $(seq 0 $((${#age[@]} - 1)))
do
echo "select grade_level from characteristcs where age=${age[$i]}"
done > query.sql
grade=($(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER -a -f query.sql))
Hi Experts,
Need your support.
Not able to use sql query alias in shell script.
Could you please help me in finding right way to use alias with sql query in shell script.
Below is the code i am using.
#!/bin/bash
sqlplus -s abc/abc@abc << EOF> bcd.csv
set trimspool on
select zone_id... (4 Replies)
I have a situation where I have a list of airplanes that make a series of flights.
Therefore I am looking up the different flights that each airplane makes based on a postgres sql query:
select flightid from plane where airplane='DELTAx'
As a result I get a series of flight numbers... (0 Replies)
Hi I would like to embed a sql query in my shell script.
Also, before any the sql query is executed, i would like to validate username and password. (1 Reply)
Hi All,
I have a query with output below
select 'create synonym "'||TABLE_NAME||'" for '||Table_owner||'."'||table_name||'"'||chr(59) from user_synonyms;
==================
create synonym "RV_SBC_SIG" for WFCONTROLLER_TE."RV_SBC_SIG";
create synonym "AQM_TASK" for AWQM_TE."AQM_TASK";... (2 Replies)
hey , i am using this code to store value of a sql query and and then use
it in other query but after some time , but it is not working. please help
#!/bin/bash
val_1=$( sqlplus -s rte/rted2@rel76d2 << EOF
setting heading off
select max(stat_id) from cvt_stats;
exit
EOF
)
nohup... (5 Replies)
Hi,
I have one SQL file prepared in UNIX and one script that is executing that.
In SQL i have Update and create queries.
I want to introduce conditions in SQL file (in UNIX) that if either of the create or update query failes whole transaction should be rollback.
I just have 1 create... (2 Replies)
Hi Team,
Can you please help me to resolve this issue.
Am unable to use this $ symbol in sql query in the shell script.
For Example:
# !/bin/sh
export USER_NAME=XXX
export PASSWORD=YYY
export ORACLE_SID=xamdb
echo $ORACLE_SID
echo " Session Details ..."
... (1 Reply)
Hi,
I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database.
For instance:
USER CITY
--------- ----------
A CITY_A
B CITY_B
C ... (2 Replies)
query sql using shell script, is it possible?
my friend told me to do a file.sql and link to my shell script, but can i query sql using shell script?
thanks in advance! (2 Replies)
I cannot figure out how to run a SQL script, or just a sqlplus query, from a shell script (bash or ksh). Basically, I need to su - oracle from root and run a query, then test the exit status. (3 Replies)
