Visit Our UNIX and Linux User Community


Oracle SQL Query & connect?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Oracle SQL Query & connect?
# 1  
Old 08-03-2009
Oracle SQL Query & connect?

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: https://www.unix.com/shell-programmin...ll-script.html

Before attempting to create my program I'd like to test the concept of this so would it be something like this:
Code:
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  
Old 08-03-2009
Code:
echo "select name from customer" | sqlplus "$login" | while read line 
do
echo $line
done

How ever i proceed as follows :
Code:
echo "select '!' || name || '!'  from test1;" | sqlplus  "user_name/password @DB"  | grep ^! | while read line
do
echo "displayed " $line |sed s/!//g
done


Last edited by panyam; 08-03-2009 at 11:31 AM..
# 3  
Old 08-03-2009
Quote:
Originally Posted by panyam
done[/code]How ever i proceed as follows :
Huh?

Mike
# 4  
Old 08-03-2009
What do You mean ?..
# 5  
Old 08-03-2009
Quote:
Originally Posted by panyam
What do You mean ?..
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 Smilie
# 6  
Old 08-03-2009
Test it once , i am sure you will understand .

otherwise :

Code:
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


Last edited by panyam; 08-03-2009 at 11:40 AM..
# 7  
Old 08-03-2009
Quote:
Originally Posted by panyam
Test it once , i am sure you will understand .
Well I can't connect to the database at the moment Smilie

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

Last edited by Dird; 08-03-2009 at 11:51 AM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #732
Difficulty: Easy
In BASIC, GOTO jumps to a numbered or labelled line in the program.
True or False?

10 More Discussions You Might Find Interesting

1. Programming

Oracle simple SQL query result in: ORA-08103: object no longer exists

Dear community, please help with a query on Oracle. I'm using SQLPlus (but with SQLDeveloper is the same) to accamplish a sinple query like: select count(*) from ARCHIT_D_TB where (TYP_ID=22 OR TYP_ID=23) and SUB_TM like '%SEP%' and CONS=1234This is a very simple query that works perfect until... (5 Replies)
Discussion started by: Lord Spectre
5 Replies

2. UNIX for Dummies Questions & Answers

Removing unnecessary eol ($) character from Oracle sql query output

Hi All, I am fetching oracle query result in shell variable. As columns numbers are more the output wraps in unix terminal .i.e one complete record in db gets store in multiple lines. with each line ends with $ character. I want to remove these unnecessary $ character but to keep required $... (8 Replies)
Discussion started by: Harshal22
8 Replies

3. Shell Programming and Scripting

Korn Script to connect and query oracle database

I've been sent the following script to finish. It's supposed to connect to an oracle database, query it, and send an email if the query result value is one or more. Currently it isn't connecting properly, just giving the following error: ERROR: ORA-01017: invalid username/password; logon denied... (2 Replies)
Discussion started by: jackmorgan2007
2 Replies

4. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

5. Shell Programming and Scripting

perl- oracle sql query

Hi, I am new to perl.How to query oracle database with perl??? Thanks (1 Reply)
Discussion started by: tdev457
1 Replies

6. Shell Programming and Scripting

Read value from user and use it in Oracle SQL query

Guys can anyone just tell me whether i can pass a value(from UNIX SCRIPT) as an ARGUMENT in Oracle Query? e.g. echo "enter value" read value insert into tablename where col=$value /*something like this*/ (1 Reply)
Discussion started by: subodh.thakar
1 Replies

7. UNIX for Dummies Questions & Answers

bash script & sql query

Hi Guys, I would like with this script "DATA=`ora123 su -c 'echo "SET HEADING OFF;\n SET FEED OFF;\n select USER NAME, ACCOUNT_STATUS from dba_users;\n exit" | sqlplus / as sysdba -s /' ` echo $DATA >> $DAT" make a sql query, but the statement "select USER NAME, ACCOUNT_STATUS from... (6 Replies)
Discussion started by: ixibits
6 Replies

8. UNIX for Advanced & Expert Users

Extract Oracle DB Connect and SQL execution log

Hi, I am trying to write a generic script as a part of a framework which will establish Oracle DB connection once and loops in to check for some files which gives the SQL statements to execute. The script is running but I am stuck with capturing errors ( ORA and SP) and outputs. Example: ... (4 Replies)
Discussion started by: mirage0809
4 Replies

9. Shell Programming and Scripting

Different way to connect to database ans execute sql query

Hi Friends, I am using AIX version and sqlplus to connect to database while writing a shell script. I have implemented three ways to connect to database : 1) sqlplus -s <USERNAME>/<PASSWORD> <<! @<SQL FILE TO EXECUTE> exit ! 2) sqlplus -s <USERNAME>/<PASSWORD> <<! -----sql statemenets... (6 Replies)
Discussion started by: gauravgarg
6 Replies

10. Shell Programming and Scripting

& in SQL query

I have a script that looks for all jobs that contain a particular calendar. Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this? I have tried: select job_name from job where run_calendar='1&15dom' select job_name from job... (3 Replies)
Discussion started by: Lindarella
3 Replies

Featured Tech Videos