Connect to DB and return a value in custom form

Shell Programming and Scripting
1

Hi,

I want to run a script in unix, where I connect to a db and run an sql, but display the result in a custom way.
lets say my query return '500', I want the person who is running the script to see: MID=500 and not the whole sqlplus values.

Thanks,
Amit

2

Hi amitlib,

Welcome to the forum.

Could you please post some real example?
the last part of your requirement contradict with the previous.

In simple way, you can modify the output of the query in whatever way you want.But we need sample data.

3

Assume that your sql data is "Brad,123,xyz"

$ cat script.sh

sql_output="Brad,123,xyz"

name=$(echo $sql_output | cut -d',' -f1)
no=$(echo $sql_output | cut -d',' -f2)
id=$(echo $sql_output | cut -d',' -f3)

echo "Your name is $name and your id is $id"

$./script.sh

Your name is Brad and your id is xyz
4

This is the script that I am running right now:

#!/bin/bash  

sqlplus CTEMEA_BM04/payplus1@LOADTST   << EOFSQL
     SELECT *
      FROM (select  mif.mid from mif order by mif.mid desc)
      WHERE rownum < 2
      ORDER BY rownum;
EOFSQL

Please use code tags for code and data samples, thank you

5

Can you paste the output of the below script?

#!/bin/bash
 
ret=`sqlplus -s CTEMEA_BM04/payplus1@LOADTST << EOFSQL
SELECT *
FROM (select mif.mid from mif order by mif.mid desc)
WHERE rownum < 2
ORDER BY rownum;
EOFSQL`
 
echo $ret

--ahamed

1 Like
6

I found the answer:

#!/bin/ksh
#set -xv
db_connection="CTEMEA_BM04/payplus1@LOADTST"

db_con()
{
	sqlplus -s $db_connection <<EOFSQL
	set pagesize 0
	set head off
	set feedback off
	${1}
EOFSQL
 }
 
MID=`db_con "SELECT * FROM (select mif.mid from mif order by mif.mid desc) WHERE rownum < 2 ORDER BY rownum;"`





echo $MID

Please use code tags for code and data samples, thank you