Section 24.7.  Currency Conversion

24.7. Currency Conversion A discussion of globalization and localization would not be complete without addressing currency conversion issues. The most common approach to the conversion from dollars to yen, for example, is to use a rate table that tracks conversion rates between monetary units. But how does an application know how to display the resulting number? Consider the following: Are decimals and commas appropriate, and where should they be placed? Which symbol is used for each currency ($ for dollar, for Euro, etc)? Which ISO currency symbol should be displayed (USD for example)? Each publication in the g11n schema has a price and is associated with a locale. We can use the TO_CHAR function to format each string, but what about displaying the correct currency? We can use the NLS_CURRENCY parameter to format our prices correctly as follows: CREATE OR REPLACE FUNCTION currency_conv_func RETURN sys_refcursor IS v_currency sys_refcursor; BEGIN OPEN v_currency FOR SELECT pub.title "Title", TO_CHAR (pub.price, locale.currency_format, 'NLS_CURRENCY=' || locale.currency_symbol ) "Price" FROM publication pub, locale WHERE pub.locale_id = locale.locale_id;   RETURN v_currency; END currency_conv_func; / Execute the currency conversion function as follows: VARIABLE v_currency REFCURSOR CALL currency_conv_func( ) INTO :v_currency; PRINT v_currency This returns the following list of prices: Title Price ---------------------------------- ---------------- Oracle PL/SQL Programming, 3rd Edition $54.95 ¥5,800 Oracle PL/SQL Programmierung, 2. Auflage 64 Note that no actual conversion is done here. If you need to automate the conversion from one currency to another, you will need to build your rate table and conversion rules. The NLS_ISO_CURRENCY symbol is generally a three-character abbreviation. With a few exceptions, the first two characters refer to the country or locale, and the third character represents the currency. For example, the United States dollar and the Japanese yen are USD and JPY, respectively. Many European countries use the Euro, however, so the country/currency rule of thumb noted earlier cannot apply. It is simply represented as EUR. The g11n schema includes ISO currency values to help us convert the prices of publications to their correct ISO abbreviations, as you can see in the ISO_CURRENCY_FUNC function: CREATE OR REPLACE FUNCTION iso_currency_func RETURN sys_refcursor IS v_currency sys_refcursor; BEGIN OPEN v_currency FOR SELECT title "Title", TO_CHAR (pub.price, locale.iso_currency_format, 'NLS_ISO_CURRENCY=' || locale.iso_currency_name ) "Price - ISO Format" FROM publication pub, locale WHERE pub.locale_id = locale.locale_id ORDER BY publication_id;   RETURN v_currency; END iso_currency_func; / To execute the ISO_CURRENCY_FUNC function, run the following: VARIABLE v_currency REFCURSOR CALL iso_currency_func( ) INTO :v_currency; PRINT v_currency The result set shows the following: Title Price - ISO Format -------------------------------------- ----------------------- Oracle PL/SQL Programming, 3rd Edition USD54.95 JPY5,800 Oracle PL/SQL Programmierung, 2. Auflage EUR64 USD, JPY, and EUR are included in our price display just as we expected based on the format mask.