After the format mask are several possible NLS parameters:

• NLS_NUMERIC_CHARACTERS—Specifies characters to use for group separators and the decimal point.
• NLS_CURRENCY—Specifies the local currency.
• NLS_ISO_CURRENCY—Character(s) to represent the ISO currency symbol.

In the examples in this section, you can practice some of these conversions. First, you'll per-form a simple character-to-number conversion. Go ahead and execute the code in Listing 7.11.

INPUT
Listing 7.11. Converting a character to an integer value.

 1: DECLARE
 2:      v_Convert_Number VARCHAR2(20) := `1997';
 3:      v_Hold_Number NUMBER ;
 4: BEGIN
 5:      v_Hold_Number := TO_Number(v_Convert_Number,'9999');
 6:      DBMS_OUTPUT.PUT_LINE(`The converted number is: ` || v_Hold_Number);
 7:      DBMS_OUTPUT.PUT_LINE(`The converted number plus 10 is: ` ||
 8:                           (v_Hold_Number+10));
 9: END;
 10: /

OUTPUT
After executing the PL/SQL block, your output should be

The converted number is: 1997
The converted number plus 10 is: 2007

ANALYSIS This block of code simply converted a character integer with a value of 1997, converted it to a number in line 6, and then additionally performed a mathematical calculation to add 10 to the integer in line 7. Without the TO_NUMBER function, you would not be able to perform any type of calculations on characters.

You will often need to convert a field in a table from one datatype to another. For instance, a real estate company uses Oracle to track its listings. Unfortunately, the house prices were declared as a type VARCHAR2(20). The format entered was always the currency symbol ($) followed by the price, offset in commas. The range of prices can be from $.01 to $999,999,999.99. You could write a function to update a column added to the table, change the datatype, and delete the extra column no longer needed. For now, you only need to calculate your commission for some property you had just sold. The going commission rate for agents in the area is 6%. Listing 7.12 shows the conversion of VARCHAR2 to NUMBER and then calculates your commission.

INPUT
Listing 7.12. Converting a character formatted as currency to an integer value.

 1: DECLARE
 2:      v_Convert_Number VARCHAR2(20) := `$119,252.75';
 3:      v_Hold_Number NUMBER ;
 4: BEGIN
 5:      v_Hold_Number := TO_Number(v_Convert_Number,'$999,999,999.99');
 6:      DBMS_OUTPUT.PUT_LINE(`The converted number is: ` || v_Hold_Number);
 7:      DBMS_OUTPUT.PUT_LINE(`Your commission at 6% is: ` ||
 8:                           (v_Hold_Number*.06));
 9: END;
10: /

OUTPUT
When you execute this code, your output appears as

The converted number is: 119252.75
Your commission at 6% is: 7155.165

ANALYSIS This PL/SQL block has an unusually long format mask. When you convert a number, the format mask must be equal to or greater than the length of the number of characters to convert. Remember, the largest value could be $999,999,999.99, so you should create the format for the largest possible value as demonstrated in line 5. But what happens if you break this rule? Go ahead and enter and then execute the code in Listing 7.13.

INPUT
Listing 7.13. Errors with the format mask.

 1: DECLARE
 2:      v_Convert_Number VARCHAR2(20) := `$119,252.75';
 3:      v_Hold_Number NUMBER ;
 4: BEGIN
 5:      v_Hold_Number := TO_Number(v_Convert_Number,'$99,999.99');
 6:      DBMS_OUTPUT.PUT_LINE(`The converted number is: ` || v_Hold_Number);
 7:      DBMS_OUTPUT.PUT_LINE(`Your commission at 6% is: ` ||
 8:                           (v_Hold_Number*.06));
 9: END;
10: /

OUTPUT
Immediately upon execution of this code, you will receive the following errors:

ORA-06502: Message 6502 not found;  product=RDBMS73; facility=ORA
ORA-06512: Message 6512 not found;  product=RDBMS73; facility=ORA
; arguments: ["SYS.STANDARD", ] [720]
ORA-06512: Message 6512 not found;  product=RDBMS73; facility=ORA
; arguments: [] [5]

How do you handle these errors? Simply execute the program Oracle Messages and Codes. Then click on Find and enter the first code, which is ORA-06502. The help you will receive from Oracle is

ORA-06502  PL/SQL: numeric or value error

Cause:     An arithmetic, numeric, string, conversion, or constraint error
 occurred. For example, this error occurs if an attempt is made to assign
 the value NULL to a variable declared NOT NULL, or if an attempt is made
 to assign an integer larger than 99 to a variable declared NUMBER(2).
Action:  Change the data, how it is manipulated, or how it is declared so
 that values do not violate constraints.

Copyright (C) 1995, Oracle Corporation

You're probably wondering, What does all that error code mean? Next, when you get help on the second error message, you will see

ORA-06512  at str line num

Cause:  This is usually the last of a message stack and indicates where
 a problem occurred in the PL/SQL code.
Action:  Fix the problem causing the exception or write an exception
 handler for this condition. It may be necessary to contact the
 application or database administrator.

Copyright (C) 1995, Oracle Corporation

When Oracle compiles, it looks for all possible errors. The first error message indicates that the number assigned to the variable is too large for the mask. Remember, Oracle has no clue as to your intent. In the case of the error messages, you could have made an incorrect declaration, or according to the second error message, you did not create a large enough mask for the function TO_NUMBER.

The last example is a store, which stored all the sales percentages as a VARCHAR2(4) field in the format 33.33. You will need to convert these numbers to their decimal equivalent. Run through the code in Listing 7.14.

INPUT
Listing 7.14. Converting VARCHAR2 percentage data to a decimal equivalent.

 1: DECLARE
 2:      v_Convert_Number VARCHAR2(20) := '33.33';
 3:      v_Hold_Number NUMBER ;
 4: BEGIN
 5:      v_Hold_Number := TO_Number(v_Convert_Number,'999.999999');
 6:      DBMS_OUTPUT.PUT_LINE(`The converted number is: ` || v_Hold_Number);
 7:      DBMS_OUTPUT.PUT_LINE(`Your decimal equivalent is: ` ||
 8:                           (v_Hold_Number/100));
 9: END;
10: /

OUTPUT
When you execute the code in Listing 7.14, your output should appear as

The converted number is: 33.33
Your decimal equivalent is: .3333

The PL/SQL code in Listing 7.14 is simply a repeat, except that you now divide the converted number by 100 with the statement in lines 7 and 8 to arrive at the decimal point.