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
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
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
2. Shell Programming and Scripting
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
3. Shell Programming and Scripting
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
4. Shell Programming and Scripting
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
5. Shell Programming and Scripting
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
6. Programming
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
7. Shell Programming and Scripting
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
8. Shell Programming and Scripting
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
9. Shell Programming and Scripting
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
10. Programming
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
LEARN ABOUT DEBIAN
sql::translator::producer::postgresql
SQL::Translator::Producer::PostgreSQL(3pm) User Contributed Perl Documentation SQL::Translator::Producer::PostgreSQL(3pm)
NAME
SQL::Translator::Producer::PostgreSQL - PostgreSQL producer for SQL::Translator
SYNOPSIS
my $t = SQL::Translator->new( parser => '...', producer => 'PostgreSQL' );
$t->translate;
DESCRIPTION
Creates a DDL suitable for PostgreSQL. Very heavily based on the Oracle producer.
Now handles PostGIS Geometry and Geography data types on table definitions. Does not yet support PostGIS Views.
PostgreSQL Create Table Syntax
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
| table_constraint } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Create Index Syntax
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( column [ ops_name ] [, ...] )
[ WHERE predicate ]
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
[ WHERE predicate ]
SEE ALSO
SQL::Translator, SQL::Translator::Producer::Oracle.
AUTHOR
Ken Youens-Clark <kclark@cpan.org>.
perl v5.14.2 2012-01-18 SQL::Translator::Producer::PostgreSQL(3pm)