Akdora’s Blog

Programming, Oracle, Life, Fun

Calling SQL and PL/SQL codes from Shell March 6, 2011

Filed under: Linux,Oracle — Akdora @ 11:24 am
Tags: , , , , , , , , ,

#################################################################################
#!/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

Advertisements
 

Rules of Precedence in SQL Where Clause February 18, 2009

Filed under: SQL — Akdora @ 8:49 pm
Tags: , , ,

Expressions in where clause process in the following order:

Number Expression
1 Arithmetic Operators
2 Concatenation Operator
3 Comparision Condition
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not Equal To
7 NOT Logical Condition
8 AND Logical Condition
9 OR Logical Condition

 

For example on AND and OR conditions:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
 
SQL> set serveroutput on
 
SQL>
SQL> SELECT t.employee_id, t.first_name, t.last_name, t.salary  ,t.manager_id
  2    FROM employees t
  3   WHERE t.salary > 15000
  4         AND t.manager_id = 100
  5         OR t.manager_id = 103;
 (more...)
 

Rownum & Order by September 26, 2007

Filed under: Non-technical — Akdora @ 8:27 am
Tags: , , , ,

Warning,

If you user rownum like this,

SELECT *

FROM TABLE

WHERE rownum < 2

ORDER BY column

you do this : get the first row that readed from database, and sort the 1 row.

Correct :

SELECT *

FROM (SELECT *

   FROM TABLE

   ORDER BY COLUMN

   )

WHERE rownum < 2

That means : sort the rows and get the first row.

 

For paging:

SELECT ID  FROM (SELECT ID, ROWNUM AS RNUM FROM (SELECT ID FROM ROWNUM_ORDER_TEST ORDER BY ID) WHERE ROWNUM <= 8) WHERE RNUM >= 4;