Tuesday, May 17, 2011

Connect Oracle from Shell and execute SQL.

A shell script that connects to Oracle and execute SQL..
The script will run on Oracle installed box, and do authentication against the username, password..

and verifies the Oracle_sid passed is valid. Once the authentication passes it executes the sql on Oracle.

The skeleton of the Script looks similar to the below...

Begin your main method.

main() {main() {

Make sure the User executing the script is Oracle user.

#1. Make sure only user oracle can run the script
if [ "$(whoami)" != "oracle" ]; then
   echo "This script must be run as oracle user" 1>&2
   exit 1
fi

#2. Read the Oracle System User Name

oracle_system_user=$PARAM(system)

#3. Read the Oracle System User Password

oracle_system_password=$PARAM(password)

#4. Prompt for ORACLE_SID
database_name=$PARAM(test_db)

#5. Check the given credentials are valid and that the SID exists.
checkAuth # Have this function created outside of main method.

#6. If, the Authentication suceeds, Export the Oracle SID
export ORACLE_SID=database_name

#7. Execute the Query you need to.
echo "Processing..............please do not exit.........."
executeProcess # Have this metod outside of main method.
echo "Execution Success........!"

# Close the main method.

}

main


# function to check if the given SID is valid.
checkAuth() {
    #Connect to oracle
    sqlplus -s -l $oracle_system_user/$oracle_system_password@$database_name << STD_IN
    exit;
STD_IN
    errorCode=$?   
        if [ ${errorCode} -ne 0 ];then
            echo "Unable to connect the DB with the supplied credentials."
            exit 1
        fi   
}


# function to execute process, DATABASE_USERNAME.
executeProcess() {
    #Connect to Oracle
    sqlplus -s -l $oracle_system_user/$oracle_system_password@$database_name << STD_IN   
    select sysdate from dual;   
    exit;
STD_IN
}


For reading the Params we can prompt the User to enter the valuse dynamically., by writing a readParam function.,
instead of hardcoding the values for username, password and sid values.. In that way the script can be uses as dynamic.

Similarly, the executeProcess method can be used to execute Plsql blocks.

The command that goes between "<< STD_IN" and "STD_IN" will be directly interpretted by Oracle, so we can have
only the valid sql commands inside... and can give any user defined name for "STD_IN"..it can be "XXXYYZZ".

that is only to say the interpreter the commands inside will get directly executed....!

No comments:

Post a Comment