ROW_NUMBER oracle

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
Advertisements

Difference between rank and dense_rank Oracle

What is the difference between RANK and DENSE_RANK functions in Oracle ?

In simple words both does the same except RANK skips if there is a tie and DENSE_RANK does not. So remember to use DENSE_RANK to use in Nth largest queries.

Detailed explanation of RANK and DESNSE_RANK given in another posts

RANK Function

DENSE_RANK Function

Example :-

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

select empno,salary,
rank() over(order by salary asc) “rank”,
dense_rank() over(order by salary asc) “dense_rank”
from emp;

See the result

Difference between RANK and DENSE_RANK
Difference between RANK and DENSE_RANK

RANK function skips number 8 while DENSE_RANK does not.

sys_context Function Oracle and its usage

SYS_CONTEXT function returns set of defined values within the database(based out of parameters). This can be called from both SQL and PLSQL.

Example:-

SELECT SYS_CONTEXT (‘USERENV’, ‘NLS_DATE_FORMAT’)  FROM DUAL;

return NLS_DATE_FORMAT as DD-MON-RR ( In my session ). USERENV is the parameter

Another example below

SYS_CONTEXT
SYS_CONTEXT

 

Below few list of all SYS_CONTEXT parameters (most important )

Parameter Description
CURRENT_SQL First 4000 bytes of the current SQL.
CURRENT_SQL_LENGTH Current SQL Length
DB_DOMAIN DB Domain
DB_NAME DB Name
HOST Host Name
INSTANCE Instance ID
INSTANCE_NAME Instance Name
IP_ADDRESS IP Address
ISDBA Yes if logged as DBA
LANG Language Country
LANGUAGE Language Detail
MODULE Tool Name
NETWORK_PROTOCOL Eg:- TCP
NLS_CALENDAR Eg:- Gregorian
NLS_CURRENCY Currency
NLS_DATE_FORMAT Date format
NLS_DATE_LANGUAGE Date Language
NLS_SORT Eg:- Binary
NLS_TERRITORY NLS country
OS_USER OS User Name
SERVER_HOST Server Host
SERVICE_NAME Service Name
SESSION_USER Session User Name
SESSION_USERID Session User ID
SESSIONID Session Id
SID Process ID
TERMINAL Client Machine Name

For complete list please read Oracle Documentation

Usage

If I want to create audit table with information like userid, machine name, ip address, SYS_CONTEXT will help. See below sample

Create table emp_audit
(
emp_id number,
create_date timestamp default systimestamp,
created_by varchar2(50) default sys_context (‘userenv’, ‘session_user’),
ipaddress varchar2(50) default sys_context (‘userenv’,’IP_ADDRESS’),
module varchar2(50) default sys_context (‘userenv’,’MODULE’)
);

insert into emp_audit(emp_id) values (1000);

select * from emp_audit;

SYS_CONTEXT Example
SYS_CONTEXT Example

You can see SYS_CONTEXT used in another post Simple Debugging Tool

TO_DATE function Oracle

TO_DATE function in Oracle is the most useful as well as ‘annoying’ function.

TO_DATE function converts string into date data types.

Eg :-

select to_date(‘2016/11/13′,’yyyy/mm/dd’) from dual;

returns  2016/11/13

Note that Oracle converts string ‘2016/11/13’into Date ‘2016/11/13’. Now this value can be inserted into a table having a column date data type.

Note :- Before doing any testing on date columns make sure the default date format settings

For example in my sqldeveloper tool, go to Tools–> Preferences –> Database –> NLS

image7
SQLDEVELOPER date format

Below sql should have returned 2016/11/13, but shows 13-NOV-16 which is in sqldeveloper nls date format. This is the most common mistake people make.

 

image8

Alternatively you can use below sql also to change the date format.

alter session set nls_date_format = ‘dd-mon-yyyy’;

The above SQL can be used from SQLPLUS also.

If you do not want to change the nls settings, you can use TO_CHAR function also in required format, see below example.

select to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’yyyy/mm/dd’) from dual;

(TO_CHAR and TO_DATE)

image9

Below are the most used formats with TO_DATE

Format Description Example Value
AD AD or BC to_char(to_date(‘2016/11/13 AD’,’yyyy/mm/dd AD’),’AD’) AD
BC AD or BC to_char(to_date(‘2016/11/13 BC’,’yyyy/mm/dd AD’),’AD’) BC
D Day of week (1 7) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’D’) 1
DAY Name of day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DAY’) SUNDAY
DD Day of month (1 31) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DD’) 13
DDD Day of year (1 366) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DDD’) 318
DY Abbr name of day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DY’) SUN
HH Hour of day (1 12) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’HH’) ‘01’
HH12 Hour of day (1 12) to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH12′) ‘04’
HH24 Hour of day (0 23) to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH24′) 16
IW Week of year to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’IW’) 45
J Julian day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’J’) 2457706
MI Minute (0 59) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MI’) 12
MM Month (01 etc) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MM’) 11
MON Abbr month to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MON’) NOV
MONTH Name of month to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MONTH’) NOVEMBER
AM AM or PM to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) AM
PM AM or PM to_char(to_date(‘2016/11/13 01:12:35 PM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) PM
Q Quarter of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’Q’) 4
RRRR Round year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’RRRR’) 2016
SS Second (0 59) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’SS’) 35
WW Week of year (1 53) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’WW’) 46
W Week of month (1 5) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’W’) 2
YYYY Last 4 digit(s) of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YYYY’) 2016
YY Last 2 digit(s) of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YY’) ‘16’

Always check below before you start using TO_DATE function

  1. NLS_DATE_FORMAT ( SELECT sys_context (‘userenv’,’NLS_DATE_FORMAT’) FROM DUAL )

or

2. use TO_CHAR for formating

Also read TO_CHAR Function

10 must-know Oracle Interview questions

Interviewing a candidate for a particular job is a tedious task, there are many factors which effect your selection. Managers has to decide within short time span with minimum questions.So if we know the most possible or probable questions before interview we can be prepared. Here 10 must-know Interview questions ( there is no ranking )

1. What is the difference between Procedure and Function ?

Answer:-

1) Function must return a value and procedure need not.

2) Function can be used in SQL with some restrictions. Procedure cannot be called directly from SQL.

2. What is the difference between DELETE and TRUNCATE ?

Answer:-

1) DELETE is a DML command and TRUNCATE is a DDL command.

2) TRUNCATE re-set the memory blocks after execution and much faster than DELETE in most of the circumstances.

3. What is the difference between PRIMARY KEY and UNIQUE KEY constraints ?

Answer:-

1) UNIQUE KEY columns can have null values but PRIMARY KEY column cannot accept null values.

2) A table can have only one PRIMARY KEY column (composite primary key is ok) but many UNIQUE KEY columns allowed.

4. What is Cartesian Product ?

Answer:-

1) If two or more tables are joining without join condition will result into Cartesian products.

2) If table A has 2 rows and table B has 4 rows then Cartesian product between A and B will return 8 rows ( 2 multiply by 4 )

5. What is a Transaction ?

Answer:-

1) Transaction is a logical unit of work which will end in a consistent status

2) In Oracle we can control transactions using Save points, Commit, rollback etc

6. What is a Package, why do we go for packages ?

Answer:-

1) Package is a collection of one more program units having Procedure, Function etc

2) Depends on business logic, all related program units can be tied into a single package for better maintenance, readability etc

7. What is triggers, where we use triggers ?

Answer:-

1) Procedural code which automatically executes based on an event like insert,update, delete etc

2) Auditing, Logging etc

8. How to do sql performance tuning ?

Answer:-

1) Basic sql performance starts with EXPLAIN PLAN

2) Explain cost, index, access methods, plan etc

9. What is the most challenging/difficult feature in Oracle ?

Answer:-

1) This is the most tricky question, answer based on your experience.

2) Do not answer with a trivial problem, try something advanced feature

10. What is a materialized view ?

Answer:-

1) A materialized view, also called as snapshot,which contains the results of a query.

2) Generally used to create summary tables based on aggregations of a table data.

Please note that I have given the short description of the answers. One question answer may trigger another question. So be prepare the Oracle fundamentals deeply before the interview.

Also read Oracle Interview Questions and Answers

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.