Oracle Functions

Logical Functions

nvl(val1, value-if-val1-is-null)

In case of null values

decode(col, test_result_1, result_1
[, test_result_2, result_2
, test_result_n, result_n]
[, else_result] )

Full IF logic, max of 255

decode(sign(some_date-sysdate), 1, 'x', 0, 'y', -1, 'z')

Trick for ranges

decode(value,'x',0)*y

Trick: price is y only if value=x

substr(a_string, 1, decode(value, 'x', length(a_string), 0))

Trick: return string only if value='x'

bitand ( a, b)

Bitwise AND. Examples:
decode ( bitand(3, 2), 2, 'x', '0') --> x (011 && 010 --> 010)
decode ( bitand(4, 2), 2, 'x', '0') --> 0 (100 && 010 --> 000)
decode ( bitand(6, 2), 2, 'x', '0') --> x (110 && 010 --> 010)

coalesce(expr1, expr2, expr3, ...)

Returns the first non-null expression in the list

---

Character Functions

Data Types

VARCHAR2

variable-length string data, up to 32767 bytes in PL/SQL
but up to 2000 bytes in the database (!)

CHAR

Fixed-length string data, up to 32767 bytes in PL/SQL
but up to only 255 bytes in the database (!)
Pads with spaces

RAW(n)

variable-length string data, up to 32767 bytes in PL/SQL
but up to only 255 bytes in the database (!)
No character conversion

LONG(n)

n = 1..32760 in PL/SQL
but up to 2GB in the database.

LONG RAW(n)

variable-length string data, up to 32760 bytes in PL/SQL
but up to 2GB in the database (!)
No character conversion

Conversion

TO_CHAR ( date or number, 'format')

Conversion

to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')

See formats below in Date Functions

to_multi_byte, to_single_byte

multi-byte <--> single-byte

Functions

lpad, rpad(c1, n [, c2] )

Add c2 to c1, up to the total length of n. May truncate

ltrim, rtrim (c [,s])

Remove occurences of s to the right of c

substr(c, m [,n] )

Substring, n is length (optional)

substrb(c, m [,n] )

Substring, by byte (?)

replace (c, str [,repl_str] )

Return c with str replaced by repl_str
Example: select replace(replace('line1
line2',chr(13),'[13]'),chr(10),'[10]') from dual;

translate(c, from, to)

Translate character by character.
Example: translate(c,
'0123456789abcdef',
'abcdefghij______')
123554df --> abceed__
Another example:
select translate ( 'line1
line2', chr(9) || chr(10) || chr(13), ' ') from dual;
Another example:
TRANSLATE(UPPER(variance_time),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'nnnnnnnnnncccccccccccccccccccccccccc')

length , lengthb

Length

nvl(length(ltrim(rtrim(the_string))), 0)

Get the length of all strings, even if null or padded with spaces.

ascii

ASCII code

chr(n [using nchar_cs])

Character for an ASCII code, with nchar_cs, takes nvarchar2 equivalent

concat (c, c)

Equivalent to c || c

initcap, nls_initcap

Initial upper case, does not affect non-alphabetic characters

instr ( string_in_which_to_search, sub_string_to_compare[, start_pos, nth_occurence])

Location of string within another string; 0 if not found; 1 is default value for starting position and n^th occurence.

regexp_instr ( string_in_which_to_search, sub_string_to_compare[, start_pos, nth_occurence])

Use regular expressions, new in oracle 10g.

instrb

Same as instr, but for bytes only (same results if single-byte system)

lower, upper, nls_lower, nls_upper

Does not affect non-alphabetic characters

nlssort

Change the sorting order; use before other NLS functions otherwise the default is used.

soundex

Phonetic representation of a string

nls_charset_id

NLS character set ID number

nls_charset_name

See nls_charset_id

Encoding a URL

Encode a URL in PL/SQL (9i)

encodedURL varchar2(100);
url_in varchar2(100);
charset varchar2(40); -- Valid Oracle character set name, NULL for database character set
...
encodedURL := UTL_URL.ESCAPE(url_in, FALSE, charset);

LONG Character Type

Use LOB columns (CLOB, NCLOB, BLOB). Do not use LONG: supported only for backward compatibility.

When copying table, use TO_LOB function:
CREATE TABLE new_table (col1, lob_col CLOB);
INSERT INTO new_table (select o.col1, TO_LOB(o.old_long_col) FROM old_table o);

---

Number Functions

abs(n )
acos(r), asin, atan, atan2,
cei

Data Types

NUMBER(precision[, scale])

precision: 1..38
scale indicates the location of the decimal point
negative number for scale: -2 rounds off to hundreds

trunc(n, m)

Trunc n to m decimals. If m<0 then to the left of decimal point

BINARY_INTEGER

-2'147'483'647..2'147'483'647

DECIMAL, DEC

Same as NUMBER

DOUBLE PRECISION, NUMERIC, REAL

Same as NUMBER

INTEGER, INT

Equivalent to NUMBER(38), i.e. no decimals

SMALLINT

Same as NUMBER(38)

FLOAT(precision)

Same as NUMBER(precision) but the precision is expressed in binary bits from 1 to 126 instead of decimal digits.

Functions

**

Exponentiation operator (not Oracle)

abs

Absolute value

acos, asin, atan, atan2

Arc cosine, arc sine, arc tangent, arc tangent of y/x

cos, sin, tan
cosh, sinh, tanh

Trigonometry

ceil(x)

Next integer (i.e. smallest integer greater than the number)

floor(x)

Previous integer (i.e. largest integer smaller than the number)

exp(x)
ln (x)
log ( n , x )

Natural logarithm, logarithm of base n of a number x (see power too)

mod (x,y)

Remainder of x divided by y

power (x, y)

x ** y (See exp too)

rount (x, n)

x rounded to n places; -1--> nearest ten.

sign(n)

Returns +1, 0, -1 or null.

sqrt x

Square root, x>0

trunc(x, n)

Truncate to n places; -1--> ten.

Conversion

TO_NUMBER ( 'string', 'format')

Conversion

to_number('1A', 'x')

Return 26 (hex to decimal)

bin_to_num(1,0,0,1) --> 9

Convert binary vector to number (9i?)

Custom is_number function:
create or replace function IS_NUMBER(in_str in varchar2) return varchar2 IS
dummy number;
begin
dummy := TO_NUMBER(in_str);
return ('TRUE');
exception when others then
return ('FALSE');
end;

---

Date Functions

1 Jan 4712 BC to 31 Dec 4712 AD

sysdate

Pseudo-column

add_months(date, num_months)

Add months, negative to substract
add_months(31-Jan, 1) returns 28 (or 29) February

last_day(m)

Last day of the month

months_between

number of months between two dates

days_between

number of days between two dates. In 10g?

new_time

Different time zone

next_day

first day in week

round

Round to nearest day, month, ...

trunc( date , 'DD')

Round to full day (time is 00:00)

TO_DATE ( 'string', 'format')

Conversion

Date formats (to_char, to_date)
D Day of week (1-7) DD Day of month (1-31) DDD Day of year (1-366) DAY WEDNESDAY Day Wednesday DY WED Dy Wed IW WW Week of year (1-53) W Week of month	MM 01-12 MON FEB Mon Feb MONTH FEBRUARY Month February Q Quarter yy YY 03 yyyy YYYY 2003 YEAR Spelled out
HH24 Hours, 24-hour format HH HH12 Hours, 12-hour format AM PM Puts either according to time MI Minutes SS Seconds SSSSS Seconds since midnight	CC Century SCC Century for BC RM Roman numerals J Julian calendar

to_char (2004, 'RM') ---> MMIV (note: RM not RN)

Examples:

• DD/MM/YYYY HH24:MI:SS

Determine elapsed time:
v_start NUMBER;
v_start := DBMS_UTILITY.get_time;
ROLLBACK; -- or whatever
DBMS_OUTPUT.put_line('Elapsed time: ' ||(DBMS_UTILITY.get_time - v_start)||' hsecs');

---

Operators

** not	Exponentiation Negation
+ -	Unary operators
* /
+ - ||
= <>!= ~= < <= > >= LIKE IN BETWEEN IS NULL	Equality Non-equality
AND
OR

Comparisons

Unequal: <> != ~=

It is a good idea to TRUNC when comparing dates

String comparisons are case-sensitive. Be careful when comparing CHAR with VARCHAR.

Wildcards for LIKE: % and _

x [NOT] BETWEEN a AND b
x [NOT] IN (a, b, c, ...)
x IS [NOT] NULL

---

Cursors

CURSOR cursor_name IS
SELECT a_column, ...
FROM ...;

OPEN cursor_name (params);
FETCH cursor_name INTO a_variable;
CLOSE cursor_name;

FOR rec_cursor IN cursor_name LOOP
... rec_cursor.a_column ...
END LOOP;

Without explicit cursor:
SELECT a_column, ...
INTO a_variable
FROM ...;

Explicit cursor attributes: cursor_name%attribute

• %isopen --> true/false.
• %found / %notfound --> true/false: no rows fetched (~EOF)
• %rowcount --> number of rows fetched, 0 before first fetch

Implicit cursor attributes: sql%attribute

• sql%isopen always false (the implicit cursor is always closed after execution)
• sql%found / sql%notfound --> true/false: rows returned or rows affected (in insert, update, delete)
  Note that the exception "no_data_found" may be raised before the if statement with sql%notfound!
• sql%rowcount --> number of rows returned

Update one table from another:

DECLARE
CURSOR update_table_cursor is
select <source_columns>, <columns_for_join>
from <source_table>
where <cond> ;
BEGIN
FOR r IN update_table_cursor LOOP
UPDATE <target_table> t
SET t.<target_column> = r.<source_column>,
t.<target_column> = r.<source_column>
WHERE t.<join_column> = r.<join_column> ;
END LOOP;
END;
/

Records

Example

declare
type a_record_type is record (one_col VARCHAR2(20), another_col table.id%TYPE);
a_record a_record_type;
begin
select a, b into a_record from a_source_table where ...;
DBMS_OUTPUT.put_line('a = ' || a_record.one_col );
end;

declare
row_record employee%ROWTYPE;
begin
select * into row_record from a_table where ...;
row_record.a :='new value';
update a_table set row = row_record where ...;
end;

Collections

Three types of collections:

• varrays
• Nested tables (NOT a nested table in the database)
• associative arrays (a sort of lookup)

Nested table:
TYPE nested_table IS TABLE OF NUMBER ;
a_nested_table nested_table := nested_table(1,3,5,6,7); -- declares object AND intializes it
begin
a_nested_table := nested_table(); -- or initialize here
a_nested_table.EXTEND(2); -- extends with two more elements

Example

TYPE tb_in_mem_type IS TABLE OF tb_records;
tb_in_mem tb_in_mem_type;
-- Open source cursor and do a bulk load
OPEN a_cursor(p_facility, p_begin_date, p_end_date);
FETCH a_cursor BULK COLLECT INTO tb_in_mem;
CLOSE a_cursor;
-- Now loop through the table in memory
FOR i IN tb_in_mem.FIRST .. tb_in_mem.LAST LOOP
do things with tb_in_mem(i).id, tb_in_mem(i).account_number, etc
END LOOP;

---

Program Flow

NULL; -- null statement

Routines

[ DECLARE variable declarations ] BEGIN ... [ EXCEPTION ...] END; /	Anonymous block
CREATE [OR REPLACE] PROCEDURE [schema_name.]name (args) [AUTHID {CURRENT_USER | DEFINER}] {IS|AS} variable declarations BEGIN .......same as above	Procedure
CREATE [OR REPLACE] FUNCTION [schema_name.]name (args) RETURN datatype [AUTHID {CURRENT_USER | DEFINER}] {IS|AS} variable declarations BEGIN......... same as above, but don't forget RETURN RETURN value	Function
CREATE [OR REPLACE] PACKAGE [schema_name.]package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} PROCEDURE a_procedure (args); FUNCTION a_fctn (args) RETURN data_type; PRAGMA restrict_references (a_fctn, WNDS, WNPS); -- See pragmas below CREATE [OR REPLACE] PACKAGE BODY [schema_name.]package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} PROCEDURE a_procedure (args) BEGIN ... END; FUNCTION a_fctn (args) RETURN data_type BEGIN ... END;	Package
CREATE [OR REPLACE] TYPE [schema_name.]object_type_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT	Object

DEFINER is default. CURRENT_USER to force evaluation of rights at run-time. If an invoker-rights routine is called, the current user is the session user. If this routing calls another that is invoker-rights, then the current user is still the session user. This happens until a definer-rights routine is called. In all sub-routines called from this routine, the owner of the routine is the current user.

args are parameters separated by commas:
parameter_name [ IN | OUT | IN OUT ] parameter_type [ := value | DEFAULT value ]
IN: the parameter is protected from being changed
OUT: write-only, the value passed to the routine is ignored
No parenthesis if there are no parameters (as opposed to java and others)
Do not include the precision ("varchar", not "varchar(200)")

See section on exceptions

IF

IF cond THEN -- No ";"
...
ELSIF cond THEN -- No "E" after the "S"
...
ELSE
...
END IF;-- space and ";" here

Logical AND: left to right

Loops

FOR loop_index IN [REVERSE] a..b LOOP
....
EXIT WHEN condition;
...
END LOOP;-- space and ";" here

loop_index is implicitely defined as INTEGER.
The EXIT statement is not the best programming practice. Use with caution.
Note that EXIT without WHEN is possible.

WHILE condition LOOP
....
END LOOP;-- space and ";" here

Tip: if incrementing (or decrementing) a variable in the loop, any test in the condition should have a comparison and not equality.

LOOP
....
EXIT WHEN condition;
....
END LOOP;

FOR v_record IN (SELECT ... FROM ... WHERE ...) LOOP
INSERT INTO ... -- or whatever other commands
VALUES (v_record.a_field);
END LOOP;

Label

GOTO label_name;
...
<<label_name>> --No ";" after the label

A statement must exist after the label
As usual, comments about the horrors of using labels apply here too, even if the Oracle compiler imposes a few rules about jumping.

Case

CASE
WHEN cond1 THEN expr1
WHEN cond2 THEN expr2
ELSE expr3
END

Case statement new in 9i.

CASE expr
WHEN val1 THEN statement1;
WHEN val2 THEN statement2;
ELSE statement3;
END;

Case statement new in 9i

cursor

New possibilities with type cursor in 9i

Examples

set serveroutput on [size {50000 | unlimited}]
set linesize 500
create or replace function look_for_bad_chars (a_text varchar2) return integer is
i integer;
r integer;
begin
r := 0;
for i in 1 .. length(a_text) loop
if ascii(substr(a_text, i, 1))>255 then
sys.dbms_output.put_line('Char number ' || to_char(ascii(substr(a_text, i, 1))));
r := 1;
end if;
end loop;
return r;
end;
/

show errors

---

Triggers

Triggers can be fired before or after the SQL statement.
The trigger can be at row-level (once for each row) or at statement-level (once for each statement). Note that row-level triggers cannot query the table.

• Enforce business rules in a before-update row-level trigger. Use declarative constraints whereever possible.
• Use after-update row-level triggers for replication and logging.
• Use statement-level before-update triggers to enforce security rules (that are not dependant on rows).

:old.column_name (null for insert)
:new.column_name(null for delete)

References to the columns. Do not use ":" in the WHEN clause; in the body, remember to prefix with ":".

CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | DELETE | UPDATE [OF col1, col2]} [OR ...]
ON table_name
[[REFERENCING [OLD AS old_alias] [NEW as new_alias] ] FOR EACH ROW [WHEN (condition)]]
DECLARE
...
BEGIN
...
END;

Example

CREATE OR REPLACE TRIGGER table_update AFTER INSERT OR UPDATE OF column1 , column2 , column3 ON the_table REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE v_action VARCHAR2 (1); BEGIN IF INSERTING THEN v_action_cd := 'I'; ELSIF UPDATING THEN v_action_cd := 'U'; END IF; ... END;

Enable and disable:
ALTER TRIGGER trigger_name { ENABLE | DISABLE };

select SUBSTR ( OWNER, 1, 20) AS owner
, TRIGGER_NAME
, STATUS
, TRIGGER_TYPE
, SUBSTR ( TRIGGERING_EVENT, 1, 40) as triggering_event
, BASE_OBJECT_TYPE
, substr ( TABLE_OWNER || '.' || TABLE_NAME, 1, 40) as table_name
, substr ( COLUMN_NAME, 1, 40) as column_name
, ACTION_TYPE
, substr( referencing_names, 1, 50) as referencing_names
, substr ( when_clause, 1, 100) as when_clause
FROM DBA_TRIGGERS;

just some more arrangements are needed:
set long 50000
select the_text from (
select trigger_name, 1 as nn, 'CREATE OR REPLACE TRIGGER ' || DESCRIPTION as the_text FROM DBA_TRIGGERS
UNION
select trigger_name, 2, 'WHEN ' || when_clause FROM DBA_TRIGGERS WHEN when_clause is not null
UNION
select trigger_name, 3, TRIGGER_BODY FROM DBA_TRIGGERS
UNION
select trigger_name, 4, '/' FROM DBA_TRIGGERS
UNION
select trigger_name, 5, ' ' FROM DBA_TRIGGERS
) order by trigger_name, nn ;

---

Exceptions

Error stops processing of PL/SQL block.

DECLARE
error_code NUMBER;
error_msg VARCHAR2(250)
BEGIN
...
BEGIN -- optionally start a sub-block
....
EXCEPTION
WHEN exception_1 THEN
...
WHEN exception_2 THEN
NULL; -- Null statement
WHEN OTHERS THEN
error_code := SQLCODE --> returns 0 (no exception), 1 (user-defined exception),
-1403 (no_data_found), -nnn (error code)
error_msg := SQLERRM --> error message
ROLLBACK;
END; -- end of sub-block
... -- code executed even if an error occured
END; -- end of block

Minimal:

error_code NUMBER;
error_msg VARCHAR2(250)

EXCEPTION WHEN OTHERS THEN
BEGIN
error_code := SQLCODE;
error_msg := SQLERRM;
dbms_output.put_line ('Error code=' || to_char(error_code) || ' Error msg:"' || error_msg || '"');
ROLLBACK;
RAISE; -- Without this, the message "PL/SQL procedure successfully completed." shows even if errors occured
-- another option is to not raise, but write a successful completion message if all goes well.
-- This will help cover cases when dbms output is not showing: a message should show in both cases.
END;

User-defined exception

In declarative portion:
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception, error_number);

Later:
EXCEPTION
WHEN my_exception THEN
...
END;

User-defined Error

my_exception EXCEPTION; -- Declare the name in declarative section

IF ... THEN
RAISE my_exception;
END IF;

EXCEPTION
WHEN my_exception THEN
...
END;

Predefined exceptions

• no_data_found
• too_many_rows
• invalid_cursor
• value_error
• invalid_number -- conversion of number to character string failed
• zero_divide
• dup_val_on_index -- duplicate value for unique index
• cursor_already_open
• not_logged_on -- not logged onto database
• transaction_backed_out -- remote part of transaction is rolled back
• login_denied -- invalid username / password
• program_error -- internal PL/SQL problem
• storage_error -- memory problem
• timeout_on_resource
• others -- catch all errors

Savepoints

...
SAVEPOINT any_name
...
ROLLBACK to any_name;
...

---

Pragma

PRAGMA restrict_references (function-name, WNDS, WNPS);

• WNDS = Writes No Database State: does not modify database tables
• WNPS = Writes No Package State: does not modify packaged variables
• RNDS = Reads No Database State: does not query database tables
• RNPS = Reads No Package State: does not reference package variables.

Example:
function cross_rate (OrigCurr varchar2, DisplayCurr varchar2) return number;
PRAGMA restrict_references (cross_rate, WNDS, WNPS);

PROCEDURE a_sub_prog IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
END;

A subprogram with this pragma can commit or roll back SQL operations without committing or rolling back the data in the main transaction. Useful to log error messages in a sub-program and roll back the main data operation that created the error.

---

Miscellaneous

"exec procedure;" is equivalent to "begin procedure; end;"

Use of execute immediate:

DECLARE
cursor c_indexes is
select owner || '.' || index_name as the_index from dba_indexes
where owner not in ('SYS', 'SYSTEM')
and index_type in ('BITMAP', 'NORMAL') ;
-- or: index_type <> 'IOT - TOP', or 'CLUSTER', or 'LOB'
begin
for r in c_indexes loop
execute immediate ('alter index ' || r.the_index || ' rebuild logging');
end loop;
end;
/

Also:cursor_name integer; rows_processed integer; sql_string varchar2(500); begin sql_string := 'alter user ' || uid || ' identified by ' || pwd; cursor_name := dbms_sql.open_cursor; dbms_sql.parse(cursor_name,sql_string,0); rows_processed := dbms_sql.execute(cursor_name); dbms_sql.close_cursor(cursor_name);

Output in code

(remember to do: set serveroutput on [size {50000 | unlimited}] )
dbms_output.put ('...');
dbms_output.put_line ('...');

Record Types and Table Types

Fill the record first then assign it to the table

DECLARE
l_budget_lines_in PA_BUDGET_PUB.budget_line_in_tbl_type;
l_budget_lines_in_rec PA_BUDGET_PUB.budget_line_in_rec_type;
BEGIN
FOR i IN 1..a LOOP
l_budget_lines_in_rec.pa_task_id :=function_of(i);
l_budget_lines_in_rec.resource_list_member_id:=another_function_of(i);
l_budget_lines_in_rec.raw_cost:=300;
l_budget_lines_in(i) := l_budget_lines_in_rec;
END LOOP;
END;

Bind variables

VARIABLE var_name type -- in sql*plus
:var_name := 1 ; -- in PL/SQL block
print var_name -- in sql*plus

Entering Blocks

BEGIN ... END;
/ --> execute immediately

BEGIN ... END;
. --> store in buffer (see SQL*Plus option SET BLOCKTERMINATOR)

Other Conversion Functions

• chartorowid
• convert
• hextoraw
• rawtohex
• rowidtochar
• to_label (char or varchar2 --> mlslabel)
• bfilename
• dump
• empty_blob
• emty_clob
• greatest
• greatest_lb
• least
• least_lb
• nvl
• uid
• userenv
• vsize