Home » Infrastructure » Unix » oracle function invoked using shell script (HP UNIX)
oracle function invoked using shell script [message #640272] Sun, 26 July 2015 03:07 Go to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
For the below oracle function need to be triggered.

I have written a shell script and invoked the oracle function. it would be great if anyone can check whether the code one is efficent or not.
Please change the code if you feel whichever is correct.
Oracle function condition requirement

CASE 1: 
(LOCATION = 'chennai'| LOCATION = 'Banglore')
:VALUE:=LOCATION_COUNT.CHENNAI_BANG
               (&LOCATION,&SALES);
case 2: 
(LOCATION ='SALEM'|LOCATION = 'TRICHY' |LOCATION = 'PONDI')
:VALUE:=LOCATION_COUNT.SAL_TRICHY_PONDI
               (&LOCATION,&SALES);
 
case 3:
(LOCATION = 'TIRUPUR')
:VALUE:=LOCATION_COUNT.GET_TIRUP
               (&LOCATION,&SALES);


Code:
cat function_check.ksh
chennai_blr () {
        VALUE=`sqlplus -S /NOLOG << EOF
        CONNECT dbs/passwd@dbtod
        SET head off
        SET serveroutput on
        select LOCATION_COUNT.CHENNAI_BANG
               (&LOCATION,&SALES) from dual;
        exit;
EOF
               }
 
Salem_Trichy_Pondi  () {
VALUE=`sqlplus -S /NOLOG << EOF
        CONNECT dbs/passwd@dbtod
        SET head off
        select :LOCATION_COUNT.SAL_TRICHY_PONDI
               (&LOCATION,&SALES) from dual;
        exit;
EOF`
 
     }
Tirupur () {
               VALUE=`sqlplus -S /NOLOG << EOF
        CONNECT dbs/passwd@dbtod
        SET head off
        select LOCATION_COUNT.GET_TIRUP
               (&LOCATION,&SALES); from dual;
        exit;
EOF`
           }
while read file
do
        if [[ "$file" == "chennai" || "$file" == "banglore" ]]
        then
                chennai_blr
        elif [[ "$file" == "salem" || "$file" == "trichy" || "$file" == "pondi" ]]
        then
                Salem_Trichy_Pondi
        elif [[ "$file" == "tripur" ]]
        then
                Tirupur
        else
                echo "You are out of chennai, blr, Salem_Trichy_Pondi and Tirupur, seems you are in $file"
        fi
done < "function_check"

Re: oracle function invoked using shell script [message #646671 is a reply to message #640272] Wed, 06 January 2016 12:59 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi


I think you can minimize the number of functions using oracle SELECT CASE statement and call the function only once by passing argument as $file.
The final else will remain as it is for city validation

garan
Re: oracle function invoked using shell script [message #646678 is a reply to message #646671] Wed, 06 January 2016 13:46 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you really think OP cares about your post 6 months later?

Previous Topic: How to pass unix declared variable into SQL and PL SQL block
Next Topic: Hide files from underprivileged users
Goto Forum:
  


Current Time: Thu Mar 28 03:51:56 CDT 2024