Collection of articles about Oracle db
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.
To change the first letter of the word to cap, use the inicap function. Use upper to covert to uppercase and lower to convert to lower case. Examples:
initcap('change case') -- Change Case
lower('Change Case') -- change case
upper('change case') -- CHANGE CASE
These functions work on Oracle 8i, 9i, 10g, and 11g.
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.
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:
select * from member where rownum < 6
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
select * from user_objects where object_type = 'TABLE'
List indices
select * from user_objects where object_type = 'INDEX'
List packages
select * from user_objects where object_type = 'PACKAGE'
List package bodies
select * from user_objects where object_type = 'PACKAGE BODY'
List procedures
select * from user_objects where object_type = 'PROCEDURE'
List sequences
select * from user_objects where object_type = 'SEQUENCE'
List synonyms
select * from user_objects where object_type = 'SYNONYM'
List triggers
select * from user_objects where object_type = 'TRIGGER'
List views
select * from user_objects where object_type = 'VIEW'
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
select * from user_objects where object_type = 'TABLE'
List views
select * from user_objects where object_type = 'VIEW'
List procedures
select * from user_objects where object_type = 'PROCEDURE'
List indices
select * from user_objects where object_type = 'INDEX'
List packages
select * from user_objects where object_type = 'PACKAGE'
List package bodies
select * from user_objects where object_type = 'PACKAGE BODY'
List sequences
select * from user_objects where object_type = 'SEQUENCE'
List synonyms
select * from user_objects where object_type = 'SYNONYM'
List triggers
select * from user_objects where object_type = 'TRIGGER'
Suppose you have two dates and you want to compares the years rather the exact date. Make sure you know the format of the dates. The use the following code:
to_char(to_date(somedate, 'yyyymmdd'), 'YYYY') = to_char(to_date(somedate, 'yyyymmdd'), 'YYYY')
Change yyyymmdd to your date format.
This example have been tested on Oracle 8i, 9i, 10g, and 11g.
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,
select name, decode(gender, 'M', 'Male', 'F', 'Female', gender) gndr
from employee;
Decode can have a maximum of 255 comma separated components. I you have so many components, then you might need to rethink the SQL you are writing.
extract function is used to extract year, month, or date from a date or timezone hour or timezone minute from an interval. Examples:
extract(DAY FROM DATE '2011-01-02') -- 02 extract(MONTH FROM DATE '2011-01-02') -- 01 extract(YEAR FROM DATE '2011-11-11') -- 2003
extract function was introduced in Oracle 9i and it is supported in Oracle 10g and 11g.
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.
select NVL(salary,monthly_invoice) from worker
A paid worker can be an employee or a contractor. An employee receives a monthly salary and a contractor invoices his hours of work at the end of the month. In this query, if the salary is not null, the salary is printed. If the salary is null, the monthly_invoice is printed.
select distinct id,
NVL((select distinct 'Y'
from table1
where x = y),
'N') active
where c = d
NVL function can also be used in a subquery as in the exmaple above
NVL2 function extends the funcitonality of NVL function. NVL function prints the value of the variable unless a null value is encountered; in which case a value is substituted for the null. In addition to this, NVL2 also allows you to substitute a value if the value of the variable is not null. Syntax:
NVL2(variable, not_null_substitute, null_substitute)
Example
select id, name, NVL2(phone, 'Provided', 'Not Provided') from staff_listing;
Here the NVL2 function prints Provided if a phone number is provided and Not Provided in the value is null.
NVL2 function maintains the same functionality in Oracle 8i, 9i, 10g, 11g, and it is expected to remain the same in future versions.
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
select name, salary
from employee
where gender = 'F'
order by salary
A union would be
select name, salary
from employee
where gender = 'M'
union
select name, salary
from employee
where gender = 'F'
order by salary
Query1 shows names and salaries of all male employees. Query2 shows names and salaries of all female employees. The union query shows the names and salaries of all male and female employees.
Note that I have removed the order by clause from query1. Queries inside a union cannot be ordered. However, the resulting rows from a union can be ordered.
Intersect
Intersect operator takes results from two queries and returns only the rows that appear in both results. Syntax
query1 intersect query2
For example, we have a query1
select name, salary
from employee
where salary > 100000
and a query2
select name, salary
from employee
where gender = 'F'
An intersect would be
select name, salary
from employee
where gender = 'M'
intersect
select name, salary
from employee
where gender = 'F'
order by salary
Query1 shows names and salaries of all employees making over 100k a year. Query2 shows names and salaries of all female employees. The intersect table shows names and salaries of all female employees making over 100k.
Minus
Minus operator takes results from two queries and returns only the rows that appear in the results of the first query but not the second. Syntax:
query1 minus query2
For example, we have a query1
select name, salary
from employee
where salary > 100000
and a query2
select name, salary
from employee
where gender = 'F'
An intersect would be
select name, salary
from employee
where gender = 'M'
minus
select name, salary
from employee
where gender = 'F'
order by salary
Query1 shows names and salaries of all employees making over 100k a year. Query2 shows names and salaries of all female employees. The minus table shows names and salaries of all male employees making over 100k.
The substr function extracts a substring from a string. Syntax:
substr( string, start position, length)
where the first position is 1 and the length of the substring is optional. Some examples
substr('substring', 2) -- ubstring
substr('substring', 1, 3) -- sub
substr('substring', -2, 2) -- ng
These examples have been tested on Oracle 8i, 9i, 10g, 11g, and it is highly likely that this syntax would not change in the future.
The following three operators have the identical meaning:
!=, ^=, <>
select name from worker where gender <> 'male';
Another way to test for inequality it to use "not in"
select name from worker where type not in ('manager','contractor');
All columns of your SELECT must be listed in the GROUP BY expression. Alternately you can use functions MIN() or MAX() which compress results.
This error occurs if a wrong value is passed when converting a number or string to date. To fix this problem, find out what date format the date is stored in e.g. yyyymmdd, dd-mm-yy, dd-mon-yy, etc. Then rewrite the to_date() statement with the correct format. For example
to_date('11-11-2011', 'DD-MM-YYYY')