Get data from Oracle table into Linux array
I have a table “machines” in Oracle database with below content.
select * from machines;
HOSTNAME IPADDRESS
——————– ——————–
machine1 192.68.1.1
machine2 192.68.1.2
I need to run a function for each hostname and ipaddress and repeat this for all the rows. I am taking the content into array and loop over the results and run the function for each row.
Below is the code for the same.
#!/bin/bash
export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=orcl
export PATH=/home/oracle/app/oracle/product/12.1.0/dbhome_1/bin:$PATH
elements=`sqlplus -s / as sysdba <<EOF
set feedback off
set echo off
set trimspool on
set termout off
set serveroutput on size 100000 format wrapped
set lines 500
set pages 0
select * from test;
EOF`
declare -a my_array=($elements) # Declaring the array and loading the rows
my_array_length=${#my_array[@]} # getting the lenght of the array
loop_var=0 # loop variable to retrieve the data
while(($loop_var<$my_array_length))
do
echo “Machine “:${my_array[$loop_var]};
echo “IP ADDRESS “:${my_array[$loop_var+1]};
loop_var=$loop_var+2;
done
[oracle@OracleED ~]$ sh /tmp/t.sh
Machine :machine1
IP ADDRESS :192.68.1.1
Machine :machine2
IP ADDRESS :192.68.1.2
select * from machines;
HOSTNAME IPADDRESS
——————– ——————–
machine1 192.68.1.1
machine2 192.68.1.2
I need to run a function for each hostname and ipaddress and repeat this for all the rows. I am taking the content into array and loop over the results and run the function for each row.
Below is the code for the same.
#!/bin/bash
export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=orcl
export PATH=/home/oracle/app/oracle/product/12.1.0/dbhome_1/bin:$PATH
elements=`sqlplus -s / as sysdba <<EOF
set feedback off
set echo off
set trimspool on
set termout off
set serveroutput on size 100000 format wrapped
set lines 500
set pages 0
select * from test;
EOF`
declare -a my_array=($elements) # Declaring the array and loading the rows
my_array_length=${#my_array[@]} # getting the lenght of the array
loop_var=0 # loop variable to retrieve the data
while(($loop_var<$my_array_length))
do
echo “Machine “:${my_array[$loop_var]};
echo “IP ADDRESS “:${my_array[$loop_var+1]};
loop_var=$loop_var+2;
done
[oracle@OracleED ~]$ sh /tmp/t.sh
Machine :machine1
IP ADDRESS :192.68.1.1
Machine :machine2
IP ADDRESS :192.68.1.2
Viswamitra's blog 