Listing 4.10. continued

20:   --Display the two character strings to show that they are the same.
21:   DBMS_OUTPUT.PUT_LINE(`CD1 = ` || cd1);
22:   DBMS_OUTPUT.PUT_LINE(`CD2 = ` || cd2);
23:   --Repeat the same example as above, but with numbers.
24:   cn1 := `995';
25:   n1 := cn1 + .99 ;
26:   cn2 := n1;
27:   DBMS_OUTPUT.PUT_LINE(`CN1 = ` || cn1);
28:   DBMS_OUTPUT.PUT_LINE(`CN2 = ` || cn2);
29: END;
30: /

OUTPUT

CD1 = 15-Nov-61
CD2 = 15-NOV-61
CN1 = 995
CN2 = 995.99
PL/SQL procedure successfully completed.

ANALYSIS
The code in Listing 4.10 illustrates some common implicit conversions. The first assignment, in line 11, causes no conversion at all because a string is assigned to a string variable. The assignment statement in line 14, however, does represent an implicit conversion because it must convert the string representation of the date to Oracle's internal format before it can assign the value to d1. In line 19, that date is again converted back to a string format. Lines 23 through 28 repeat the same process, but this time with a number.

Implicit conversions are convenient, but beware. In relying on them, you are relying on Oracle's built-in assumptions and on default settings you might not even be aware of, and which might change from one release to another. The format of a date leads to some good examples. Did you know that Oracle's default date format varies depending on the language setting? That it can also be installation-dependent? And that it can vary between a client PC executing a Developer/2000 script and a database server? In fact, the date format can even be changed for the duration of a particular session. Figure 4.2 illustrates this by showing the same PL/SQL code succeeding once and then failing after the date format has been changed.

For the reasons just listed, it is often safer to code conversions explicitly. Explicit conversions also better document your code by making it clear to other programmers exactly what is happening.

Explicit Conversion

Oracle has several built-in functions that are designed to convert information from one datatype to another. These are shown in Table 4.12.

Figure 4.2.
The default date
format is changed.

Table 4.12. Conversion functions.

Each of these functions takes three arguments: the value to be converted, a format string specifying how that conversion is to take place, and optionally a string containing language-specific parameters. These functions are described in detail on Day 7, but Listing 4.11 gives some common examples of how you can use them.

INPUT
Listing 4.11. Examples of the conversion functions.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   d1    DATE;
 4:   d2    DATE;
 5:   d3    DATE;
 6:   d4    DATE;
 7:   n1    NUMBER;
 8:   n2    NUMBER;
 9:   n3    NUMBER;

                                                       continues

Listing 4.11. continued

10:  BEGIN
11:   --Here are some common date formats which you might encounter.
12:   d1 := TO_DATE(`1/1/02','mm/dd/yy');
13:   d2 := TO_DATE(`1-1-1998','mm-dd-yyyy');
14:   d3 := TO_DATE(`Jan 1, 2000','mon dd, yyyy');
15:   --Year 2000 problems? Note the effect of using rr instead of yy.
16:   d4 := TO_DATE(`1/1/02','mm/dd/rr');
17:   DBMS_OUTPUT.PUT_LINE(`d1 = ` || TO_CHAR(d1,'dd-Mon-yyyy'));
18:   DBMS_OUTPUT.PUT_LINE(`d2 = ` || TO_CHAR(d2,'mm/dd/yyyy'));
19:   DBMS_OUTPUT.PUT_LINE(`d3 = ` || TO_CHAR(d3,'Day, Month dd, yyyy'));
20:   DBMS_OUTPUT.PUT_LINE(`d4 = ` || TO_CHAR(d4,'Dy, Mon dd, yyyy'));
21:   --Here are some examples of numeric conversions.
22:   n1 := TO_NUMBER (`123.99','999D99');
23:   n2 := TO_NUMBER (`$1,235.95','$9G999D99');
24:   DBMS_OUTPUT.PUT_LINE(`n1 = ` || TO_CHAR(n1,'999D99'));
25:   DBMS_OUTPUT.PUT_LINE(`n2 = ` || TO_CHAR(n2,'$9G999D99'));
26: END;
27: /

OUTPUT

d1 = 01-Jan-1902
d2 = 01/01/1998
d3 = Saturday , January   01, 2000
d4 = Tue, Jan 01, 2002
n1 =  123.99
n2 =  $1,235.95
PL/SQL procedure successfully completed.

ANALYSIS
Lines 12 through 16 show the TO_DATE function being used to convert some common date formats to date variables. Lines 17 through 20 display these dates and show some more formatting possibilities. Lines 22 through 25 show some examples of conversions between numeric and character datatypes.

Summary

Today you have learned about writing PL/SQL expressions. You have read descriptions of each of the PL/SQL operators, and have seen examples of these operators in action. You have also seen how to write complex expressions and how the rules of operator precedence govern Oracle's evaluation of these expressions. Remember that you can use parentheses when you need to exercise control over a calculation. Most important to remember are the effects of null, or unknown, values on expressions. This is a particularly important subject to keep in mind when writing comparisons for use with IF statements. Mastering this one area will save you countless grief as you write code in the future.

Q&A

Q Why does the expression TRUE AND NULL evaluate to NULL, but the expression TRUE OR NULL evaluates to true?
A This is a good question. To understand the answer, it might help to think in terms of null being an unknown value. The AND operator requires that both its operands be true in order to return a true result. If one of the operands is unknown, then you can't be sure that if it were known it would be true, so AND must evaluate to false in this case. Things are different, however, for the expression TRUE OR NULL. The OR operator only requires one of its operands to be true in order to return a true result. In the case of TRUE OR NULL, you do know that one operand is true. Whether the other operand is true or false or unknown doesn't matter at this point because you have one that you know is true, and one is all you need.
Q Does the IN operator let me do anything that I couldn't do otherwise?
A No, not really, but it does make your code more readable and easier to maintain. The expression x IN (1,3,4,10,30,30,40,100) is equivalent to x=1 OR x=3 OR x=4 OR x=10 OR x=30 OR x=40 OR x=100, but you will probably find the first version a bit easier to read and understand.
Q You said that a statement such as IF X = NULL THEN... would not work as expected, and that IF X IS NULL THEN... should be used instead. Why?
A The first expression will never be true. It will always evaluate to NULL because one of the operands is null and it can never be known if two values are equal when one of the values is unknown. The second expression uses the IS NULL operator, which is designed to check for nulls. It specifically checks to see if the value of X is unknown and evaluates to true if that is the case.
Q When I am comparing strings, especially when comparing a CHAR string to a VARCHAR2 string, is there a convenient way to tell PL/SQL to ignore any trailing spaces in the CHAR string?
A Yes, use the built-in RTRIM function, for example: IF RTRIM(char_string) = varchar2_string then...
Q I'm comparing two dates and only want to know if they are in the same year. Can I use the TRUNC function to accomplish this?
A Yes. By default, the TRUNC function truncates the time portion of a date, but the optional second argument enables you to specify a different point of truncation. To compare only the years, you can write: IF TRUNC(date_1,'yyyy') = TRUNC(date_2,'yyyy') THEN...