Data type conversion, PL-SQL Programming

Datatype Conversion

At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character string. The PL/SQL supports both the explicit and implicit (automatic) datatype conversion.

Explicit Conversion

To convert the values from one datatype to other, you use the built-in functions. For e.g. to convert a CHAR  value to a DATE   or NUMBER   value, use the function TO_DATE  or TO_NUMBER, respectively. On the contrary, to convert a DATE or NUMBER value to the CHAR value, you use the function TO_CHAR.

Implicit Conversion

When it makes sense, the PL/SQL can convert the datatype of a value implicitly. This permits you to use the variables, literals, and parameters of one type where the other type is expected. In the example shown below, the CHAR variables start_time and finish_ time hold string values representing the number of seconds in the past midnight. The variation between those values must be assigned to the NUMBER variable elapsed_time. And hence, the PL/SQL converts the CHAR values to the NUMBER values automatically.


start_time CHAR(5);

finish_time CHAR(5);

elapsed_time NUMBER(5);


/* Get system time as seconds past midnight. */


-- do something

/* Get system time again. */


/* Compute elapsed time in seconds. */

elapsed_time := finish_time - start_time;

INSERT INTO results VALUES (elapsed_time, ...);


Before assigning a selected column value to a variable, the PL/SQL will, if necessary then convert the value from the datatype of the source column to the datatype of the variable. This happens, for e.g.  If you select a DATE column value into a VARCHAR2 variable.

Similarly, before assigning the value of a variable to a database column, the PL/SQL will, if necessary then convert the value from the datatype of the variable to the datatype of the target column. If the PL/SQL cannot determine that implicit conversion is required, you get a compilation error. In such situation, you should use a datatype conversion function. The table shows that implicit conversions PL/SQL can do.

566_data type conversion.png

It is your duty to ensure that the values are convertible. For illustration, the PL/SQL can convert the CHAR value '02-JUN-92' to a DATE value but cannot convert the CHAR value 'YESTERDAY' to a DATE value. Likewise, the PL/SQL cannot convert a VARCHAR2 value containing the alphabetic characters to a NUMBER value.

Implicit versus Explicit Conversion

Normally, to rely on the implicit datatype conversions is a poor programming practice as they can hamper the performance and might change from one software release to the next. Also, the implicit conversions are context sensitive and hence not always predictable. Rather, use datatype conversion functions. In that way, your applications will be easier and reliable to maintain.

Posted Date: 10/2/2012 2:41:38 AM | Location : United States

Related Discussions:- Data type conversion, Assignment Help, Ask Question on Data type conversion, Get Answer, Expert's Help, Data type conversion Discussions

Write discussion on Data type conversion
Your posts are moderated
Related Questions
Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor

Recursion The Recursion is a powerful method for simplify the design of the algorithms. Principally, the recursion means the self-reference. In the recursive mathematical serie

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

Projection in SQL - correct version Student StudentId is enrolled on some course. SELECT DISTINCT StudentId FROM IS_ENROLLED_ON In more complicated examples it is someti