I'm very new to Oracle because I'm start using Oracle when I reach here. When I was in Myanmar, our RDBMS is Microsoft SQL. In MsSQL, GUI is very simple and for example: if you need your ID as auto increment, set identity to true/yes in Ms.SQL properties. In Oracle, we need to create trigger for auto increment. Today, I feel like myself to create Function in Oracle for convert number format to Ordinal Format which I already created in CF UDF. Why I created the same Function in Orcale is because I want to generate report by using cfreport tag.

Here is my simple Function

view plain print about
1CREATE OR REPLACE Function ConvertOrdinal
2 --Input parameter from outside
3 ( cycle IN varchar2 )
4 RETURN varchar2
5IS
6 --Output parameter from function
7 ordinalformat varchar2(5);
8BEGIN
9 if ((mod(cycle,10) = 1) AND (mod(cycle,100) = 1))then
10 ordinalformat := 'st';
11 elsif
12 ((mod(cycle,10) = 2) AND (mod(cycle,100) = 2))then
13 ordinalformat := 'nd';
14 elsif
15 ((mod(cycle,10) = 3) AND (mod(cycle,100) = 3))then
16 ordinalformat := 'rd';
17 else
18 ordinalformat := 'th';
19 end if;
20RETURN cycle || ' ' || ordinalformat;
21
22EXCEPTION
23WHEN OTHERS THEN
24 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
25END;

Execute this function in PL/SQL

view plain print about
1SELECT ConvertOrdinal('1') CO FROM DUAL
Output will display as follow
view plain print about
1CO
2====
31 st

This entry was posted on September 18, 2010 at 10:53 PM and has received 2161 views. There are currently 2 comments. Print this entry.