Search This Blog

Monday, April 19, 2010

Some useful functions

In Oracle/PLSQL, the initcap function sets the first character in each word to uppercase and the rest to lowercase.

The syntax for the initcap function is:initcap( string1 )

string1 is the string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

initcap('tech on the net'); would return 'Tech On The Net'

initcap('GEORGE BURNS'); would return 'George Burns'


In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).


Applies To:

Oracle 9i, Oracle 10g, Oracle 11g


For example:

You could use the decode function in an SQL statement as follows:


SELECT supplier_name,

decode(supplier_id, 10000, 'IBM',

10001, 'Microsoft',

10002, 'Hewlett Packard',

'Gateway') result

FROM suppliers;


The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN

result := 'IBM';

ELSIF supplier_id = 10001 THEN

result := 'Microsoft';

ELSIF supplier_id = 10002 THEN

result := 'Hewlett Packard';


result := 'Gateway';



Check for file existence:

If Not (FileExists(&Str_Filepath, %FilePath_Absolute)) Then

MessageBox(0, "", 0, 0, " *** Invalid File Path/ No write access -- Check the File Path <" | &Str_Filepath | ">");

Exit (1);




  1. I dont know what to say. This web site is amazing. Thats not truly a actually substantial statement, but its all I could come up with soon after reading this. You know a great deal about this subject. Much making sure that you produced me wish to understand additional about it. Your web site is my stepping stone, my buddy. Many thanks for that heads up on this theme.

  2. Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.