Displaying the data from the select query in a particular format

Shell Programming and Scripting
1

Hi,

I have a shell script that returns 10 records for the column Name and age from a select query. Where when i store those data in retrieve_list.txt file i need to store the data in a particular format like:-

$Jason$30
$Bill$23
$Roshan$25

Here is my script:

1) database_retrieve_script.sh
-------------------------------

#!/bin/ksh

LOGFILE="databaseRetrieve.log"
USER="myuser"
PASS="mypass"
SID="sampledb"

sqlplus -s ${USER}/${PASS}@${SID} << EOF >${LOGFILE} 2>&1
whenever oserror exit 98;
whenever sqlerror exit 99;
@retriveData.sql
exit 140;
EOF
SQL_DATA="$?"
if [ "$?" -ne 140 ]
then
echo "Connection error (Oracle down?)"
else
echo "Connection estabilished, script executed."
${SQL_DATA} > retrieve_list.txt
fi

2) retriveData.sql
------------------------
#!/bin/ksh

select * from retrieveTable;

Here the script will get the whole data and won't store it
in the above format.I want the data to be stored in the above format.Pls. do help me in this.It is urgent...

Thanks

2

kindly change your retriveData.sql

as follows,

select '$'||ColumnName1||'$'||ColumnName2 from <retrieveTable>;
3

Thanks Manas... But how i will get the data in the format i want..Pls. do help me in this.

4

Pls. do reply on this to get the data in above format.

5

U can use the the query as provided in ur sql file "@retriveData.sql" and then call the shell script.

  • If there are some error/exception or whatever is the difference in the output vs expected, please specify
6

Thats right... I can get the data(Name,Age) from the select query but i need to organize the data in a particular format. Like:

$Jason$30
$Bill$23
$Roshan$25

and save those data into a file. Here my question is that how to organize the data in the above format after getting the data from the query? Pls. do help me...

7

Pls. do reply i am finding it difficult to store the data in above format in a .txt file..Pls. do help me in this. It is urgent...

8

hey
if you want to put your desired format in .txt file ,

then

change the following line in the script
database_retrieve_script.sh

# replacing LOGFILE="databaseRetrieve.log to"
LOGFILE="databaseRetrieve.txt"

and as earlier retriveData.sql has

select '$'||ColumnName1||'$'||ColumnName2 from <retrieveTable>;

then run database_retrieve_script.sh.
and then check databaseRetrieve.txt ....

hopes i understood your query ????????:confused:

9

Manas,

I want to store the data in this format in a .txt file:-

$Jason$30
$Bill$23
$Roshan$25.

When i retrieve the data from the database i will getting the data as junk and it wont be displayed in any format.
To get the data in the above format, what i need to do ?
I need to parse the data and set it with the delimeter $.
How to do it..Pls. do help me in this..

10

No replies for this..Pls. do shed some light into this..It is really urgent.

11

Did you try the suggested solution?

$ sqlplus -s '/ as sysdba'<<!
> set pages 0 feed off echo off
> select '$'||name||'$'||age from retrieveTable;
> !
$Jason$30
$Bill$23
$Roshan$25
12

Thanks..When i try to use sqlplus i am getting an error saying "sqlplus : not found". sqlplus is a default command in shell script ? or we need to install it?
pls. do provide an answer for this..

13

No,
sqlplus is an oracle utility, not a standard shell command.
Given your question, I believe you should read the Oracle documentation first ...

14

rado, Can u shed some light in to this..whether i need to install sql * plus in Unix to make use of the sqlplus utility? What are the setup i need to follow? or any classpath settings? Pls. do provide some help...

15

In my humble opinion, this is somehow off-topic here -
the topic is too complex and not appropriate for a shell scripting forum.

I'd go to otn.oracle.com to search and read the documentation.

16

Thanks Rado...