Visit Our UNIX and Linux User Community


Problems with fetching data from Oracle database


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problems with fetching data from Oracle database
# 1  
Old 03-05-2013
Problems with fetching data from Oracle database

Here's a database query which looks up the NAME column of PRODUCT table
Code:
SELECT NAME FROM PRODUCT ;

And this query retrieves me the following output
Code:
SUGAR
COCOA
HONEY
WHEAT
CABBAGE
CAULI FLOWER
[6 rows selected]

Please note the last record CAULI FLOWER contains TWO blank spaces between the two words.

Now I want to do the same using Unix script and I wrote the following :
Code:
set -A prod_list `sqlplus -s USER/PWD@DB <<!
SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET LINESIZE 4000 ;
SET WRAP OFF ;
SELECT NAME FROM PRODUCT ;
!
`

and then queried the prod_list array to find 7 instead of 6 entries
Code:
for product in ${prod_list[@]} ; do
echo $product
done

The output is like
Code:
SUGAR
COCOA
HONEY
WHEAT
CABBAGE
CAULI 
FLOWER

7 items in the array. Smilie
My guess is the TWO interim spaces within the last record value is splitting the record in two seperate records
at that point of time when I am trying to fetch the records via sqlplus (ie., via Unix).
Can this behavior be overridden ? If yes how ?
Also we tried to use a SQL cursor to fetch the values into unix , but we do not know exactly how to do it. Smilie
Can anybody help us on that too ?

Please note :
Unix version : Korn Shell
OS : Linux
Database : Oracle 11g


Thanks
Kumarjit.

Last edited by radoulov; 03-06-2013 at 10:51 AM..
# 2  
Old 03-05-2013
Could I suggest a variation to your query:-

Code:
SELECT ''''||NAME||'''' FROM PRODUCT ;

There are four single quotes at the beginning and end of the field you are requesting along with double pipe marks to concatenate. This should return you quoted values that I hope will load your array.

I regret I don't have anything I can test this on though, so can you give it a try and let us all know how you get on.



I hope that this helps,

Robin
Liverpool/Blackburn
UK

Last edited by rbatte1; 03-05-2013 at 01:24 PM..
# 3  
Old 03-05-2013
I would suggest instead of using an array, spool the result to a file.
Code:
spool prod_list.txt
SELECT NAME FROM PRODUCT ;
spool off;

Then later use a while loop to read entries from the spooled file.
Code:
while read product
do
  echo "$product"
done < prod_list.txt

# 4  
Old 03-06-2013
Its a mixed experience , as the solution from rbatte1 wasnt fruitful enough but bipinajith 's clicked good time.
And for that I would like to thank you both for chipping in with your feedbacks , irrespective of the yield. Smilie

I am just stuck at some other place that i might seek your help to pass by.

The SPOOL command
Code:
spool prod_list.txt
SELECT NAME FROM PRODUCT ;
spool off;

not only writes all the data outcome into the file but also displays the output in the Unix command line , and I must not show to the user any output from the database.

How to go for it ?????
This User Gave Thanks to kumarjt For This Post:
# 5  
Old 03-06-2013
To suppress the output to stdout, try this:

Make sure you are connecting using silent mode -s, also SET TERM OFF & use a variable:
Code:
SqlOut=`sqlplus -s USER/PWD@DB <<!
SET TERM OFF

---------- Post updated at 10:10 ---------- Previous update was at 09:04 ----------

if above methods does not help, then redirect the output to /dev/null
Code:
sqlplus -s USER/PWD@DB <<! > /dev/null


Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Mask the Oracle Database Data

Hi, We have to mask the data that is coming from production environment to Non-production environment. The database is running in oracle platform. If anybody has generic scripts to achive this task,it would be great if that can be shared with me? Thank you, Jayaprakash. (10 Replies)
Discussion started by: bandaru_0810
10 Replies

2. Shell Programming and Scripting

why do we need UNIX shell script to load data into Oracle database

Hello everyone, I am new to shell scripting/ loading data into a database. I want to load data into Oracle database using SQL loader. Can some one please explain why do we need unix shell script to load the data into the database? Also can someone please explain what has to be in that script?... (5 Replies)
Discussion started by: new_prog
5 Replies

3. Shell Programming and Scripting

how to extract the data from database (oracle) and send the output as an .xls file?

Hi, How to extract the data from Oracle database and sent the output data to mails using mailx command with .xls attachement? Here i know how to connect the database using unix shell script and how to use the mailx command in UNIX script But i don't know how to use the .xls format file (i... (1 Reply)
Discussion started by: psiva_arul
1 Replies

4. Shell Programming and Scripting

Need shell script to extract data from oracle database

shell script (4 Replies)
Discussion started by: frns5
4 Replies

5. UNIX for Dummies Questions & Answers

Howto capture data from rs232port andpull data into oracle database-9i automatically

Hi, i willbe very much grateful to u if u help me out.. if i simply connect pbx machine to printer by serial port RS232 then we find this view: But i want to capture this data into database automatically when the pbx is running.The table in database will contain similar to this view inthe... (1 Reply)
Discussion started by: boss
1 Replies

6. Shell Programming and Scripting

Need Shell Script to upload data from Text file to Oracle database

Hi Does any one have any idea on uploading the data using Unix Shell script from text file to Oracle database. Requirement:- 1. Need to connect to Oracle database from Unix Shell script. 2. Need to pick Text file from some location on Unix Box. 3. Need to upload the data from text file to... (6 Replies)
Discussion started by: chandrashekharj
6 Replies

7. Shell Programming and Scripting

unix script to export data from csv file to oracle database

Hello people, Need favour. The problem I have is that, I need to develop a unix shell script that performs recurring exports of data from a csv file to an oracle database. Basically, the csv file contains just the first name and last name will be dumped to an Unix server. The data from these... (3 Replies)
Discussion started by: vinayagan
3 Replies

Featured Tech Videos