SQL Data with Spaces into Arrays


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
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
Login or Register to Reply

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

More UNIX and Linux Forum Topics You Might Find Helpful
Phantom Arrays in PL/SQL
mrn6430
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...... Programming
1
Programming
Perl : Arrays : where are these spaces coming from
popeye
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 =~...... Shell Programming and Scripting
2
Shell Programming and Scripting
Loop through array of arrays of string with spaces
kidmanos
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...... Shell Programming and Scripting
4
Shell Programming and Scripting
Allocating data arrays in C++
puttster
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...... Programming
8
Programming
How to use sql data file in unix csv file as input to an sql query from shell
Nareshp
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...... Shell Programming and Scripting
2
Shell Programming and Scripting