Rownum and order by in oracle

ROWNUM or ORDER BY which comes first ?

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.
Advertisements

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.

5 different ways to test Oracle Ref Cursor results

In the previous posts we have covered Ref Cursors and Ref Cursor examples.
Now we will examine how Ref Cursor examples are tested.

For all the examples I have used the package we have created in Ref Cursor Example post

create or replace package pkg_refcur 
is
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);
………………………………………….
………………………………………….
End pkg_refcur;
/

create or replace package body pkg_refcur
is
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select e.empno,e.empname,d.deptno,d.deptname from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

………………………………………….
………………………………………….
End pkg_refcur;

1. Using sqlplus 

— Declare a variable for refcursor
SQL> variable v_refcur refcursor;

— Execute the package
SQL> exec pkg_refcur.p_get_employees(2,:v_refcur);

PL/SQL procedure successfully completed.

— Print the refcursor variable
SQL> print v_refcur;

EMPNO EMPNAME                  SALARY     DEPTNO    MANAGER
—————————————————————–
16 Henry                      8500          2         11
17 Robert                     9500          2         16
18 Paul                       7700          2         17

Please note that v_refcur is a bind variable here.

2. Using %rowtype 

This will be useful only when the ref cursor is based on a single table

SQL> set serveroutput on;
SQL> declare
rec_emp  emp%rowtype;
v_refcur sys_refcursor;
Begin
pkg_refcur.p_get_employees(2,v_refcur);
loop
fetch v_refcur into rec_emp;
exit when v_refcur%notfound;
dbms_output.put_line(‘Empno   –>’ || rec_emp.empno);
dbms_output.put_line(‘Empname –>’ || rec_emp.empname);
dbms_output.put_line(‘Salary   –>’ || rec_emp.salary);
dbms_output.put_line(‘deptno –>’ || rec_emp.DEPTNO);
end loop;
End;
/
Empno   –>16
Empname –>Henry
Salary   –>8500
deptno –>2
Empno   –>17
Empname –>Robert
Salary   –>9500
deptno –>2
Empno   –>18
Empname –>Paul
Salary   –>7700
deptno –>2

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose

3. Using PL/SQL table of type and record 

Procedure p_get_dept_emp cannot be tested with using (2). So declare a record and type on it.

SQL> Declare
2  Type rec_emp_dept is record
3  (
4  empno    emp.empno%type,
5  empname  emp.empname%type,
6  deptno   dept.deptno%type,
7  deptname dept.deptname%type
8  );
9  rec      rec_emp_dept;
10  v_refcur sys_refcursor;
11  Begin
12  pkg_refcur.p_get_dept_emp(2,v_refcur);
13   loop
14       fetch v_refcur into rec;
15       exit when v_refcur%notfound;
16       dbms_output.put_line(‘Empno    –>’ || rec.empno);
17       dbms_output.put_line(‘Empname  –>’ || rec.empname);
18       dbms_output.put_line(‘Deptno   –>’ || rec.deptno);
19       dbms_output.put_line(‘Deptname –>’ || rec.deptname);
20   end loop;
21  End;
22  /
Empno    –>16
Empname  –>Henry
Deptno   –>2
Deptname –>RESEARCH
Empno    –>17
Empname  –>Robert
Deptno   –>2
Deptname –>RESEARCH
Empno    –>18
Empname  –>Paul
Deptno   –>2
Deptname –>RESEARCH

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose.
This is not ideal for a Ref Cursor returning many columns.

4. Using a wrapper function to call 

This is a pretty much easy method. But may not work if you have a procedure having DML and ref cursor return

SQL> create or replace function f_refcur return sys_refcursor
2  is
3  v_refcur sys_refcursor;
4  Begin
5    pkg_refcur.p_get_dept_emp(2,v_refcur);
6    return v_refcur;
7  End;
8  /

Function created.

SQL> select f_refcur from dual;

F_REFCUR
——————–
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

EMPNO EMPNAME                  DEPTNO DEPTNAME
———- ——————– ———- ——————–
16 Henry                         2 RESEARCH
17 Robert                        2 RESEARCH
18 Paul                          2 RESEARCH

5. Using Developer tools like TOAD, Sql Developer etc

Normally professional developers use any of the development tools like TOAD or SQL Developer etc.

a) Using TOAD

Step 1

Connect to TOAD

Open Schema Browser

Click “Package” tab

Locate the package and choose “Execute Procedure” from right click menu

Test Ref Cursor TOAD Step 1
Test Ref Cursor TOAD Step 1

Step 2

Click right most icon of the opened window as shown below

Test ref cursor from TOAD step 2
Test ref cursor from TOAD step 2

Step 3

Choose the last radio button “Load into grid from memory (strong and weak)”

Test ref cursor from TOAD step 3
Test ref cursor from TOAD step 3

Click OK

Click OK

Now you can see the ref cursor results in another window as shown below

Test ref cursor from TOAD Step 4
Test ref cursor from TOAD Step 4

In similar manner you can test any ref cursor within package/procedure/function

b) SQL Developer

SQL Developer is a Oracle Corporation product.

Step 1

Connect to SQL Developer ( I Have used version 3.0.4)

Click “Package” node and locate the package

Test red cursor from SQL Developer Step 1
Test red cursor from SQL Developer Step 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 2

Click Run button or Ctrl-F10

Test red cursor from SQL Developer Step 2
Test red cursor from SQL Developer Step 2

Click OK

Step 3

Test red cursor from SQL Developer Step 3
Test red cursor from SQL Developer Step 3

 

 

 

 

 

 

 

 

 

 

(In the message window you have to choose “Output variables”)

Conclusion :- Most of the ref cursors can be tested with either of the method. Also you can use the base query directly in SQL window or SQL plus to test the ref cursors.

Ref Cursors Examples

REF CURSOR is a data type in the Oracle. To read more about REF CURSOR

I have created a package to explain few different ref cursor examples.
It covers most of the typical and simple examples.

create or replace package pkg_refcur 
is
— Declaring a Strong Ref Cursor Type
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

— Declaring a Weak Ref Cursor Type
TYPE ref_weak_typ IS REF CURSOR;

–Package Signatures
procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);

procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor);

procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor);

procedure p_dynamic(pi_tablename  in   integer,
po_results    out  sys_refcursor);

End pkg_refcur;
/

create or replace package body pkg_refcur
is
— 1)
— Using Strong ref cursor to return employees in a department
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

— 2)
— Using weak ref cursor to return employees and department details
procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

— 3)
— Using system ref cursor to return employees and department details
procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_deptemp;

— 4)
— Associating multiple query
procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor)
is
Begin
if upper(pi_type) = ‘DEPT’ then
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘EMP’ then
Open po_results for
select * from dept e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘BOTH’ then
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
end if;
Exception
when others then
raise;
End p_getdeptemp;

— 5)
— Associating multiple query with dynamic query
procedure p_dynamic(pi_tablename  in  integer,
po_results    out sys_refcursor)
is
Begin
if upper(pi_tablename) in (‘EMP’,’DEPT’) then — for security
Open po_results for
‘select * from ‘ || pi_tablename ;
end if;
Exception
when others then
raise;
End p_dynamic;

End pkg_refcur;
/

Conclusion :- Ref Cursors are very ideal for interchanging data between two different application involving Oracle as a database, like Java and Oracle, Dot net and oracle, Oracle forms and Oracle etc.

Ref Cursors Oracle

The REF CURSOR is a data type in the Oracle. REF CURSOR also referred as Cursor Variables.Cursor variables are like pointers to result sets. Cursor can be attached to only one query while REF CURSOR can be used to associate multiple queries at run time. 

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1  empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;

REF CURSOR can be categorized into three

1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;

Here empcurtyp is a Strong Ref Cursor

2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR;
…..
End;

Here empcurtyp is a Weak Ref Cursor

3. System Ref Cursor
This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare
empcurtyp SYS_REFCURSOR;
…..
End;

Advantages 
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables ( Read more about BIND VARIABLES )

Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values

In general Ref Cursors are only be used when static cursor cannot do the work.