How to call stored procedure with argument(OUT parameter) from Unix shell script

~ sqlandplsql ~ Leave a comment

This post we will examine how to get the parameter to the caller (UNIX). ie OUT Oracle parameter will show in UNIX shell script

For example how to find total number of employees and display in shell script

— Below stored procedure returns total number of employees
create or replace procedure p_get_count(p_emp_count out integer)
as
begin
select count(*) into p_emp_count from emp;
exception
when others then
dbms_output.put_line(sqlerrm);
end p_get_count;

Next constructing the shell script

!/bin/ksh
#Purpose : Get employee count
#Name : get_count.sh
#Argument : p_emp_count(integer) as return variable

export DB_USERID=”SCOTT”
export DB_PWD=”TIGER”
export DB_SID=”ORCL”
ORACONN=”$DB_USERID/$DB_PWD@$DB_SID”

# Main Section
echo “program started…”
db_output=`sqlplus -s $ORACONN<<END
SET SERVEROUTPUT ON
declare
n_count integer;
begin
p_get_count(n_count);
dbms_output.put_line(n_count);
end;
/
exit;
END`
echo “Employee count=$db_output”
echo “program ended…”

Run the shell script get_count.sh

sqlandplsql$ sh get_count.sh
program started…
Employee count=8

PL/SQL procedure successfully completed.
program ended…
sqlandplsql$

Total there are 8 employees

Things to remember

  1. Always use error handling in stored procedures
  2. Add appropriate logs both in PL/SQL and Shell Script
  3. Add proper comments

Note:- Very much simple example have used to explain the fundamentals

Also read

How to call stored procedure with argument(IN parameter) from Unix shell script

 

 

Advertisements

How to call stored procedure with argument(IN parameter) from Unix shell script

~ sqlandplsql ~ Leave a comment

In DEPT table we have 5 records and we need to add a new Traveling department based out in Boston

dept

Let us see how we can achieve this through unix shell prompt.

First create a stored procedure to accept two IN arguments and a return flag as OUT parameter

— Below procedure insert new record into dept table
— returns Y is success and N for failure
create or replace procedure p_set_dept
(
deptname in varchar2,
deptlocation in varchar2,
success_flag out varchar2
)
as
n_deptno integer;
begin
— finds max deptno
select nvl(max(deptno),0)+1 into n_deptno from dept;
— insert script
insert into dept values(n_deptno,deptname,deptlocation);
commit;
success_flag := ‘Y’;
exception
when others then
success_flag := ‘N’;
dbms_output.put_line(sqlerrm);
end p_set_dept;

Next to create a shell script to call above procedure

#!/bin/ksh

#Purpose : Add new department
#Name : set_dept.sh
#Argument : p_dname(string) and p_dloc(string)

export DB_USERID=”SCOTT”
export DB_PWD=”TIGER”
export DB_SID=”ORCL”
ORACONN=”$DB_USERID/$DB_PWD@$DB_SID”

p_dname=$1
p_dloc=$2

function set_dept
{
p_deptname=$1
p_deptloc=$2
db_output=`sqlplus -s $ORACONN<<END
SET SERVEROUTPUT ON
declare
v_success_flag varchar2(1);
begin
p_set_dept(‘$p_deptname’,’$p_deptloc’,v_success_flag);
dbms_output.put_line(v_success_flag);
end;
/
exit;
END`
db_status=`echo $db_output | cut -b 1`
}

# Main Section
echo “program set_dept.sh started…”
set_dept $p_dname $p_dloc
if [ $db_status = “Y” ];
then
echo “Successfully ran set_dept.sh”
else
echo “Error in set_dept.sh –>$db_output”
exit 1
fi
echo “program set_dept.sh ended…”

Now we have both stored procedure and shell script. Let us execute and see

sqlandplsql$ sh set_dept.sh “Travel” “Boston”
program set_dept.sh started…
Successfully ran set_dept.sh
program set_dept.sh ended…

One record got inserted.

dept_after_insert

Trying to add same department again( Note that deptname column in DEPT table has a unique constraint).
Program correctly captures the error and displays in the console.

sqlandplsql$ sh set_dept.sh “Travel” “Boston”
program set_dept.sh started…
Error in set_dept.sh
Error=ORA-00001: unique constraint (CUPID_DEV.UQ_DEPT) violated
N
PL/SQL procedure successfully completed

Set_dept_shell

Similarly developers can call any type of procedures with different argument types.

Things to remember:-
1. Catch the return type from Oracle to Unix
2. Print the error if any
3. Capture all logs ( note that above program does not have logs )

 

Did find this article useful ? Please comment.

 

5 common mistakes Java Developers make when writing SQL

~ sqlandplsql ~ Leave a comment

Java developers always have tough time understanding SQL implementation. SQL is straight forward, simple and easy to write. Compared to SQL, java is very complex. Java developers are highly obsessed with objects, they think every thing should be object oriented. Seen many java developers design/create tables after designing/creating the UI screen, actually it should be other way around.

Let us examine some mistakes they make while developing data-centric applications

1. Using ROWNUM and ORDER BY CLAUSE together 

Example:-

orderby1

To find lowest salary employee, some java developers write like below

select * from emp where rownum = 1 order by salary asc ;  — wrong query

Surrely above query will attract logical bugs.

See the result

rownum_orderby_wrong

The result is wrong. Please use the correct query as given below

select * from (select * from emp order by salary asc) where rownum = 1 — correct query 

rownum_orderby_correct

Best Practice :-  Always apply rownum after order the rows. Note that do not use order by and rownum in the same where clause, use in each level of queries.

2. Pagination in Java memory

Java developers do pagination in Java instead in database side. Load all data into memory and paginating is a time-consuming process especially for large data sets. So if possible apply pagination before it loads into Java.

Example :- Top 5 salaried employees

select * from
(
select empno,empname,salary,row_number() over ( order by salary desc ) rk from emp
)
where rk <= 5;

Example :- 3rd to 6th top salaried employees

select * from
(
select empno,empname,salary,row_number() over ( order by salary desc ) rk from emp
)
where rk between 3 and 6;

Best Practice :- If possible use pagination in database level itself

3. Scare to use database procedures or packages from Java

Many Java developers scare to use database procedures or packages for processing the business logic. Instead they use standalone queries either directly in java code or in xml files. This is inefficient and use more network round trips. Some believe that stored procedures downgrade the performance which is not true.

Best Practice :- To write data centric business logic in database procedures rather than exposing direct queries in Java.

4. Loading all data into java memory

Seen in many java applications loading huge data into the memory (java cache) and process whenever require. This negatively impact the performance and increase the overhead of the applications.

Best Practice :- Load data into memory only when require.

5.  Using separate INSERT and UPDATE instead of MERGE statement

Many java developers (who use Oracle as their database) does not know there is a MERGE SQL command which will combine INSERT and UPDATE into one single SQL

a) INSERT statement

insert into emp (empno,empname,salary,deptno) values(19,’JOHN’,7800,5);

b) UPDATE statement

update emp set empname = ‘JOHNSON’where empno  = 19;

c) MERGE statement

MERGE INTO emp e
USING (select 19 as empno,’JOHNSON’ as empname,7800 salary,5 as deptno from dual) a
ON (e.empno = a.empno)
WHEN MATCHED THEN
UPDATE SET e.empname = a.empname,e.salary = a.salary, e.deptno = a.deptno
WHEN NOT MATCHED THEN
INSERT (empno, empname,salary, deptno)
VALUES (a.empno, a.empname,a.salary, a.deptno);

see the result.

merge_statement

get and set method can be combined if properly coded

Best Practice :- Try to combine insert and update SQL statements using merge SQL statement (Only in Oracle )

 

Some more common mistakes…………

a) SQL NULL and Java NULL implementation is not same.

b) Unnecessary usage of multiple distinct clases in each union query

c) UNION and UNION ALL confusions

d) Write multiple SQL even though they can be written into one single SQL

 

 

 

5 different ways to find TOP N queries

~ sqlandplsql ~ Leave a comment

5 different ways to find TOP N queries

Let us write five different queries to list the top 4 highest paid employees from emp table.

1) Using ROWNUM
2) Using ROW_NUMBER()
3) Using RANK()
4) Using DENSE_RANK()
5) Using MINUS

rownum1

1) Using ROWNUM

select * from (select empno,salary,rownum as rk from emp order by salary desc) where rk < 5;

rownum_topN

2) Using ROW_NUMBER()

select * from (select empno,salary,row_number() over(order by salary desc) as rk from emp) where rk < 5;

rownumber_topN

3) Using RANK()

select * from (select empno,salary,rank() over(order by salary desc) as rk from emp) where rk < 5;

rank_topN

4) Using DENSE_RANK()

select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk < 5;

denserank_topN

Note that dense_rank will return multiple records if there is a tie.

5) Using MINUS

select empno,salary from emp
minus
select empno,salary from (select empno,salary,rownum as rk from emp order by salary desc) where rk > 4;

minus_topN

You can also read – Nth highest salary 

Rownum and order by in oracle

~ sqlandplsql ~ Leave a comment

ROWNUM

ROWNUM is a psuedocolumn to return the row number of the row in a table. The order will be based on how oracle selects a row from table.

rownum1

Examples with rownum

rownum2

rownum3

Note that rownum will be decided by Oracle itself ( Oracle will assign a number based on how it retrieves from storage/memory)

Order by

Order by is a sql clause which used to order a selected column

Example :-

orderby1

Order by and Rownum together

Why we should use rownum and Order by clause together ? Many experienced developers ( Mainly java developers ) think they can write rownum and order by in same where clause to get the top N query.

For example – To find lowest salary employee developers write like below

select * from emp where rownum = 1 order by salary asc ; —  wrong query

see the result

rownum_orderby_wrong.PNG

Result is wrong.  It should be salary 7500 ( Empno – 14 )

Why ? Oracle applies the ROWNUM first and then applies the order by clause.

See the correct query below

rownum_orderby_correct

So always apply the order by and in next level apply the rownum.

To find a lowest salary employee :-

select * from emp where rownum = 1 order by salary asc ; —  wrong query

select * from (select * from emp order by salary asc) where rownum = 1 — correct query 

Few more tips about rownum

  1. Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
  2. Do not write ROWNUM <= 1 instead write ROWNUM = 1
  3. In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
  4. Do not write ROWNUM > 1, which will no yield any results
  5. ROWNUM and ROW_NUMBER() is not same.

Please comment.

 

 

ROW_NUMBER oracle

~ sqlandplsql ~ 2 Comments

ROW_NUMBER is an example of analytical function. It returns a unique number for each record.

Consider below emp table data

EMPNO EMPNAME SALARY DEPTNO
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

ROW_NUMBER()

Select empno, deptno, salary, row_number() over(order by salary) from emp;

ROW_NUMBER assigns a unique number by ordering by salary ascending by default

ROW_NUMBER simple example
ROW_NUMBER simple example

You can use row_number to find top N records

Below query returns first 3rd to 5th records

select * from
(
select empno, deptno, salary, row_number() over(order by salary) as rn from emp
) where rn between 3 and 5
;

ROW_NUMBER() with partition by clause

select empno, deptno, salary,
row_number() over(partition by deptno order by salary)
from emp;

Returns unique number within the window group, ie deptno. Each department it will find the employees based on their salary.

Row_number_partitionby.PNG
ROW_NUMBER with partition by clause

ROW_NUMBER() with NULLS LAST

This ignores the records having salary as null and give least priority for ranking

Select empno, deptno, salary,
row_number() over(order by salary nulls last)
from emp;

ROW_NUMBER with NULLS LAST
ROW_NUMBER with NULLS LAST