O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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. I can use the TO_CHAR function to format each string, but what about displaying the correct currency? I can use the NLS_CURRENCY parameter to format my prices correctly as follows:

/* File on web: g11n.sql */
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;

I 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required