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

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.

 

Advertisements

5 common mistakes Java Developers make when writing SQL

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

 

 

 

Rownum and order by in oracle

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.

 

 

5 different ways to load flat file into Oracle table

We need to move data from flat file to Oracle table frequently. For example sales department sends daily sale data in excel sheet to IT department, how this data feed into Oracle database (to tables) ? we will examine different methods.

1.  SQLLOADER 

sqlloader is an Oracle utility to load data from external files to table. This is one of the most used utility in Oracle database.

EMP
——- ——– ————
EMPNO      NOT NULL NUMBER(38)
EMPNAME                    VARCHAR2(20)
SALARY                          NUMBER
DEPTNO                         NUMBER(38)

First create a control file with details of flat file and table column prameters

emp.ctl

LOAD DATA
INFILE ‘C:\Temp\emp.csv’
BADFILE ‘C:\Temp\emp.bad’
DISCARDFILE ‘C:\Temp\emp.dsc’
INSERT INTO TABLE emp
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ TRAILING NULLCOLS
(empno,empname,salary,deptno)

Flat file as emp.csv

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

run below command in command prompt (Generally ORACLE_HOME\bin)

sqlldr userid=username/password@connect_string control=c:\temp\emp.ctl log=c:\temp\emp.log

Bad data will log into bad file. Note that sqlloader has many many options in it, many professionals use it to automate the loading process.

2. EXTERNAL TABLES

External tables is an advanced feature of Oracle SQLLOADER. You can write sql on top of the External Tables.

Step 1 :- Create directory ( with the help of DBA may be) and grant permissions to the user

sql>create directory load_dir as ‘C:\Temp’;

sql>grant read,write on directory load_dir to user;

Step 2 :- Create flat file in directory

emp.csv

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

Step 3 :- Create EXTERNAL TABLE

Drop table EMP if it exists and then create it using below sql

CREATE TABLE emp
(EMPNO integer,
EMPNAME VARCHAR2(20),
SALARY integer,
DEPTNO integer)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
fields terminated by ‘,’
)
LOCATION (’emp.csv’)
);

Step 4 :- Run select query ( This will select data from External Table which is associated with a flat file )

Select * from emp;

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

3. UTL

UTL is another Oracle utility to load data from flat file to Oracle and vice versa.

Step1 :- Check the utl_directory using below sql (Ask DBA if you do not have priviliges)

image5

Here “C:\TEMP”is the utl directory. So we can use any files in the directory for utl operations. I have an emp.dat file in it;

emp.dat

10,Bill
20,John
30,Bruce

USe below plsql block to read all lines from emp.dat

DECLARE
filehand UTL_FILE.FILE_TYPE;
line VARCHAR2(4000);
v_empno varchar2(10);
v_empname varchar2(10);
BEGIN
filehand := UTL_FILE.FOPEN(‘C:\TEMP’, ’emp.dat’, ‘R’);
LOOP
UTL_FILE.GET_LINE(filehand, line);
if line is not null then
v_empno := substr(line,1,instr(line,’,’)-1);
v_empname := substr(line,instr(line,’,’)+1,length(line));
dbms_output.put_line(v_empno);
dbms_output.put_line(v_empname);
insert into emp(empno,empname) values(v_empno,v_empname);
commit;
else
exit;
end if;
END LOOP;
commit;
utl_file.fclose(filehand);
EXCEPTION
WHEN others THEN
null;
END;
/

3 rows inserted into emp table.

4. Using Tools (SQLDEVELOPER)

EMP
——- ——– ————
EMPNO           NOT NULL    NUMBER(38)
EMPNAME                             VARCHAR2(20)

emp.csv

10,Bill
11,Solomon
12,Susan
13,Wendy
14,Benjamin
15,Tom

Step 1 – Click on Tables –> EMP

Step 2 – Click on Actions –> Import Data –> Choose csv file

image1

Step 3 – Click next  –> choose column details –> next

image2

Data successfully loaded into EMP table

image3

similarly TOAD also have

5. INSERT script

Data can be loaded using insert script. You can use excel to create dynamic insert scripts

image4

Copy insert script from C column and run

insert into EMP(empno,empname) values (11,’Solomon’);
insert into EMP(empno,empname) values (12,’Susan’);
insert into EMP(empno,empname) values (13,’Wendy’);
insert into EMP(empno,empname) values (14,’Benjamin’);
insert into EMP(empno,empname) values (14,’Tom’);

Generally for large data sets, sqlloader and external tables are preferred methods.

( Note :-  I have used very basic examples only to explain the basics of flat file loading, it may have some logical errors )

PLJSON – JSON extension for PL/SQL

PLJSON

PL/JSON is a generic JSON object written in PL/SQL.

JSON

JSON (JavaScript Object Notation) ( www.json.org ) is a lightweight data-interchange format. It is a text based lightweight xml object.

Example :-

1)

{“Oracle Corporation”:”oracle”}

2)

Consider emp table with 2 columns and 3 records

Empno    Empname
—————————–
10            clerk
11            bill
12            chang

Above records can be written in JSON format as

{

“emp”:[

                 {“empno”:”10″,”empname”:”clerk”},

                 {“empno”:”11″,”empname”:”bill”},

                 {“empno”:”12″,”empname”:”chang”} ]

}

In PL/SQL we can convert table data into JSON using PL/JSON generic JSON object. It is an excellent third party library available in http://sourceforge.net/projects/pljson/

Facts about PL/JSON

1. Written by – Jonas Krogsboell ( inspired by code from Lewis R Cunningham )

2. License – Free license under MIT

3. Download – https://github.com/pljson/pljson

4. Installation – Download zip file and and extract. Run install.sql ( You should have required privileges to run )

PLJSON is one of the excellent library written in PL/SQL. I have seen many people using it and excellent reviews.

It has lot of advantages

This sparkled me to write an standalone PL/SQL API to convert Oracle SQL to JSON format without creating any dependent object or types. My intention was to develop an simple API. This API takes sql as arguments and converts to JSON and clob format.The client program call the API and manipulate the JSON and process it. It is not using any ref cursor object here.

REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the employees in a specific department:

I assume this API is not for very large conversion projects. But relatively small systems. API Code given below

——————————————————————————————————————————————–

create or replace function sql_json_clob(p_sql varchar2) return clob
is– Author : Manoj
— Date : 3-Mar-2016
— Purpose: Converts sql to json format

v_json_clob clob := empty_clob();
v_cursor integer := dbms_sql.open_cursor;
v_coldesc dbms_sql.desc_tab;
v_seperator char(1);
v_comma char(1);
v_column varchar2(500);
v_columnvalue varchar2(4000);
v_columnname varchar2(4000);
n_colcnt pls_integer := 0;
n_cnt pls_integer := 0;
n_status integer;
v_sql varchar2(32767);
v_rowstring varchar2(32767);
v_title varchar2(50) := ‘dept’;
Type typjson is table of varchar2(32767) index by pls_integer;
n pls_integer := 0;
tt typjson;
v_mystring varchar2(32767);
begin
v_sql := p_sql;
— to find the column count
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
for i in 1..255 loop
begin
dbms_sql.define_column(v_cursor,i,v_column,2000);
n_colcnt := i;
exception
when others then
if ( sqlcode = -1007) then — variable not in select
exit;
else
raise;
end if;
end;
end loop;

n_status := dbms_sql.execute(v_cursor);
dbms_sql.describe_columns(v_cursor,n_colcnt,v_coldesc);
loop
exit when ( dbms_sql.fetch_rows(v_cursor) <= 0);
v_rowstring := null;
v_seperator := null;
for i in 1..n_colcnt loop
dbms_sql.column_value ( v_cursor, i, v_columnvalue);
v_columnname := v_coldesc(i).col_name;
v_rowstring := v_rowstring || v_seperator || ‘”‘ || v_columnname || ‘”: “‘ || v_columnvalue || ‘”‘ ;
v_seperator := ‘,’;
end loop;
v_rowstring := ‘{‘ || v_rowstring || ‘}’;
–v_json_clob := v_json_clob || v_comma || v_rowstring;
v_comma := ‘,’;
n := n + 1;
tt(n) := v_rowstring;
end loop;
dbms_sql.close_cursor(v_cursor);

— 382828
v_comma := null;
for k in tt.first..tt.last
loop
–dbms_output.put_line(tt(k));
v_mystring := v_mystring || v_comma || tt(k) ;
v_comma := ‘,’;
if length(v_mystring) > 28000 then
–dbms_output.put_line(length(v_mystring));
v_json_clob := v_json_clob || v_mystring ;
v_mystring := null;
end if;
end loop;
if v_mystring is not null then
v_json_clob := v_json_clob || v_mystring;
end if;

v_json_clob := ‘{“Success”: “true”,”‘ || v_title || ‘”:[‘ || v_json_clob || ‘]}’;
return v_json_clob;

exception
when others then
dbms_output.put_line(sqlerrm);
v_json_clob := ‘{“Success”: “true”,”‘ || v_title || ‘”:[]}’;
if dbms_sql.is_open(v_cursor) then
dbms_sql.close_cursor(v_cursor);
end if;
return v_json_clob;
end sql_json_clob;
/

Usage:
SQL> select sql_json_clob(‘select id,id as name from table where rownum < 10000’) from dual;

——————————————————————————————————————————————–

It might need liitlebit testing and modification.

I appreciate your comments and feedback.

 

High Water Mark Oracle

In simple term High water mark is a logical mark in the memory area to separate blocks having data and no data. As you know that the blocks represent the most granular form of data in memory.

When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward as data get saved into the database. After inserting some records High Water Mark will be moved to forward.

See below given logical and pictorial representation of high water mark when a table data insert/update/delete/truncate

High Water Mark Oracle
High Water Mark Oracle

By default High Water Mark starts at the first block for new tables. When table gets data via insert/update more blocks being used High Water Mark moves forward. When data get deleted, High Water Mark remain same even though blocks doesn’t have any data.

Delete has no impact on high water mark and Truncate reset the High Water Mark. This is the one difference between delete and truncate ( Read more difference between Delete and Truncate )

When full table scan takes place, Oracle scans all blocks below High Water Mark even though it has no data.