Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: Bash looping thru select - how?

    Hi,

    In DB2 one could do something like this in bash scripting:

    db2 connect to mydatabase user userid using pswrd
    if [ $? -ne 0 ]
    then
    echo "Error when connecting to database"
    exit
    fi
    echo '*** Connected to mydatabase instance'

    We run this in DB2CLP shell (just like SQLPLUS shell).

    How do you do connect using bash as above in oracle? I want to integorrate the return code for success

    You are the creator of your own destiny!

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Here's a solution
    Code:
    $
    $ cat testplus
    #! /bin/ksh
    
    sqlplus << EOF
    $1/$2
    EOF
    
    exit 0
    $
    $ cat testconnection
    #! /bin/ksh
    
    if [[ $(testplus $1 $2 | grep ORA-01017 | wc -l) -ne 0 ]];
    then
            echo "Invalid password/username."
    else
            echo "Valid password/username."
    fi
    
    exit 0
    $
    $ testconnection notexists notexists
    Invalid password/username.
    $
    $ testconnection scott tiger
    Valid password/username.
    $
    The idea here is to have testplus execute the sqlplus connect for you, then you grep out its output looking for ORA-01017 (which is what oracle returns when an invalid username/password is supplied).
    Last edited by JMartinez; 02-22-06 at 16:22.

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    I am not a bash guru! Just use bash for database access.

    Comparing the db2/bash scripting this looks very cumbursom?

    Is there a simpler way of doing this??? like db2?

    You are the creator of your own destiny!

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Cumbersome ?? !! ??

    DB2 <> Oracle, that being said.. tools are different, you dont use the same tools you use on DB2 to connect to Oracle. When you call sqlplus from the shell, there's a context switch, that is.. control is passed to sqlplus until you exit it (wether it is sucessfull -with the exit command- or not -with ^D-), reason why there's no valid return code from it to the shell.

    But.. look at that, a file called testplus, with just 5 lines (you could even make it three lines long), on which you use $(testplus $1 $2 | grep ORA-01017 | wc -l) to test its 'return code' and you find this "cumbersome" for some reason..

    but perhaps this
    Code:
    /usr/users/oracle>
    /usr/users/oracle> cat testconnection
    #! /bin/ksh
    
    sqlplus $1/$2 > connstate << EOF
    EOF
    
    if [[ $(grep ORA-01017 connstate | wc -l) -ne 0 ]];
    then
            echo "Invalid password/username."
    else
            echo "Valid password/username."
    fi
    
    exit 0
    /usr/users/oracle>
    /usr/users/oracle> testconnection blah blah
    Invalid password/username.
    /usr/users/oracle>
    /usr/users/oracle> testconnection scott tiger
    Valid password/username.
    /usr/users/oracle>
    could looks simpler to you.

  5. #5
    Join Date
    Aug 2003
    Posts
    106
    JMartinez:

    Thanks for your very valuable input!

    Is there a way to a loop to thru select statement in BASH (or KORN) which returns multiple rows?

    As an example I could do this in db2:

    for spname in $(db2 -x "select procname from syscat.procedures where language='SQL'")
    do
    quotedName="'${spname}'"
    echo ${quotedName}
    db2 -x "select text from syscat.procedures where procname = ${quotedName}" > sp/${spname}.DB2
    done

    You are the creator of your own destiny!

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You must understand that DB2 <> Oracle, and thus things are done entirely different between both. For example, I could resume that whole shell script with a simple SQL*plus script on Oracle. I suggest you read about SQL*Plus and spooling files.

Posting Permissions

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