SQL Data with Spaces into Arrays

Tags
shell scripts

 
Thread Tools Search this Thread
# 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

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
SQL: copying data up figaro Programming 3 2 Weeks Ago 04:38 PM
Phantom Arrays in PL/SQL mrn6430 Programming 1 09-01-2015 12:45 PM
Shell to SQL for data gksenthilkumar Shell Programming and Scripting 3 07-12-2015 02:36 PM
Reading in data that has spaces in it. JSNY Shell Programming and Scripting 2 05-02-2014 05:24 PM
Creating Multiple data files with spaces in the filename ban3rj33 Shell Programming and Scripting 2 03-18-2014 06:44 PM
Perl : Arrays : where are these spaces coming from popeye Shell Programming and Scripting 2 12-09-2013 07:56 AM
Loop through array of arrays of string with spaces kidmanos Shell Programming and Scripting 4 09-06-2012 12:29 PM
SQL: copying data down figaro Programming 8 12-08-2011 02:35 PM
Allocating data arrays in C++ puttster Programming 8 10-30-2010 09:37 PM
Format data to columns addind spaces old_mike Shell Programming and Scripting 6 11-20-2009 08:15 AM
How to use sql data file in unix csv file as input to an sql query from shell Nareshp Shell Programming and Scripting 2 11-09-2009 02:15 AM
Searching a particular string with spaces in a data file andy4013 Shell Programming and Scripting 9 06-19-2009 09:16 AM
Removing spaces from data monika Shell Programming and Scripting 4 11-09-2008 04:34 PM
How to load different type of data in a file to two arrays ahjiefreak Shell Programming and Scripting 5 10-09-2008 10:53 AM
Reading in data sets into arrays from an input file. sidamin810 Shell Programming and Scripting 5 07-06-2005 08:54 AM