Visit Our UNIX and Linux User Community


handling Multiline SQL outputs


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting handling Multiline SQL outputs
# 1  
Old 01-08-2008
handling Multiline SQL outputs

Hi,

I'm using KSH and calling a SQL statement inside my script which returns more than 1 record.

Code:
Result=`sqlplus -s $DB_USER/$DB_PWD@$DB_STRING <<END
set echo off
set head off
spool junk.txt
select Name from abc where job = 'Manager';
spool off
exit;
END`

echo $Result

The above SQL returns 3 names as:

Quote:
Tom Hanks
Harry Potter
John
But when i'm calling this SQL in my script and assigning the Result to a variable "Result" and echoing the same, the result comes out in a single line like below:

Quote:
Tom Hanks Harry Potter John
What I require is the output should be assigned to the variable result in the below format so that I can seggregate the records:

Quote:
Tom Hanks
Harry Potter
John
Is there any way i can achieve such result?

Thanks in advance.
# 2  
Old 01-08-2008
Code:
OIFS="$IFS"
IFS=' '
#your code here
#
#
IFS="$OIFS"

# 3  
Old 01-08-2008
What are IFS and OIFS

Hi,

It doesnt work ... i didnt get the meaning of IFS and OIFS? what are they? are these variables? what is the use of these variables?

Please explain?

Thanks.
# 4  
Old 01-08-2008
Sorry .. it works

Sorry Admin ... my mistake .. it really works ... Smilie

Can u explain a little bit on how u achieved it? Is there any documentation for OIFS and IFS?
# 5  
Old 01-08-2008
Quote:
Originally Posted by man ksh
IFS The Internal Field Separator that is used for word splitting after expansion and to split lines into words with the read builtin command. The default value is
‘‘<space><tab><newline>’’.
OIFS is just a variable I used to hold IFS while it is changed so I can set it back afterwards, since I don't know if it will be the default or not.

Code:
# cat test
1
2
3
4
5 6
# OIFS="$IFS"
# a=$(cat test)
# echo $a
1 2 3 4 5 6
# IFS=' '
# echo $a
1 
2 
3 
4 
5 6
# IFS="$OIFS"
# echo $a
1
2
3
4
5
6

# 6  
Old 01-08-2008
Thanks

Thanks a lot !!

Nice explaination !!! You made my day ! Smilie

Previous Thread | Next Thread
Test Your Knowledge in Computers #547
Difficulty: Medium
unary operators only operate on universal variables.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Printing awk outputs

Hello All, I have the following command which works partially: gzcat *2016-03-25_*gz | gawk -F"|" ' BEGIN{format = "%-10s %-13s %-17s %-35s\n"; printf format, "EVENT_TYPE","RESPONSE_CODE","INTERNAL_ERR_CODE","FLOWNAME"; printf format, "----------", "-------------", "-----------------",... (6 Replies)
Discussion started by: EAGL€
6 Replies

2. UNIX for Dummies Questions & Answers

gunzip outputs gibberish

I run cygwin on windows 7, and have been using the windows command line. I've been trying to gunzip some previously compressed large sequence output files in .txt.gz format. This worked for about the first 10 files and then for the rest of them the file (as viewed using the 'head' command) is... (1 Reply)
Discussion started by: woceht
1 Replies

3. UNIX for Dummies Questions & Answers

Need help with 2 similar commands with different outputs

NEVERMIND.... "$TIME" did the trick... stupid me. :o Hi, I know that this must be something pretty dumb but I cant figure it out myself. I have this: #!/bin/bash TIME="Time: 20120611" FILE=file.log grep $TIME $FILE And I get the following output: file.log:Time: 20120611... (2 Replies)
Discussion started by: RedSpyder
2 Replies

4. Shell Programming and Scripting

Storing outputs into variables

I need to know how to store output from one command so that it can initiate another command. chktraf -s | cut -c1-4 output would look like 321 142 256 342 123 Then if the value of the output = 0, then initiate next command. if then for xx in 01 02 03 04 05 06 07 08 09 10 do ... (4 Replies)
Discussion started by: Shaun74
4 Replies

5. Shell Programming and Scripting

Handling SQL prompt through Perl expect module

Hi All, I have a doubt whether expect module in perl will work in SQL prompt or its applicable only for shell prompt ? Thanks, Arun V (2 Replies)
Discussion started by: arun_maffy
2 Replies

6. Shell Programming and Scripting

create outputs from other command outputs

hi friends, The code: i=1 while do filename=`/usr/bin/ls -l| awk '{ print $9}'` echo $filename>>summary.csv #Gives the name of the file stored at column 9 count=`wc -l $filename | awk '{print $1}'` echo $count>>summary.csv #Gives just the count of lines of file "filename" i=`expr... (1 Reply)
Discussion started by: rajsharma
1 Replies

7. Programming

strcat outputs garbage

Anyone have any ideas why when using strcat function I would get some garbage at the beginning of the output string? what I'm doing is something like the following example. Code: char temp; char tempHolder; for(int i=0;i<something;i++){ sprintf(temp,"%u ", someVariable);... (2 Replies)
Discussion started by: airon23bball
2 Replies

8. UNIX for Dummies Questions & Answers

Multiple Substring Outputs

Hello, I am reading a file with millions of lines in it. Each line is big line containing several xml tags. I need to Output just the value of two tags in a seperate flat file. For eg- I need to output whats present in <ComponentName> something </ComponentName> and another tag is... (2 Replies)
Discussion started by: sunnybehl
2 Replies

9. Shell Programming and Scripting

compare 2 outputs

Hello, I have a shell script that is used as follows: ./script -s <Oracle_server_name> the script returns several lines in this format: parameter name required value Current Value comments --------------- ------------- -------------- --------- My objective now is to compare 2... (1 Reply)
Discussion started by: melanie_pfefer
1 Replies

10. Shell Programming and Scripting

How to print two sql query outputs side by side in excel

Hi, I have to sql queries like select sno,sname from temptable; select deptno,dname from depttable; In excel i want to specify the column number to which my output should be displayed. please help me in this... thanks in advance... (6 Replies)
Discussion started by: prasee
6 Replies

Featured Tech Videos