Concatenation is Oracle

Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages use || for the or operator. Example:

insert into mytable (id, name) values (12, 'Mr. ' || lastname);

The field lastname is concatenated to the string Mr.

Oracle Packages

A package is an encapsulated collection of related PL/SQL types, objects, procedures, and other program objects. Packages have a package specification and a package body. Package specification declares constants, variables, types, cursors, exceptions and procedures. Package definition defines cursors and procedures.

Packages provide the advantages of modularity, information hiding, and encapsulation.

Package commands To create a package

CREATE PACKAGE package_name

To create package body

Oracle NVL function

NVL function can be used to substitute a value when null in encountered. It has the following syntax:

NVL(string,replacement_string)

If string has a value, it will be displayed. If the string is null, the replacement string will be displayed.

select NVL(salary,'unpaid volunteer') from staff

This query prints staff salaries. Volunteers do not have a salary so the value for their salary is null. In the query, when the salary is null, replacement string 'unpaid volunteer' is printed.

Listing objects in Oracle

If you want to list tables in MySQL, you simply type the following command:

show tables

Oracle does not provide such a short cut. You have to type the following command:

select * from user_objects where object_type = 'TABLE'

The word TABLE must be uppercase. This would lisl all tables in the database. The same query could be modified slightly to list functions, procedures, sequences, view and more.

List functions

Oracle set operators: union, intersect, minus

The most commonly used set operators in Oracle are:

  • union
  • intersect
  • minus

Union Union combine results returned by two or queries into a single table. In a union, all tables must have matching columns. It shows all rows from the first query and all rows from the second query while removing duplicate entries. It has the syntax:

query1 union query2

For example, we have a query1

select name, salary from employee where gender = 'M' order by salary

and a query2

Oracle decode function

Decode function has the same functionality as an IF-THEN-ELSE statement. It is present in Oracle 9i and above. It has the syntax

decode(column/expression, value, substitute, default value)

For example,

decode(gender, 'M', 'Male', 'F', 'Female', gender) gndr

This is like saying the following in PL/SQL:

IF gender = 'M' THEN
  gndr := 'Male';
ELSIF gender = 'F' THEN
  gndr := 'Female';
ELSE
  gndr := gender;
END IF;

An SQL example,

Adding a leading dollar sign ($) in front of a number in Oracle

There are two ways of adding a leading dollar sign to numbers in Oracle. You can either define teh column with a number format which display number with a dollar sign or you can define it in you select statement as follows:

select name, to_char(salary, '$9,999,990,99') from salarytable

to_char is used to change string formatting. Here we add $ and a comma after every three digits left of the decimal. We also specified that there are two decimal places. Without the 0 in the middle, you would get $.00 instead of $0.00 when the salary is 0.

Limit clause in oracle

MySQL and Postgres support the limit clause which limits the number of entries returned when a query is executed:

select * from member limit 5

This query will return at most 5 entries. To do the same in Oracle, you need to use the rownum clause:

Pages