Shell Programming and Scripting

Hi All,

I want to format the result driven from the query into neat format.
For example pls find the below code,

#! /bin/sh

result='

sqlplus -s uname/passwrd@DBname

select no,name,address,ph_no, passport_no,salary,designation
from emp_table where salary>1000;

exit

EOF'

echo $result

The code results a huge set that is not fit into the screen.
the number of rows returned from the query is also not static.

please help me to the result in formatted manner.

please advice me if any coloring or formatting text style command are availabe.

Thankyou all in advance.

use cut or awk. see man pages of cut.

best way is to format in ORACLE and spool the output. use the set options to format your output.

somethng like this

Code:

 
#! /bin/sh

sqlplus -s "$connection_string"

spool ~/$HOME/sqloutput.txt

select no,name,address,ph_no, passport_no,salary,designation
from emp_table where salary>1000;

spool off

exit

EOF

Hi Panyam/Rakeshawasthi,

Thank you for the assistance.
The 'spool' works fine for my requrement.

one more help please!!
please let me know, Can I use sqlplus command inside if condition.

i got error in the below code.

if [ $j -gt 1 ]
then
echo "*** Accounts in Server-5 $arg_5 ***"

sqlplus -s batchexc/rogerprod@FXGSM1U1 <<EOF

select * from cmf where account_no=80320398;

exit

EOF

fi

I got the error "./RunBill.sh[123]: Syntax error at line 127 : `<<' is not matched."

I really couldn't troubleshoot this. since i found , the code works fine before i include this if stmt.

moreover, the first line inside if also not get printed.

pls help me on this. Thanks in advance!!

Code:

please let me know, Can I use sqlplus command inside if condition.

Yes you can use .

Check your code as the problem is not with what you have posted.

Not sure if writing exit is OK/necessary.
As a good practice login/passwd, you should give in the line after sqlplus...i.e.

Code:

sqlplus -s << EOF
batchexc/rogerprod@FXGSM1U1

Hi Panyam/Rakeshawasthi and All,

I have tried the 'sqlplus statement' inside 'if'. but i got the same error.

when i tried the same sqlplus without 'if' the query run perfectly.

I really really confused and can't get the solution.

pls assist me.

the below is my script (sql_if.sh)

#! /bin/sh

a=10

if [ $a -eq 10 ]
then

sqlplus -s << EOF
batchexc/rogerprod@FXCATU1

select * from student;

exit

EOF
fi


The output is: ./sql_if.sh[5]: Syntax error at line 8 : `<<' is not matched.

When I comment the 'if' statement this script works fine..

I really can't analyse this scenario, pls guide me.
Thanks in advance!!