Oracle PL/SQL

Contents

• Logical Functions
• Character Functions
• Number Functions
• Date Functions
• Operators
• Cursors, records and collections
• Program Flow
• Triggers
• Exceptions
• Pragmas
• Miscellaneous

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

"Q quote":
q'[it's a string]'
is equivalent to
'it''s a string'.
The q quote removes the need to double the single quotes.
{}, (), <> can be used in place of []

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.
Example: substr(a_string, 1, instr(a_string, '-')-1) -- String up to but not including the first dash
Example: case when instr(a_string, '-')=0 then a_string else substr(a_string, 1, instr(a_string, '-')-1) end -- String up to but not including the first dash, and return the full string if there is no dash

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)

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_CHAR ( 0.25, '00D99')

Leading zeros

to_number('1A', 'x')

Return 26 (hex to decimal)

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

Convert binary vector to number (9i?)

Number format models
0 Leading or trailing zeros
9 digit, includes 0 if no integer part
B blank for integer part
C ISO currency symbol as defined by NLS_ISO_CURRENCY parameter
L Local currency, as defined by NLS_CURRENCY
D decimal character, according to NLS_NUMERIC_CHARACTER
G group separator, according to NLS_NUMERIC_CHARACTER parameter
1.23EEEE Scientific notation
1.23MI Trailing negative sign
1.23PR Negative number in angle brackets
S Plus or minus sign, can be leading or trailing
FM... Removes the leading blank (format modifier): "In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number" (taken from Oracle documentation)

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

SELECT (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('01/01/1970','DD/MM/YYYY')) * 24 * 60 * 60 FROM DUAL;

Current epoch time

Timestamps

Timestamp store fractional seconds
Conversion: cast(ts as date) or cast(dt as timestamp)
Current time: SYSTIMESTAMP instead of SYSDATE
Difference in timestamps gives an interval, not the number of days
Default format in SQLPlus:
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY/MM/DD HH24:MI'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='YYYY/MM/DD HH24:MI:SS:FF3';
Note: the documentation says that TRUNC does not work with timestamps but I can do it. I guess it converts to date in later versions of Oracle
select sessiontimezone from dual;: gives offset
'US/Eastern', 'US/Central', 'America/Denver', '', '', '', '', '', '', '' in smaller timezone file
'Europe/Zurich', 'America/Guayaquil', 'America/Lima', '', '', '', '', '', '', '' not in smaller timezone file
select to_char( cast (sysdate as timestamp with time zone) at time zone 'GMT' , 'HH24:MI TZH:TZM') from dual;
select to_char( cast (sysdate as timestamp with time zone) at time zone 'America/New_York' , 'HH24:MI TZH:TZM') from dual;

Examples:

• DD/MM/YYYY HH24:MI:SS
• sysdate + 7 - to_number(to_char(sysdate, 'D')) ---> Next Saturday
• trunc(sysdate, 'DD') + 21/24 ---> At 9PM
• to_char (2004, 'RM') ---> MMIV (note: RM not RN)

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

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

You need the last "/" after a PL/SQL block.

Routines

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

PL/SQL function
CREATE OR REPLACE function test_string_out (P_MESS OUT VARCHAR2)
RETURN NUMBER IS
MESSAGE VARCHAR2(32767):=''; -- the maximum is 32767
N_iterations number := 200000;
begin
    for i in 1 .. N_iterations loop
        MESSAGE := substr(MESSAGE || 'a' || to_char(i) || CHR(13) || CHR(10),1,32767);     end loop;
    P_MESS := MESSAGE ;
    DBMS_OUTPUT.PUT_LINE('string out length: '||to_char(length(MESSAGE)));
    RETURN -1;
END test_string_out;
/

Anonymous calling block:
set serveroutput on
declare
the_message varchar2(32767); -- this is the maximum
rtn number;
begin
    rtn := test_string_out(the_message);
    DBMS_OUTPUT.PUT_LINE('string out length: '||to_char(length(the_message)));
    if rtn = 0 then
        DBMS_OUTPUT.PUT_LINE('test_string_out succeed. '||to_char(sysdate,'Dy Mon Dd HH24:MI:SS YYYY'));
    else
        DBMS_OUTPUT.PUT_LINE('test_string_out failed. '||to_char(sysdate,'Dy Mon Dd HH24:MI:SS YYYY')||'.'||' The message is:');
        DBMS_OUTPUT.PUT_LINE(the_message);
    end if;
end;
/

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;

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);

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