Chapter 2 Fundamentals

PL/SQL User s

2.1 Lexical Units

Character Set:

The characters that PL/SQL uses include

Alphanumerical characters

Tabs, spaces and carriage returns

The characters: ( ) + - * / < > + ! ; ,   @ %   # $ ^ & _ | { } ? [ ]

Lexical Units

A line of PL/SQL text is made of lexical units:

delimiters (single or compound symbols)

identifiers including reserved words.

Literal

Comments

The following line of PL/SQL text

Bonus := salary * 0.1 ; -- calculate bonus.

Contains id, delimiters, literal and comments.

2.2 Delimiters:

A delimiter is simple or compound symbols which has special meaning.

(1) Simple Symbols:

+, - addition operator, subtraction/negation operator

/, * division and multiplication operation operators.

=, <, > relational operators

(, ) expression or list delimiters.

; statement terminator

% attribute operator for table and column.

, item separator

. component operator, member accessing operator.

@ remote access operator

character or character string delimiter.

quoted identifier.

: host variable identifier.

(2) Compound Symbols:

** exponentiation operator

<>, !=, ~=, ^=, <=, >=, => : relational operators

= assignment operator

.. range operator in for loop.

|| string concatenation operator

<<, >> label delimiters.

- - single line comment delimiter

/*, */ multiple line comment delimiter.

2.3 Identifiers

3 An identifier is used to name a variable, constants, program units such as functions, procedures packages.

4 An identifier characters string consists of alphanumerical letters, _, $, # and beginning with alphabetical letters.

5 Reserved words: reserved words are identifiers. Those include IF, END, FOR and etc.

6 Predefined identifiers: Identifiers defined in STANDARD package include NOTFOUD, INVALID_NUMBER and etc.

7 Quoted identifiers: For flexibility, PL/SQL allows you double quote the identifier so that characters such as space, / can be used in identifiers. The quoted identifiers must be all capital characters.

2.4 Literal

A literal is a constant of number, character, string or Boolean value:

123,  a ,  This is a string , TRUE, FALSE

where the character and string are both quoted by single quotation marks.

2.5 Comments:

-- Single line comments

/* Multiple line comments

*/

2.6 Data Types

1 BINARY_INTTEGER:

stored signed integer between  231   1 and 231 - 1.

The subtypes of BINARY_NUMBER include

NATUAL: 0,  , 2147483647

POSITIVE: 1,  , 2147483647

2 NUMBER ( precision, scale ):

The precision specifies the total number of digits for the number.

The scale specifies where to round the number. A positive scale will round to the number scale digit to right the decimal point. A positive scale will round to scale digits to the left of decimal point. For example

NUMBER (7, 2) will round the number at hundredth.

NUMBER(7, -3) will round the number at thousand.

The precision ranges between 1 to 38, and scale ranges between  84 to127.

Subtypes of NUMBER

DEC, DECIAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT

Those subtypes are for compatibility with other systems and more descriptive names.

3 PLS_INTEGER

PLS_INTEGER has the same range as NUMBER and BINARY_INTEGER.

PLS_INTEGER uses less space to store a number and uses machine operation, and therefore is faster than NUMBER and BINARY_INTEGER.

Exception will be raised for PLS_INTEGER if overflow occurred. However, no exception is raised if overflow occurred in NUMBER of BINARY NUMBER.

4. CHAR [ (max_length) ]

Specify a fixed length string variable. The maximum length is 32,767 and the default length is 1. The parameter must be literal (it cannot be constant or variable. Even the CHAR(n) can have n as large as 32,767 bytes, but the column of database table can only have 255 characters. To store long string, use LONG column which can stored 3 gigabytes. Also you cannot select a column longer then 32,737 bytes into a CHAR variable.

The subtypes of CHAR : CHARACTER, STRING are two subtypes of CHAR for compatibility with IBM/DS and IBM DB2 types or you need more descriptive names.

5. VARCHAR( max_length )

Specify a variable length character variable and parameter is required. The parameter like in CHAR specify the number of bytes reserved for the variable. In case of multiple bytes per characters, the number of characters stored in the VARCHAR(n) and CHAR(n) variable is less than n.

The max_length of VARCHAR2 variable is 32,767.

A VARCHAR2 column of a table has maximum length 2000. Therefore, you can not insert a VARCHAR(n) variable longer than 2000 into VARCHAR2 column.

You can insert a VARCHAR2 variable into LONG column. However, you cannot select a LONG with more than 32,767 bytes into VARCHAR2 because of limit of VARCHAR2 variable.

Subtypes STRING and VARCHAR are for compatible with ANSI/ISO and IBM types. You are not encouraged to use VARCHAR since VARCHAR may change to accommodate SQL standard.

6 LONG

Specify variable-length character string with maximum length = 32,767.

The LONG database column can stored 2 gigabytes data. So you can insert a LONG variable into a LONG column, but you cannot select a LONG column longer than 32,767 column into LONG variable.

Long column can be used to stored text, array of characters, short document. It can be used in INSERT, DELETE, UPDATE, and SELECT statements. However, you cannot used it in expressions, function calls, SQL clauses like WHERE, group by, order by.

7. RAW ( n )

ROW variable are used to stored binary data or character string such as graphics and pictures. PL/SQL does not interpret RAW data, and Oracle does not transfer when RAW data is transfer from one system to another system.

A row variable can be as long as 32,767 bytes. However, a RAW column can have only 255 bytes.

8. LONG RAW

A LONG RAW variable can have as many as 32,767 bytes while a LONG RAW data can be as long as 2 gigabytes.

You can insert a LONG RAW variable data into LONG RAW column. However, you cannot select a LONG RAW column longer than 32,767 into a LONG RAW variable.

9. ROWID

When a table is created, a pseudo column is created for that table. Each row has a unique number after inserted. Accessing row with ROWID is the fastest way.

ROWID variable can be used to store row identifier. A ROWID variable can be printed out with

ROWIDtoCHAR ( rowid_varible / rowid_column )

* When ROWID is printed out it has the following hexidecimal format:

BBBBBBBB.RRRR.FFFF

The first 8 hexadecimal digits is the block address and next 4 digit is the row number and the rightmost 4 digits are file number in a database.

* The following statements show how to use row id:

SELECT ROWIDTOCHAR ( ROWID ), name from employee ;

SELECT * FROM employee where ROWID =  0000049F.0007.0004 

10. Boolean

* Boolean variable is used to store boolean values or non-value (NULL).

11. Date

Store data and time. The valid date for the date variable is January 1, 4712 BC to Dec. 31, 4712.

* When date is stored in date column, the date and time (seconds from the midnight) are stored. The date part is default to the first day of current month and the time part is default to midnight.

-Defined Subtype

A similar way to typedef in C allows you to define new subtype based on a base type. The base type will inherit all the operations of the base type. No new operator can be added to the new subtype.

No length constraint can be used in the base type to define new subtype. However, %TYPE attribute can be used to work around that restriction.

You define new subtype in the declaration part with following syntax:

SUBTYPE subtype_name IS base_type ;

Example: All subtype definition must be defined in DECLARATION part.

SUBTYPE Counter IS NATUAL ; -- Counter can be used to define varaible

Tmp VARCHAR2 ( 12 ) ;

SUBTYPE phone_type IS tmp%TYPE ; -- define a phone type with at most 12 chars.

2.7 Type Conversions

Values among certain types can be converted either implicitly or explicitly. The implicit type conversion is done by Oracle. The explicit type conversion is done by calling some type conversion function.

The following table shows which type can be converted to other type implicitly.

To

Binary_ Char Date Long Number Raw RowID Varchar2

Integer

From

Binary_

Integer X X X X

Char X X X X X X X

Date X X X

Long X X X

Number X X X X

Raw X X X

RowID X X

Varchar2 X X X X X X X

The following table shows the functions need for type Conversions:

To

Char Date Number Raw RowID

From

Char TO_DATE TO_NUMBER HEXTOROW CHARTOROWID

Date TO_CHAR

Number TO_CHAR TO_DATE

Raw RAWTOHEX

RowID ROWIDTOCHAR

2.8 Declarations

Declarations declare variable, constants, cursors and user defined subtypes.

DECLARE is used only in the PL/SQL blocks to begin the declaration part. The reserved word DECLARE is removed when a PL/SQL block becomes part of subprogram. Otherwise syntax error occurs.

1. Variables Declarations

The full syntax of define variable is

identifier TYPE [ [ NOT NULL ] := expression ] ;

where

(1) identifier defines variable name

(2) TYPE is either base data type or user-defined subtype, or a %TYPE attribute of a column, variables, or %ROWTYPE attribute of a table.

(3) NOT NULL will not allow the variable contains NULL value.

(4) The expression assigns a value into the variable. The initial value is a must if NOT NULL is specified. The expression can be variables, constants, function call and operators.

2 Examples:

Example 1: Use function call to initialize variable.

-- To debug a PL/SQL block, you can use server utility.

-- First set the server output on, then use DBMS_OUTPUT package to bebug the block. For detail discussion read Oracle Application Developer Guide.

SQL > SET SERVEROUTPUT ON

DECLARE

S VARCHAR ( 20 ) := TO_CHAR( sysdate,  MM/DD/YY HH:MM:SS  ) ;

BEGIN

DBMS_OUTPUT.PUT_LINE ( s ) ;

END ;

SQL > RUN

Example 2: Use %ROWTYPE to define varaible.

declare

cursor c IS select deptno, count(*) CNT, sum(sal) Total

from scott.emp group by deptno ;

t c%ROWTYPE; --cursor C  s type attribute is used to define var.

begin

open c;

loop

fetch c into t;

exit when c%NOTFOUND ;

dbms_output.put_line(t.deptno || ', ' || t.CNT || ', ' || t.Total );

end loop;

end ;

2. Restrictions:

Notice that columns in the select statement for a cursor must have alias. Otherwise syntax error will occur.

Single variable can be defined per line.

No forward variable declaration; variable must be defined before its reference.

3. Constant Declarations

identifier CONSTANT TYPE := expression ;

4. Naming Conventions

When name (of procedure, table, column and etc) is referenced, it can be in the different formats:

identifier (   ) Simple format

object.identifier.(   ) Qualified format.

Identifer @network (   ) remote

object.identifier @network qualifiered remote.

Names in PL/SQL are not case-sensitive.

Local constant and variable names overload the table name if the table name is not qualified/. That is the following will cause error:

DECLARE

Emp varchar(10) ;

CURSOR c IS select * from emp ; -- error will occur.

above will be fine if qualified emp with schema.

Column names of table have higher precedence than local constants or variables.

DECLARE

Ename varchar(10) :=  Robert  ;

CURSOR c IS select * from emp WHERE ename = ename ;

The condition will be true always since two enames in where clause will be taken as column name. It is good idea to pre-fixed the local name with my_.

2.9 Scope and Visibility

PL/SQL blocks can be nested. That is subblocks can be defined in the excutable and exception handling parts.

Same name can be used to define variable, constant, type, subprogram in block or subblock. The scope and visibility of a name in nested blocks are very similar to Pascal scope and visibility: Outer name is visible to all inner blocks. The inner name overwrite the outer names. Inner names are visible in blocks in which the names are defined.

Example:

declare

emp varchar(10) := 'John Doe' ;

cursor c IS select * from scott.emp;

begin

declare

emp NUMBER(7, 2) := 3.1415 ;

begin

dbms_output.put_line( emp ); -- 3.14 is printed.

end ;

for t in C loop

dbms_output.put_line( t.ename ); -- emp names are printed.

end loop;

dbms_output.put_line( emp ); --  John Doe  is printed.

end;

2.10 Assignment Statements

Variable are initialized to NULL by default.

Operation with NULL value will result NULL usually.

TRUE or FALSE are global constant names and can be assigned to Boolean variables. Like most languages, an Boolean expression can be assigned to a Boolean variable. Alternatively, values in database can be assigned to variables by either SELECT INTO or FETCH INTO statements.

2.11 Operators and Expressions

2.11.1 Operator and their Precedence:

Operator Operations

**, NOT exponentiation, logical negation

+, - identity, negation

*, / multiplication, division

+, -, || addition, subtraction concatenation.

=, !=, <, <=, >, >=,

IS NULL,LIKE, BETWEEN, IN comparison

AND conjunction

OR inclusion.

* Examples

(1) IN operator: DELETE FROM emp where ename IN ( NULL,  Smith ,  Lynos ) ;

(2) BETWEEN operator: UPDATE emp SET sal = sal * 1.05 WHERE sal BETWEEN 1000 and 1200

(3) Date Comparison

DECLARE

D1 DATE :=  01-JAN-94  ;

D2 DATE :=  31-DEC-93  ;

BEGIN

If ( d1 > d2 ) Then

DBMS_OUTPUT.PUT_LINE ( D1 ||   is larger than   || d2 ) ;

END IF;

END ;

(4) Don t compare two real number for exact equality or inequality.

2.11.2 Handling NULL

When NULL is a operand of a unary operator, binary, ternary (BETWEEN) operator, NULL will be the result after the evaluating the operation (except v IS NULL and v contains NULL).

Zero-length string is treated as NULL.

-- Testing NULL values. Any operator (except IS NULL) with

-- NULL operand will result NULL or FALSE.

declare

i number(3) ;

ok boolean ;

begin

ok := i between 10 and 100; -- I has NULL, OK is false.

if ok then

dbms_output.put_line( 'TRUE' ) ;

else

dbms_output.put_line( 'FALSE') ;

end if;

i := 50;

ok := i between 10 and 100 ;

if ok then

dbms_output.put_line( 'TRUE' ) ;

else

dbms_output.put_line( 'FALSE') ;

end if;

ok := '' < 'ABC' OR '' >= 'ABC';-- null string is treated as NULL.

if ok then

dbms_output.put_line( 'TRUE' ) ;

else

dbms_output.put_line( 'FALSE') ;

end if;

end ;

/*

FALSE

TRUE

FALSE

Are the output

*/

Concatenation operator ignores NULL value.

NOT v results NULL if v contain NULL value.

Variable is initialized to NULL when defined without initializatin.

If a NULL argument is passed to a function, a NULL is return except in the following three cases:

(1) DECODE function

SELECT partname, DECODE( partcolor, 1,  Red , 2,  Yellow , 3,  Blue , NULL,  White ) ;

White color is printed out for all parts whose color code is NULL.

(2) SELECT ename, NVL( comm, 0.0) FROM emp ; All null commission column will be printed out as o.o

(3) New_string := REPLACE ( old_string, old_substring, new_substring ) ;

The new string will be the same string if old_substring is NULL. Otherwise, new_string will be old_string with all old_substring in old_string replaced with new_substring.

2.12.3 Built-in Functions

Built-in functions can be called in either SQL statement or PL/SQL statement (except that DECODE function can only be called from SQL statement). Function calls can be nested. The built-in functions can be divided into groups: error-reporting, number functions, character functions, conversion functions, date functions and miscellaneous functions

2.12.1.1 Error Reporting Functions

Function SQLCODE return NUMBER

The function return code of an exception. The code is negative exception no data found exception which is 100. If is a user-defined exception the value return is +1 if the user-defined exception is not associated with an integer exception with EXCEPTION_INT pragma. Call to SQLCODE within exception handler is meaningful. Call to SQLCODE outside exception handler returns 0 always.

Function SQLERRM [ ( n NUMBER ) ] return CHAR

The function returns error message associated the error code from anywhere the SQLERRM is called and supplied with argument n. If n is omitted, then call to SQLERRM outside exception handler will result  ORA-0000: normal , successful completion . If a user-defined exception is raised, the message from a the corresponding handler is  User-Defined Exception .

2.123..2 Number Functions

Number functions are usually the math function which take one or two arguments and return numeric values.

Function Prototype Function

ABS ( n NUMBER )

CEIL( n NUMBER )

COS( n NUMBER )

COSH( n NUMBER )

EXP( n NUMBER ) return e to the nth power

FLOOR( n NUMBER )

LN( n NUMBER ) return natural logarithm of n

LOG( m NUMBER, n NUMBER ) return m-based logarithm of n

MOD( m NUMBER, n NUMBER ) return reminder of m / n

POWER( m NUMBER, n NUMBER ) return the number m raised to nth power.

ROUND( m NUMBER, [ n NUMBER ]) return m round at 0, n position left (right) to decimal ) point if

m is omitted or n is negative (positive).

SIGN( n NUMNER ) -1, 0 , +1 if n is negative, 0 or positive.

SIN( n NUMBER )

SINH( n NUMBER )

SQRT( n NUMBER ) n must be 0 or positive number.

TAN( n NUMBER )

TANH( n NUMBER )

TRUNC( m NUMBER, [ n NUMBER ]) return m truncated at 0, n position left (right) to decimal )

point if m is omitted or n is negative (positive).

2.12.3 Character Functions

Function Prototype Function

ASCII ( c VARCHAR2) return NUMBER return the ascii code.

CHR ( n NUMBER) return VARCHAR2 return char in db char set that n represents

CONCAT( s1 VARCHAR2, s2 VARCHAR2) return VARCHAR2

Attach s2 to s1 first and returns the combined string. If NULL argument is treated as zero-length string. Neither s1 nor s2 will be changed by the concat function.

INITCAP ( s VARCHAR2) return VARCHAR2 return string by converting first letter of each word in s to upper case and all other letter in lower case. S will be the same as before after the call.

INSTR ( s1 VARCHAR2, s2 VARCHAR2 [, pos NUMBER [, n NUMBER] ] ) return NUMBER : search s1 starting a position pos for n s occurrence of s2. If pos is negative, the search is done backwards. The return value is the first letter of that occurrence. P and n are default to 1.

LENGTH ( s VARCHAR2) return NUMBER

LENGTH ( s CHAR) return NUMBER

Return the number of characters in s and including trailing blanks if s is of CHAR type.

LENGTHB ( s VARCHAR2) return NUMBER

LENGTHB ( s CHAR) return NUMBER

Return the number of bytes in s and including trailing bytes if s is of CHAR type.

LOWER ( s VARCHAR2) return VARCHAR2

LOWER ( s CHAR) return CHAR

Return string obtained by converting s to lowercase.

LPAD ( s VARCHAR2, len NUMBER [, pad VARCHAR ] ) return VARCHAR2

Return a string with len characters from s if s more than len characters. If s has less than len character, pad( or blank character) is used on the left of s to make a string with length = len.

LTRIM ( s VARCHAR2, set VARCHAR ) return VARCHAR2

Remove from the first characters which appeared in set. If set is omitted, then it default to single space.

DBMS_OUTPUT.put_line ( LTRIM (  This is a test ,  isTh ) )

Will print   is a test . There is a leading space.

REPLACE ( s1 VARCHAR2, s2 VARCHAR2 [, s3 VARCHAR2 ] ) return VARCHAR2

Return s1 with all occurrence of s2 replaced by s3. If s3 is omitted, all s2 occurrence in s1 are removed. If s2 is NULL, then s1 is returned.

REPLACE ( s VARCHAR2, len NUMBER [, pad VARCHAR2 ] ) return VARCHAR2

Returns the string s right-padded to length len, with the sequence of characters in pad replicated as many times as necessary. If pad is omitted, single blank is default. If s is longer than len characters, the first len characters are returned.

RTRIM ( s VARCHAR2 [, set VARCHAR2] ) return VARCHAR2

Returns s with right characters appeared in set removed until the left most character which does not appear in set. If set is not specified, the set is default to a single blank character.

SOUNDEX ( s VARCHAR2) return VARCHAR2

Returns a string which is the phonetic representation of string s. The function allows you to compare English words that are spelled differently but sounds a like. The phonetic representation is defined in The Art of Computer Programming, Vol. 3 by Donald Knuth.

SUBSTR ( s VARCHAR2, pos NUMBER [, len NUMBER ] ) return VARCHAR2

Returns a substring of string s, starting at pos character position, and with length equal to len characters. If len is omitted, the characters between pos and end of s are returned. The pos can be negative and len must be positive. If pos is negative, the substring starts from the end of string s.

SUBSTRB ( s VARCHAR2, pos NUMBER [, len NUMBER ] ) return VARCHAR2

Similar to SUBSTR. The difference is that SUBSTRB starts at pos byte (not character) position and the len is length in byte (not in character). In language the length of a character is one byte, SUBSTR and SUBSTRB are the same.

TRANSLATE ( s VARCHAR2 set1 VARCHAR2, set2 CHAR ) return VARCHAR2

Returns string s with all occurrences of set1 replaced set2.

UPPER ( s VARCHAR2) return VARCHAR2

UPPER ( s CHAR) return CHAR

Returns s with all letters in uppercase.

2.12.4 Conversion Functions

CHARTOROWID ( s CHAR ) return ROWID

CHARTOROWID ( s VARCHAR2 ) return ROWID

Convert a string of char or varchar2 type to rowid type value.

CONVERT (str VARCHAR2, set1 VARCHAR2 [, set2 VARCHAR2] ) return VARCHAR2

Converts the string from one character set to another character set. Set1 and set2 can be database column name or character set name such as US7ASCII, WE8DEC, WE8HP and etc.

HEXTORAW ( s VARCHAR2) return RAW

HEXTORAW ( s CHAR) return RAW

Converts the hexadecimal string of type CHAR or VARCHAR2 type to RAW type value.

HEXTORAW ( bin RAW ) return VARCHAR2

Converts the binary value bin from type RAW to a hexadecimal string of type VARCHAR2

CHARTOROWID ( bin ROWID ) return VARCHAR2

Convert the binary value bin of type ROWID to an 18-byte hexadecimal string of VARCHAR2.

TO_CHAR( dt DATE [, fmt [, nlsparam] ] ) return VARCHAR2

Converts a date to string. NLSPARAM specifies the language in which the month and abbreviation are used.

TO_DATE( s VARCHAR2 [, fmt [, nlsparam] ] ) return DATE

TO_DATE( s NUMBER [, fmt [, nlsparam] ] ) return DATE

Converts string or a number to a value of type DATE in the format specified by fmt. The format models are given in the following table:

Format Model Description

CC, SCC century (S prefixes BC date with a minus sign)

YYYY, SYYYY year (S prefixes BC with minus sign)

IYYY year based on ISO standard

YYY, YY, Y last three, two, or one digit(s) of year.

IYY, IY, I last three, two, or one digit(s) of ISO year.

YEAR, SYEAR year spelled out (S has the same meaning as before)

RR last two digits of year in another century.

BC, AD BC or AD indicator

B.C., A.D. BC or AD indicator with periods

Q quarter of year

MM month number (01-12)

RM Roman numeral month (I-XII; JAN=I)

MONTH month name

MON abbreviation name of month

WW week of year (1-53)

IW week of year based on ISO standard

W Week of month (1-5)

DDD day of year (1-366)

DD day of month

D day of week

DAY name of day (in a week?)

DY abbreviation of name of day

J Julian day ( number of day from January 1, 4712 BC)

AM, PM Meridian indicator

A.M., P.M. Meridian indicator with periods

HH, MM12 hour of day (1-12)

HH24 hour of day (0-23)

MI minute (0-59)

SS seconds

SSSSS seconds past midnight (0-86399)

TO_CHAR ( num NUMBER [, fmt VARCHAR2 [, nlpparms ] ) return VARCHAR2

Convert a number to a string by the format specified. All format models for conversion between CHAR and NUMBER are given late.

TO_NUMBER ( s CHAR [, fmt VARCHAR2 [, nlpparms ] ) returN NUMBER

TO_NUMBER ( s CHAR [, fmt VARCHAR2 [, nlpparms ] ) returN NUMBER

Converts character string to number in the forma specified by fmt.

Format Example Desciption

9 9999 significant digit

0 0999 leading zero

$ $999

B B999 leading blank

MI 999MI Trailing minus sign

S S999 leading sign

PR 999PR angle bracket for negative values

D 99D99 decimal separator

G 9G99 group separator

C C999 ISO currency symbol

L L999 local currency symbol

, 9,999 comma

. 99.99 period

V 999V99 10n multiplier; n is the number of 9s after V

EEEE 9.99EEEE scientific notation

RN, rn RN upper or lower-case Roman numeral.

2.12.5 Date Functions

ADD_MONTHS (dt DATE, m NUMBER ) return DATE

ADD_MONTHS (m NUMBER, dt DATE ) return DATE

Add or subtract |m| month the dt. If dt  s day is more than the last day of new date, the last day of the month will appear the resultant month.

LAST_DAY( dt DATE ) return DATE

Returns the last day of month containing dt.

MONTHS_BETWEEN ( dt1 DATE, dt2 DATE ) return NUMBER

Returns the month between dt1 and dt2. The result is positive if dt1 is later than dt2. Otherwise the result is negative. If dt1 and dt2 fall on the same days of month, the result is integer. Otherwise the result is a fraction number.

NEW_TIME ( dt DATE, zone1 VARCHAR2, zone2 VARCHAR2)

Convert date and time in dt in zone1 to date and time in zone2. The possible values for zone1 and zon2 are

Value Description

AST Atlantic standard time

ADT Atlantic daylight time

BST Bering standard time

BDT Bering daylight time

CST Central standard time

CDT Center daylight time

EST East standard time

EDT East daylight time

GMT Greenwich Mean time

HST Alaska-Hawaii standard time

HDT Alaska-Hawaii daylight time

MST Mountain standard time

MDT Mountain daylight time

MST Atlantic standard time

NST new-found land standard time

PST Pacific standard time

PDT Pacific daylight time

YST Yukon standard time

YDT Yukon daylight time

NEXT_DAY ( dt DATE, day VARCHAR2) return DATE

Returns the date of first day of the week named by day that is later than date dt. Day must be the seven day of a week.

ROUND (dt DATE [, fmt VARCHAR2] ) return DATE

Rounds dt to a time unit specified by fmt:

Format Model Description

CC, SCC century

SYEAR, YYYY, SYEAR, YYY, YY, Y year

Q quarter

MONTH, MON, MM month

WW first day of a week in a year

W first day of week in a month

DDD, DD, J day

DAY, DY, D nearest Sunday

HH, HH12, HH24 hour

MI minute

SYSDATE return DATE

Returns system date and time.

TRUNCT ( dt DATE [, fmt VARCHAR ] ) return DATE

Returns dt with time portion truncated. The format is the same as those used in round.

2.12.6 Miscellaneous Functions

DECODE ( expr, search1, result1 [, search2, result2]   [default] )

Compare expr with search i and return result if expr matches search i. If no match is found then, return default.

DUMP ( expr DATE [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, pos BINARY_INTEGER ]]] ) return VARCHAR2

DUMP ( expr NUMBER [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, pos BINARY_INTEGER ]]] ) return VARCHAR2

DUMP ( expr VARCHAR2 [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, pos BINARY_INTEGER ]]] ) return VARCHAR2

Returns internal representation of expression expr. The fmt can be 8 (octal), 10 (decial), 16 (hexadecimal) and 17(single character). Pos and len specifies which part is dumped. The default is the whole expression in decimal.

GREATEST ( e1, e2, d3 ,   )

Returns the largest value in a list of expressions.

LEAST ( e1, e2, d3 ,   )

Returns the smallest value in a list of expressions.

NVL( s1 CHAR, s2 CHAR ) return CHAR

NVL( dt1 DATE, dt2 DATE) return DATE

NVL( b1 BOOLEAN, b2 BOOLEAN) return BOOLEAN

NVL( n1 NUMBER, n2 NUMBER) return NUMBER

NVL( s1 VARCHAR2, s2 VARCHAR2 ) return VARCHAR2

Return first expression is it is NULL. Otherwise returns the second expression.

UID return VARCHAR2

Returns unique identification number assigned to the current Oracle user.

USER return VARCHAR2

Returns username of the current Oracle user.

USERENV ( s VARCHAR2 ) return VARCHAR2

Returns information about current session. The s determine the information to retrieve:

String Result from USERENV

 ENTRYID  auditing entry identifier

 LABEL  returns the session label; available only in Trusted Oracle.

 LANGUAGE  Returns language, territory, and database character set in use.

 SESSIONID  Returns the auditing session identifier.

 TERMINAL  Returns operating system identifier for the session terminal.

You cannot specify  ENTRYID , or  SESSIONID  option in SQL statement that access a remote database.

VSIZE ( e DATE ) return NUMBER

VSIZE ( e NUMBER ) return NUMBER

VSIZE ( e VARCHAR2 ) return NUMBER

Returns the number of bytes in internal representation of expression e. If e is NULL, then NULL is returned.