Visit Our UNIX and Linux User Community


SQL Data with Spaces into Arrays


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQL Data with Spaces into Arrays
# 1  
Old 10-19-2007
SQL Data with Spaces into Arrays

Hi

Simple thing has been driving me nuts. I have used the following code is ksh scripts to get data from Oracle table and then display it, allowing user to select one of the data options returned...


REP_DATA=`sqlplus -s ${WMSDB} <<EOF
SET SERVEROUTPUT ON FEEDBACK OFF VERIFY OFF ECHO OFF HEADING OFF
SELECT ACTIVITY_ID, ACTIVITY_NAME
FROM ACTIVITY;
exit;
EOF`

#get data into array
set -A temp_arr ${REP_DATA};

#for each entry in the array, launch a process
x=0
while [ $x -lt ${#temp_arr[*]} ]
do
# extract the name and process flag status from the array
echo "$x\t${temp_arr[x]}";
let x=x+1;
done;

echo "Select the Activity you are working on: \c"
read ACT_ID
...


Normally this stuff is fine but now I have the problem that the ACTIVITY_NAME field has spaces in...

ACTIVITY_ID ACTIVITY_NAME
101 Badminton
202 Sailing
203 Abseiling and Climbing
301 Painting, Drawing and Sketching

So the set -A command ends up in an array of...
(101,Badminton,202,Sailing,203,Abseiling,and,Climbing,301,Painting,,Drawing,and,Sketching)

I tried to wrap the ACTIVITY_NAME using double quotes (i.e. SELECT ACTIVITY_ID, '"' || ACTIVITY_NAME || '"') in the SQL in the hope that UNIX would treat it as one string but it didn;t work.

Any guidance would be appreciated.

Thanks

Previous Thread | Next Thread
Test Your Knowledge in Computers #388
Difficulty: Medium
The Unix make command uses A.I. to maintain, update, and regenerate related programs and files.
True or False?

10 More Discussions You Might Find Interesting

1. Programming

Phantom Arrays in PL/SQL

Having issue with an oracle stored procedure that fetches 5k array size to an down stream application using oracle client interface. It is creating phantom arrays and keeps sending arrays that do not exist to begin with and congesting the connections. This happened when we upgraded from oracle... (1 Reply)
Discussion started by: mrn6430
1 Replies

2. Shell Programming and Scripting

Reading in data that has spaces in it.

I have a csv file called template.csv which has the following data Name, Age, Height Jessica Jesse, 18, 150 Now what I want to do is use a shell script to read the name age and height which looks like this: #!bin/sh INPUT='template.csv while read Name Age Height do echo... (2 Replies)
Discussion started by: JSNY
2 Replies

3. Shell Programming and Scripting

Perl : Arrays : where are these spaces coming from

Example data The sub and array in question sub parse_status { my $status_info = shift; my @info; push @info,"$1\n" if $status_info =~ /(System State : +)/; push @info, "System_Uptime : $1\n" if $status_info =~ /Uptime:.+?up (.+?), load/; push @info, "$1\n" if $status_info =~... (2 Replies)
Discussion started by: popeye
2 Replies

4. Shell Programming and Scripting

Loop through array of arrays of string with spaces

Hi I'm trying to loop through an array that contains other arrays and these arrays consist of strings with spaces. The problem is that I can't seem to preserve the spacing in the string. The string with spaces are either divided into multiple items if I change IFS to \n or all the elements of... (4 Replies)
Discussion started by: kidmanos
4 Replies

5. Programming

Allocating data arrays in C++

lets say that I have a two dimensional array: char myarray; and my process of taking in values from an input file is this: for(i=0;x<2;i++) // 2 is the amount of rows in the array { input>>myarray; } now when I display my array my output is this: why hello To make this work... (8 Replies)
Discussion started by: puttster
8 Replies

6. Shell Programming and Scripting

Format data to columns addind spaces

Hi all, I have a problem to format data from different database queries into one look. The input data are as follows, every line has the same number of values but a different number of characters: adata, bdata, cdata, ddata fffdata, gdata, hdata, idata jdata, kdata, ... (6 Replies)
Discussion started by: old_mike
6 Replies

7. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

8. Shell Programming and Scripting

Removing spaces from data

Hi, I want to remove spaces from data. Data is: 1,aa ,21, 22 2,a a ,23 ,24 3, ,25 ,26 output should be: 1,aa,21,22 2,a a,23,24 3, ,25,26 i.e i have to remove leading and trailing spaces.Not the space between data and also i dont want to remove the space if data is... (4 Replies)
Discussion started by: monika
4 Replies

9. Shell Programming and Scripting

How to load different type of data in a file to two arrays

Hi, I have tried to find some sort of previous similar thread on this but not quite close to what I want to achieve. Basically I have two class of data in my file..e.g 1,1,1,1,1,2,yes 1,2,3,4,5,5,yes 2,3,4,5,5,5,no 1,2,3,4,4,2,no 1,1,3,4,5,2,no I wanted to read the "yes" entry to an... (5 Replies)
Discussion started by: ahjiefreak
5 Replies

10. Shell Programming and Scripting

Reading in data sets into arrays from an input file.

Hye all, I would like some help with reading in a file in which the data is seperated by commas. for instance: input.dat: 1,2,34,/test for the above case, the fn. will store the values into an array -> data as follows: data = 1 data = 2 data = 34 data = /test I am trying to write... (5 Replies)
Discussion started by: sidamin810
5 Replies

Featured Tech Videos