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.
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
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
Well I can't connect to the database at the moment
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.
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
The only one to actually do anything (other than asking for input again was your last suggestion (otherwise:) which had this 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.