Connect to DB and return a value in custom form


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Connect to DB and return a value in custom form
# 1  
Old 11-29-2011
Connect to DB and return a value in custom form

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  
Old 11-29-2011
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  
Old 11-29-2011
Assume that your sql data is "Brad,123,xyz"

Code:
$ 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"

Code:
$./script.sh

Your name is Brad and your id is xyz

# 4  
Old 11-29-2011
SQL that I am running

This is the script that I am running right now:
Code:
#!/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
Moderator's Comments:
Mod Comment How to use code tags

Last edited by Franklin52; 11-29-2011 at 05:57 AM.. Reason: Please use code tags for code and data samples, thank you
# 5  
Old 11-29-2011
Can you paste the output of the below script?
Code:
#!/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
This User Gave Thanks to ahamed101 For This Post:
# 6  
Old 11-29-2011
I found the answer:
Code:
#!/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

Moderator's Comments:
Mod Comment How to use code tags

Last edited by Franklin52; 11-29-2011 at 06:24 AM.. Reason: Please use code tags for code and data samples, thank you
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Connect direct - SFTP - List of servers that I can connect

Greetings Experts, I am working for a bank client and have a question on connect-direct and SFTP. We are using Linux RedHat servers. We use connect-direct to transfer (NDM) files from one server to another server. At times, we manually transfer the files using SFTP from one server to another... (2 Replies)
Discussion started by: chill3chee
2 Replies

2. Shell Programming and Scripting

Return: can only `return' from a function or sourced script

Not sure where the problem is. I can run the script without any issue using the following command. . /opt/app/scripts/cdc_migration.sh But it fails with the below error when I try it this way /opt/app/scripts/cdc_migration.sh /opt/app/scripts/cdc_migration.sh: line 65: return: can only... (1 Reply)
Discussion started by: svajhala
1 Replies

3. Cybersecurity

When i start CSF i cant connect VPS or download any data into it It appears i cant connect Linux VP?

It appears i cant connect linux VPS server via SSH or i cant SCP any file to it and i cant wget any file TO it (from inside it) while CSF (Config Server Firewall, LFD is running. Just after isntall in default configuration and after changing TESTING mode to LIVE mode. Trying to wget & install... (1 Reply)
Discussion started by: postcd
1 Replies

4. Shell Programming and Scripting

Remove x lines form top and y lines form bottom using AWK?

How to remove x lines form top and y lines form bottom. This works, but like awk only cat file | head -n-y | awk 'NR>(x-1)' so remove last 3 lines and 5 firstcat file | head -n-3 | awk 'NR>4' (5 Replies)
Discussion started by: Jotne
5 Replies

5. Shell Programming and Scripting

Transpose Data form Different form

HI Guys, I have data in File A.txt RL03 RL03_A_1 RL03_B_1 RL03_C_1 RL03 -119.8 -119.5 -119.5 RL07 RL07_A_1 RL07_B_1 RL07_C_1 RL07 -119.3 -119.5 -119.5 RL15 RL15_A_1 RL15_C_1 RL15 -120.5 -119.4 RL16... (2 Replies)
Discussion started by: asavaliya
2 Replies

6. AIX

AIX Remote Connect Fail With “No more multiple IP addresses to connect” Error

We have a production server at a client site running AIX. And recently when users are trying to connect to it via telnet, it prompts "No more multiple IP addresses to connect". Can I know what does this error mean? and how to rectify this? Thanks. (2 Replies)
Discussion started by: a_sim
2 Replies

7. UNIX for Dummies Questions & Answers

Changing ip in a custom way

Hi. I hope someone can help me. I have e very special question. I have a Lunix server and I have installed Webmin on it. This way, I can create a login for an other user and give him restricted access to some custom commands I set up. One of the commands i would like to setup, is for him to... (9 Replies)
Discussion started by: Wonderke
9 Replies

8. UNIX for Dummies Questions & Answers

to pick up the Return Code ( RC) from the mailx command and return it to SAS uisng 's

Hi All, Can anyone please let me know the syntax / how to pick up the Return Code ( RC) from the mailx command and return it to SAS uisng 'system()' function and '${?}'. I am in a process to send the mail automatically with an attachment to bulk users. I have used 'Mailx' and 'Unencode'... (0 Replies)
Discussion started by: manas6
0 Replies

9. UNIX for Advanced & Expert Users

Changing Unix form to Microsoft Word form to be able to email it to someone.

Please someone I need information on how to change a Unix form/document into a microsoft word document in order to be emailed to another company. Please help ASAP. Thankyou :confused: (8 Replies)
Discussion started by: Cheraunm
8 Replies
Login or Register to Ask a Question