Oracle SQL Query & connect?

Shell Programming and Scripting
1

Hi I'm looking to query a table on a database and then iterate over the results in a loop. I believe this is the last part of my script that I need (after finding out threads for passing variables to other scripts and calling functions in other scripts). I've searched the forums but the best example I've found (easiest to understand the concept of) is this: Can we write SQL cursors from shell script

Before attempting to create my program I'd like to test the concept of this so would it be something like this:

select name from customer | while read line
do
echo $line
done

Is there any other information I would need to provide? Do I need to connect to SQL+/Developer from within the script? What would this look like and how would it link to the query above? Thanks for any help.

Mike

2 
echo "select name from customer" | sqlplus "$login" | while read line 
do
echo $line
done

How ever i proceed as follows :

echo "select '!' || name || '!'  from test1;" | sqlplus  "user_name/password @DB"  | grep ^! | while read line
do
echo "displayed " $line |sed s/!//g
done
3

Huh?

Mike

4

What do You mean ?..

5

I don't understand what you mean. Do you mean that is how you would do it? If so why do you have exclamation marks surrounding the column name? I also don't understand grep or the stuff after $line :frowning:

6

Test it once , i am sure you will understand .

otherwise :

echo "set head off
set echo off
set feedback off
select  name   from test1;" | sqlplus  -s "username/password"  | sed '/^$/d' | while read line
do
echo "displayed " $line |sed s/!//g
done
7

Well I can't connect to the database at the moment :slight_smile:

Edit: It's working now, thanks. I had the wrong login/pwd details :f Copied them from a local copy on here but when looking at the live .ksh I noticed they were different.

Mike

8 
ExecSql() {
sqlplus -S apps/apps <<EOD_SQL
SET ECHO         OFF
SET FEEDBACK     OFF
SET VERIFY       OFF
SET HEADING      OFF
SET PAGESIZE     0
SET LINESIZE     9999
$* ;
EXIT;
EOD_SQL
}

ExecSql 'Select * from GLCA_versions' | 
while read line
do
   echo "displayed:  $line"
done

Jean-Pierre.

9

The only one to actually do anything (other than asking for input again was your last suggestion (otherwise:) which had this :slight_smile: http://i29.tinypic.com/2ug32iq.png

I don't actually have to output anything though it was just for testing purposes (seeing that I could connect to the database) so the formatting isn't important.

Mike