How to Format the result driven from a SQL Query

Shell Programming and Scripting
1

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.:b:

2

use cut or awk. see man pages of cut.

3

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

somethng like this

 
#! /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
4

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!!

5 
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.

6

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

sqlplus -s << EOF
batchexc/rogerprod@FXGSM1U1
7

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 &lt;&lt; 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.. :confused:

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

8

paste the exact code from the program, in {code} tags.

9

The problem is that the closing EOF is indented.
Either remove the indent or use sqlplus -s <<-EOF
(note the minus sign between << and EOF).