Retrieving values from the oracle table

Shell Programming and Scripting
1

Hi,

How to retrieve two different date values(min & max) from the oracle table and assign to two different variables in the shell script to process further.

With Regards

2 
var=`sqlplus username/password@database<<EOF
select date from table;
exit;
EOF`
3

Hi,

how to return a date value from the function called ?

With Regards

4

In shell scripting, there is no concept of return value from function. Its a line by line execution. So u can directly use variables set in function afterwords. Consider following code.

function abc {
....
command 1
command 2
....
var=5
}

function main {
....
abc
date=$var           # I can directly use this variable here which was set in abc function
....
}

The only thing u need to remember is make sure functions are defined before its execution.

5

Hi,

I need to execute the below code to get the max & min date from the table:

su - oracle<<EOC
export PATH=${PATH}:${ORACLE_HOME}/bin
id -a
exit
EOC

`sqlplus abc/xyz123@localdb<<EOS
select min(time) from tab_purg;
select max(time) from tab_purg;
exit
EOS`

I want to store the date values retrieved from the above two queries in two different variables and want this variable values to display on the screen.

How can it be done ?

With Regards

6 
set `sqlplus abc/xyz123@localdb<<EOS
select min(time)||"|"||max(time) from tab_purg;
exit;
EOS`

bothDates="$1"
first=`echo $bothDates|cut -d"\|" -f1`
second=`echo $bothDates|cut -d"\|" -f2`
echo "First=$first and Second=$second"
7

Hi,

Below is the output of your solution:

First=SQL*Plus: and Second=Release

I need to display date values from the table tab_purg as explained earlier.

With Regards

8

Common..!! I have just given u the rough idea. U need to make changes in it as per your requirement. U can't directly copy and paste it to the prompt. Anyway, it is coming like this because u need to do initial settings before query execution.

set `sqlplus -S abc/xyz123@localdb<<EOS
set head off;
select min(time)||"|"||max(time) from tab_purg;
exit;
EOS`

sqlplus -S option will not display login details and also headers are subjected to be removed.

1 Like
9 
myvar=$(
  sqlplus -s me/pwsd@somewhere << !
  set pages 0
  set feed off
  select min(time), max(time) from tab_purg;
  exit
!
)
echo $myvar

Put the last ! <<<<< all the way over to the leftmost column.