Category Archives: SQL

PL/SQL – sequences

Create and use sequence:

CACHE 100; -- how many next values should be prepared and ready to use

select hr.myseq.nextval from dual; -- selects next unique value
select hr.myseq.currval from dual; -- selects last produced value, but only in current session!

Sequence can be altered, but one property cannot be changed in alter command – it is starting value.

PL/SQL – views

Create, use, delete view:

create or replace force view hr.euro_countries -- force: create even when table does not exist
select * from hr.countries
where region_id = 1
with check option; -- do not allow DML for rows which are not visible in this view
--with read only -- do not allow any DML

select * from hr.euro_countries;

drop view hr.euro_countries;

PLSQL – constraints

There are two ways to create constraints:
a) when creating a table – inline with the column,
b) after creating a table – more flexible.

create table hr.people
id NUMBER(10) constraint people_id_pk PRIMARY KEY constraint people_id_ck CHECK (id >= 0),
first_name VARCHAR2(30 CHAR) constraint people_fn_nn NOT NULL,
last_name VARCHAR2(30 BYTE) constraint people_ln_ck CHECK (last_name is not null),
local_hello NVARCHAR2(50) constraint people_lh_uq UNIQUE, 
height NUMBER(3,2) constraint people_height_ck CHECK (height >= 1.5) DISABLE,
best_skill NUMBER(1) constraint people_bs_fk references skills(id) on delete cascade DEFERRABLE INITIALLY DEFERRED,
knowledge_level NUMBER(1) constraint people_kl_fk references technology(id) on delete set null,
hobby NUMBER(1) constraint people_hobby_fk references hobby(id) on delete set null

alter table hr.people
enable constraint people_height_ck;

alter table hr.people
add constraint people_fn_ck check (LENGTH(first_name) > 2);

alter table hr.people
drop constraint people_fn_ck;

alter table hr.people
modify local_hello constraint people_lh_nn NOT NULL;

When Primary Key or Unique constraint is created, index is build automatically.
Primary Key is combination of NOT NULL and UNIQUE constraints.

Not null constraint is translated to CHECK(column_name IS NOT NULL).

When Foreign Key is created index is not created, you should do it on your own as it is good practice.

Deferrable means that constraint is not checked directly in time of insert or update. It is checked at the end of the transaction. It can solve chicken and egg like problems.

On delete set null means that when parent of this value (foreign key relationship) is deleted value will be set to null.
On delete cascade means that row will be deleted when parent value is deleted.


I’ve been reading about differences between NVARCHAR2 and VARCHAR2 and generally people say that NVARCHAR2 should be used when someone wants to have multilingual data in a column. I played a little bit with it and I had some doubts still.

Doubt no. 1
Imagine I crated simple table to play with:

create table hr.test1

Then I inserted a few rows with texts using different languages (german, japanese, russian):

insert into hr.test1 values ('こんに');
insert into hr.test1 values ('Здр');
insert into hr.test1 values ('aßö');

select * from hr.test1;

And I see that I have correct multilingual result without using NVARCHAR2:

So when I really should use NVARCHAR2, if this example works well with VARCHAR2?
Answer: There is no difference whether you use NVARCHAR2 or VARCHAR2 when default character set of the database is set to multi-byte character set (i.e. UTF-8). The only one difference can be the length in bytes for the same strings. Continue reading, please…

Doubt no. 2:
Imagine I’ve crated simple table to play with:

create table hr.test2
col1 VARCHAR2(3 CHAR),
col2 NVARCHAR2(3)
insert into hr.test2 values ('asf', 'asf');
insert into hr.test2 values ('Здр', 'Здр');
insert into hr.test2 values ('こんに', 'こんに');
select LENGTHB(col1), LENGTHB(col2) from hr.test2;

Result is:
3 6
6 6
9 6

Why there are such differences in byte-length? Why for japanese chars NVARCHAR2 took less bytes, but for english chars VARCHAR2 took less?
It is due to the fact that default character set is ‘AL32UTF8’ (multi-byte one) and NVARCHAR2 uses fixed-length bytes for every character. In this case always any character stored in NVARCHAR2 takes 2 bytes and it do not depend on language used. This is opposite to VARCHAR2.

To check your character set execute:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

PL/SQL – create and alter table

Creating tables

--Create simple table with heap organization:
create table hr.people
id NUMBER(10) NOT NULL, -- any integer up to 10 places
first_name VARCHAR2(30 CHAR) NOT NULL, -- text up to 30 characters (single non english character can take more than one byte)
last_name VARCHAR2(30 BYTE) NOT NULL,  -- text up to 30 bytes
local_hello NVARCHAR2(50), -- unicode text, multilingual data (it means you can mix in this column different languages - including english like and non english exotic asian languages)
height NUMBER(3,2), -- any number up to 3 digits where up to two are on the right of comma and 3-2 are on the left
skills_level NUMBER(1) DEFAULT 3, -- default must be constant value, cannot base on function
knowledge_level NUMBER(1) DEFAULT 3,
final_level as (skills_level * knowledge_level) -- function-based virtual column

Notice that when you can insert into height column value 1.7554432 (it will be rounded), but you cannot insert 10.7 (it will fail).

--Create table copy (DDL + DML):
create table hr.people2 as select * from hr.people;

--Create table copy (DDL only):
create table hr.people3 as select * from hr.people where 1=2;

Altering tables

alter table hr.people add profession nvarchar2(20);
alter table hr.peple modify height number(4, 3) NOT NULL;
alter table hr.people rename column height to height_cm;
alter table hr.people drop column final_level; -- can be time consuming as every row must be rebuilt
alter table hr.people set unused column first_name; -- do not removes column, but it won't be available in queries, views, etc. Use when dropping column can take long time.
alter table hr.people drop unused columns; -- it should follow a few 'set unused' commands when it is allowed to consume some resources to clean up not needed columns.
alter table hr.people read only; -- table will reject any modifications, alter table privilege is needed for this
alter table hr.people read write; -- will turn off read only mode
drop table hr.people; -- it will delete the table (also if it is in read only mode)

For more information about differences between NVARCHAR2 and VARCHAR2 and encoding go to:

PL/SQL – transactions

Transactions in Oracle are controlled using a few key words:
BEGIN – starts
SAVEPOINT name – creates a marker which can be used to roll back part of transaction only
COMMIT – accepts
ROLLBACK – rejects everything
ROLLBACK TO SAVEPOINT name – rejects changes up to savepoint

Transaction also can be closed (commited or rejected) when DDL or DCL command is executed (commit is executed), session is killed, db crash ocurred, … (rollbacks).

You can decide whether your tool (SQLPLUS, SQLDeveloper) commits transaction for you. By default you need to commit on your own. To change this use SET AUTOCOMMIT ON.

--simple transaction commited
insert into hr.regions values (100, 'Region A');
insert into hr.regions values (101, 'Region B');

--commit or rollback simple transaction
regions_count number := 0;
insert into hr.regions values (103, 'Region C');
insert into hr.regions values (104, 'Region D');
select count(*) into regions_count from hr.regions;
    if regions_count > 15 
        then rollback; 
        else commit;
    end if;

--rolling back to savepoint
regions_count number := 0;
insert into hr.regions values (103, 'Region C');
savepoint after_a;
insert into hr.regions values (104, 'Region D');
select count(*) into regions_count from hr.regions;
    if regions_count > 5 
        then rollback to savepoint after_a; --will rollback only one insert
    end if;
commit; -- the second insert will be commited

PL/SQL – insert, truncate, merge

Insert examples

insert into hr.regions values (7, 'Space'); -- insert single row using columns positioning
insert into hr.regions (region_id, region_name) values (8, 'Milky Way'); -- insert single row explicitly selecting columns
insert into hr.regions2 select * from regions; -- insert all rows from one table to another

There exists command to insert rows to many tables at once, search for ‘INSERT ALL‘.
Insert command creates rows in a table, but there are far more efficient ways to insert data: SQLLoader, DataPump.

To remove all rows from table delete and truncate commands can be used. Be carefull, truncate is extremely fast, but cannot be rolled back. It is not DML statement, it clears ‘high water’ mark of the table.

Merge command is used for example when you have two tables and you want to update one table using data from the second one. Merge can insert, update and delete rows in the same time.

merge into employees e using new_employees n
on (e.employee_id = n.employee_id)
when matched then
update set e.salary=n.salary
when not matched then
insert (employee_id,last_name,salary)
values (n.employee_id,n.last_name,n.salary);