OraFAQ Forum: Server Administration » Returned fields into variables

Home » RDBMS Server » Server Administration » Returned fields into variables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Returned fields into variables [message #373560]

Mon, 23 April 2001 13:36

S Bala
Messages: 9
Registered: April 2001

Junior Member

Hi all,
Can anyone suggest me on how to assign the fields
(multiple in no.) i retrieved from ORACLE database to UNIX variables using SQLPLUS?
Thanx and regards,
S Bala

Report message to a moderator

Re: Returned multiple DB fields into Unix shell variables [message #373561 is a reply to message #373560]

Mon, 23 April 2001 13:53

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

#!/bin/ksh
# AH Macey Jan 2001
echo "------------------------------"
echo "KSH method 1 using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev <<EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF`

echo "there are ${#my_arr*} elements in the array"
element=0
while [[ $element -lt ${#my_arr*} ]]
do
echo "==>"${my_arr[[$element]]}
let element=$element+1;
done

echo "Echo all in one command now!"
echo ${my_arr*}

echo "------------------------------"
echo "KSH Method two using while read..."
echo "------------------------------"

## also works OK... sqlplus -s > tmp.txt scott/tiger@dev <<EOF
sqlplus -s scott/tiger@dev <<EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF

while read reslt_line
do
echo "==>"$reslt_line
done < tmp.txt

echo "------------------------------"
echo "KSH method 3 using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev <<EOF
@my_sql.sql
exit;
EOF`

echo "there are ${#my_arr*} elements in the array"
element=0
while [[ $element -lt ${#my_arr*} ]]
do
echo "==>"${my_arr[[$element]]}
let element=$element+1;
done

echo "Echo all in one command now!"
echo ${my_arr*}

==========================================================================

#!/bin/csh
# AH Macey Jan 2001
echo "Making the sql script now to be executed..."
echo 'note Escaped $'
cat > multi_ora_select.sql <<EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT name from v\$parameter where rownum < &1 ;
exit;
EOF

echo "--------------------------------"
echo "CSH Method one using an array..."
echo "--------------------------------"

## Max 4095 in Sun OS 5.6!
set my_arr=`sqlplus -s scott/tiger@dev @multi_ora_select.sql 6`
echo "there are $#my_arr elements in the array"
@ count = 1
while ( $count <= $#my_arr )
echo $count "==>" $my_arr[[$count]]
@ count++
end

echo "--------------------------------"
echo "CSH Method two using foreach..."
echo "--------------------------------"

foreach reslt_line ( `sqlplus -s scott/tiger@dev @multi_ora_select.sql 6` )
echo "==>"$reslt_line
end

Report message to a moderator

Fixed posting [message #373562 is a reply to message #373561]

Mon, 23 April 2001 13:56

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

The posting failed first time. Replace each double '~' below with a double '<' to make the scripts work.

#!/bin/ksh
# AH Macey Jan 2001
echo "------------------------------"
echo "KSH method 1 using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev ~~EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF`

echo "there are ${#my_arr*} elements in the array"
element=0
while [[ $element -lt ${#my_arr*} ]]
do
echo "==>"${my_arr[[$element]]}
let element=$element+1;
done

echo "Echo all in one command now!"
echo ${my_arr*}

echo "------------------------------"
echo "KSH Method two using while read..."
echo "------------------------------"

## also works OK... sqlplus -s > tmp.txt scott/tiger@dev ~~EOF
sqlplus -s scott/tiger@dev ~~EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF

while read reslt_line
do
echo "==>"$reslt_line
done < tmp.txt

echo "------------------------------"
echo "KSH method 3 using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev ~~EOF
@my_sql.sql
exit;
EOF`

echo "there are ${#my_arr*} elements in the array"
element=0
while [[ $element -lt ${#my_arr*} ]]
do
echo "==>"${my_arr[[$element]]}
let element=$element+1;
done

echo "Echo all in one command now!"
echo ${my_arr*}

==========================================================================

#!/bin/csh
# AH Macey Jan 2001
echo "Making the sql script now to be executed..."
echo 'note Escaped $'
cat > multi_ora_select.sql ~~EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT name from v\$parameter where rownum < &1 ;
exit;
EOF

echo "--------------------------------"
echo "CSH Method one using an array..."
echo "--------------------------------"

## Max 4095 in Sun OS 5.6!
set my_arr=`sqlplus -s scott/tiger@dev @multi_ora_select.sql 6`
echo "there are $#my_arr elements in the array"
@ count = 1
while ( $count <= $#my_arr )
echo $count "==>" $my_arr[[$count]]
@ count++
end

echo "--------------------------------"
echo "CSH Method two using foreach..."
echo "--------------------------------"

foreach reslt_line ( `sqlplus -s scott/tiger@dev @multi_ora_select.sql 6` )
echo "==>"$reslt_line
end

Report message to a moderator

Re: Returned multiple DB fields into Unix shell variables [message #373576 is a reply to message #373561]

Tue, 24 April 2001 07:30

S Bala
Messages: 9
Registered: April 2001

Junior Member

Hi,
Thanx very much . But i also need to
access the field variables seperately.
The given methods will put the entire row as
a single array element.
How will i access one row at a time and put the
field elements as array elements ?

Thanx ,
Bala S

Report message to a moderator

Re: Returned multiple DB fields into Unix shell variables [message #373578 is a reply to message #373561]

Tue, 24 April 2001 09:07

S Bala
Messages: 9
Registered: April 2001

Junior Member

Hi ,
In my prev mail , i meant to say that ,
my fields have spaces in between and so
how will i assign the entire row to an array
with the fields as the array elements?
Please help
Regards,
S Bala

Report message to a moderator

Re: Returned multiple DB fields into Unix shell variables [message #373581 is a reply to message #373561]

Tue, 24 April 2001 11:35

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

There are probably many way, but here is a example - stick the lines through awk.

# $0 is the whole line
echo "hello to the world" | awk '{ print $0 }'
echo "hello to the world" | awk '{ print $1 }'
echo "hello to the world" | awk '{ print $2 }'
echo "hello to the world" | awk '{ print $3 }'

Report message to a moderator

Previous Topic:	Comparison of SQLPlus vs. MS SQL Server Utility
Next Topic:	Second Record for each employee

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 00:21:55 CDT 2024