Oracle and PL/SQL Recipes: A Problem-Solution Approach

6-3. Converting a Number to a String

Problem

You need to alter some numbers into a currency format for display. Given a set of numbers, your application will perform a calculation and then convert the outcome into currency format, which will be a string type.

Solution

Use the TO_CHAR conversion function to obtain a nicely formatted currency string. The following code block accepts a number, performs a calculation, and then converts the number to a string:

CREATE OR REPLACE FUNCTION CALCULATE_BILL(bill_amount IN NUMBER) RETURN VARCHAR2 AS   tax                     NUMBER  := .12;   tip                     NUMBER  := .2;   total_bill              NUMBER  := 0; BEGIN   total_bill := bill_amount + (bill_amount * tax);   total_bill := total_bill + ...