Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2015
    Posts
    2

    Unanswered: doubt in shell script

    I need to invoke an three different oracle function for the three different conditions.

    I have coded myself according to the conditions. but i am not sure whether i have coded correctly. it would be great if anyone call help me in this.

    Condition requirement :
    Code:
    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:
    Unix script :
    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"

  2. #2
    Join Date
    Jul 2015
    Posts
    2

    Oracle function invoking using shell script

    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.
    Oracle function condition requirement

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

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by arjunaram View Post
    . . . e t c
    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.
    . . . e t c
    There is insufficient information to know if code is efficient or not.
    Also it seems you are incorrectly mixing bind variables with sqlplus arguments and regular variables.
    In fact I doubt the code actually works, does it?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •