Conversion Between Datatypes
There are many different situations in which you need to convert data from one datatype to another. You can perform this conversion in two ways:
- Implicitly
By allowing the PL/SQL runtime engine to take its “best guess” at performing the conversion.
- Explicitly
By calling a PL/SQL function or operator to do the conversion.
In this section I will first review how and when PL/SQL performs implicit conversions, and then focus attention on the functions and operators available for explicit conversions.
Implicit Data Conversion
Whenever PL/SQL detects that a conversion is necessary, it attempts to change the values as required to perform the operation. You would probably be surprised to learn how often PL/SQL performs conversions on your behalf. Figure 7-2 shows what kinds of implicit conversions PL/SQL can perform.
With implicit conversions you can specify a literal value in place of data with the correct internal format, and PL/SQL will convert that literal as necessary. In the following example, PL/SQL converts the literal string ‘125’ to the numeric value 125 in the process of assigning a value to the numeric variable:
DECLARE a_number NUMBER; BEGIN a_number := '125'; END;
You can also pass parameters of one datatype into a module and then have PL/SQL convert that data into another format for use inside the program. In the following procedure, the second parameter is a date. When I call that procedure, I pass a string value in the form DD-MON-YY, and PL/SQL converts ...
Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.