sql script to find out the userid

Prasun1 · 06-30-2005, 10:20 AM

Hi all,

This is another problem which is thwarting my work. I am writing a shell script . Now from this script I can easily check the name of the userid by running a whoami. After this I am also connecting to an Oracle database through sqlplus.

Now when I enter sqlplus there are situations when I have to issue queries based upon this userid..I mean this userid will be used in the where clause of many queries.

Now my question is how can I make this variable available once I am in sqlplus.

Thanks and Regards
Prasun.

druuna · 06-30-2005, 11:50 AM

Hi,

This should work, using a here-document:

Code:

#!/bin/bash

USER="`whoami`"
TMP=/path/to/spoolfile

sqlplus -silent <<SQLpart
<user>/<pwd>
  spool ${TMP}
  set linesize 70
  set pagesize 0
  set heading off   
  set feedback off
  select * from TABLE where USERID like '${USER}'
  spool off
  exit
SQLpart

As you can see both TMP and USER are declared in the shell and used by sql.

Hope this helps.

Prasun1 · 06-30-2005, 10:43 PM

Hi,

Thanks a lot for your reply. It will be very nice if you can tell what does << SQLPART and SQLPART at the end of the coding really do ? Rest of the things are well understandable.

Thanks and Regards
Prasun

druuna · 07-01-2005, 12:07 AM

Hi again,

The SQLpart and SQLpart are the beginning (<<SQLpart) and ending of a container. Everything between these are given to the command (in this case sqlplus -silent).

You can choose any (none reserved) word for the SQLpart, as long as they are the same (<<HERE ..... HERE is often used).

Also take a look at the bash manpage, this section: Here Documents

Hope this clears things up a bit.

Prasun1 · 07-01-2005, 12:26 AM

Thank you so much

One more thing which I would like to know from you i.e how can I make some sql/plsql variable availble in my shell. This problem is occuring because I am selecting some rows while I am in sqlplus environment . These same row fields should be displayed in the screen which I have designed in shell scripting . But once I return to shell how can I have these values available ?

Thanks and Regards
Prasun.

druuna · 07-01-2005, 09:54 AM

Hi again,

If I understand correctly you want (parts of) the ouput of the sql statement put in variables and work with these.

There are a few ways of doing this:

1) use the spoolfile (if it is created). Parse the correct fields and work with them.
- this is kinda expensive, you need to write to and from a file.

2) 'pipe' the output to another command. I.e:

sqlplus -silent <<SQLpart
<user>/<pwd>
select * from TABLE where USERID like '${USER}'
exit
SQLpart | while read row1 row2 row3 row4
do
echo "user ${row1} is a ${row2} and a good ${row4}"
done

- this solution could become tricky if the output of the sql statement hasn't a fixed amount of variables (in a fixed position). That's why, in the above example, I do read row3, but do not use it (you could also create a better sqlstatement).

Hope this clears things up a bit.