Calling SQL and PL/SQL codes from Shell

#################################################################################
#!/bin/ksh
##################################################################################
# Filename: test.sh
# Name    : Özay Akdora (https://akdora.wordpress.com)
# Date		: 04.03.2011
# Purpose : Oracle - Shell Integration (SQL Operations & PL/SQL Execution )
# Env			: This example is uses Oracle 11G with "/export/home/oracle/app/product/11.2c" path and korn shell script

# First of all we need to define some variable to access Oracle Database
export ORACLE_HOME=/export/home/oracle/app/product/11.2c
export LD_LIBRARY_PATH=/export/home/oracle/app/product/11.2c/lib
export PATH=/usr/local/bin:/usr/local/sbin:/usr/bin:/export/home/oracle/app/product/11.2c/bin:/export/home/ubtool/ubSQL_5.0.1:

# Then, we need to define username/password (@ optional another database TNS definition).
export db_connection_url=scott/tiger@MYDB
# I needed to connect to another database to query a table that has my configuration data.
# Your shell code may locate in an another machine that you want to connect.
# Anyway, You have to define TNS info of MYDB database to /export/home/oracle/app/product/11.2c/network/admin/tnsnames.ora

# You have to give permission to execute this file. For example : chmod 777 test.sh
# You also have to give permission, if you put this file to a folder that you created new.

# Run as : "./test.sh"

#######################################################################
# Let's see what we got here;
# Example 1: Access db and query SYSDATE:

echo "########## Example 1: SYSDATE ############"
RETVAL=`sqlplus -s $db_connection_url <<EOF
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') my_date from dual;
exit;
EOF`

echo $RETVAL
# As you see, we connected to database with sqlplus command ( We defined it's path to PATH above.
# Then we set username and password with $db_connection_url variable
# After that, we tell the command line execute what you see between EOF words 🙂 (Note: You can use ENDASDKAJSDK instead of EOF ) 🙂
# Finally, we set the date to $RETVAL variable and prints it to screen.
# Let me explain that : Think like this way. We are using SQLPLUS (in any environment) and executing some commands and getting some result on the screen
# In this example; We set everything to $RETVAL that we see the results of sqlplus screen. Everything!

#######################################################################
# Example 2 : SQLCODE
echo "########## Example 1: SQLCODE ############"
RETVAL=`sqlplus -s $db_connection_url <<EOF
whenever sqlerror exit sql.sqlcode;
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') my_date from dual;
exit;
EOF`

ERROR_CODE=$?

#Let me check the error code.
if [ $ERROR_CODE != 0 ]
then
  echo "There are some errors. ErrorCode: $ERROR_CODE";
else
  echo "Command executes successfully. ErrorCode: $ERROR_CODE";
fi

echo $RETVAL
#######################################################################
# Example 3 : SQL(Select) with WHERE clause
echo "########## Example 3: SQL with WHERE clause ############"

#Declare the variable
feed_id=1;

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select * from extract_external_feeds where feed_id='$feed_id';
exit;
EOF`

#echo $RETVAL
v_result=`echo -n $RETVAL|awk '{printf $4}'`
echo $v_result

# Let's see what we done here. 
# We added "set feed off echo off head off " line to command. So, I do want to see column names as result.
# In (feed_id='$feed_id') section do not forget to use (').
# We writes (awk '{printf $3}') the data of third column in my table.
# Note: We assumes that the data in the columns do not have any space like " " space  🙂
#######################################################################
# Example 4 : SQL(Select) with WHERE clause & Multiple Variables
echo "########## Example 4: SQL with WHERE clause ############"

#Declare the variable
feed_id=1;

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select ROW_LINE from EXTRACT_EXTERNAL_FEEDS_VW where feed_id='$feed_id';
exit;
EOF`

#echo $RETVAL
var1=`echo -n $RETVAL|awk -F\# '{printf $1}'`
var2=`echo -n $RETVAL|awk -F\# '{printf $2}'`
var3=`echo -n $RETVAL|awk -F\# '{printf $3}'`
echo $var1
echo $var2
echo $var3

# Since, I do want to make query for each column value, I return them with and deliminater like "#". The we parse it with awk command in shell.
# SELECT FEED_ID, FILE_PATH, MAIN_PATH, DB_VIEW_NAME FROM EXTRACT_EXTERNAL_FEEDS
#       
# CREATE OR REPLACE VIEW EXTRACT_EXTERNAL_FEEDS_VW AS
#  SELECT FILE_PATH || '#' || MAIN_PATH || '#' || DB_VIEW_NAME AS ROW_LINE, FEED_ID
#    FROM EXTRACT_EXTERNAL_FEEDS

#######################################################################
# Example 5 : SQL (Select) with dynamic column names
echo "########## Example 5 : SQL (Select) with dynamic column names ############"

feed_id=1;
column_name='DB_VIEW_NAME';

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select $column_name from EXTRACT_EXTERNAL_FEEDS where feed_id='$feed_id';
exit;
EOF`

echo $RETVAL
# We can pass variables to SQLPLUS commands like ($column_name)
#######################################################################
# Example 6 : Calling PL/SQL 
echo "########## Example 6 : Calling PL/SQL ############"

RETVAL=`sqlplus -s $db_connection_url <<EOF
var x number ;
exec  pk_my_package.prc_my_proc(1,1,:x);
print x;
exit;
EOF`

echo $RETVAL
# We can call PL/SQL codes as we work in SQLPLUS and read the outputs
#######################################################################
# Example 7 : Calling Dynamic PL/SQL 
echo "########## Example 7 : Calling Dynamic PL/SQL ############"
dynamicCode1="declare v_date date; begin select sysdate into v_date from dual; dbms_output.put_line(to_date(v_date,'dd.mm.yyyy hh24:mi:ss') ); end; ";
dynamicCode2="
var x varchar2(2000); 
exec prc_my_proc;
print x;";

RETVAL=`sqlplus -s $db_connection_url <<EOF
set serveroutput on;
$dynamicCode2
exit;
EOF`

echo $RETVAL
# We can also set dynamic variable that contains PL/SQL commands, but you have to set the variable with new lines to execute them successfully.
# If you set the variable like $dynamicCode1. The code does not work. You have to do it as $dynamicCode2. You may read this code from an config file or oracle table.
#######################################################################

# Finally, if we want to do fancy. The following is a way one can describe this script as Oracle job. 
# 
#	BEGIN
#		DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME        => 'akdora_test',
#									  PROGRAM_TYPE        => 'EXECUTABLE',
#									  PROGRAM_ACTION      => '/export/home/bonjovi/ozay/test.sh',
#									  NUMBER_OF_ARGUMENTS => 0,
#									  ENABLED             => TRUE,
#									  COMMENTS            => 'Fantasy Program');
#	END;
#	/
#
#
#
# I hope this entry is helpful for you.
# Cheers

Advertisement