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.

DECLARE

start_time CHAR(5);

finish_time CHAR(5);

elapsed_time NUMBER(5);

BEGIN

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

SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual;

-- do something

/* Get system time again. */

SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual;

/* Compute elapsed time in seconds. */

elapsed_time := finish_time - start_time;

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

END;

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
Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

Using %ROWTYPE The %ROWTYPE attribute gives a record type which represents a row in a table (or view). The record can store the whole row of data selected from the table or fetc

Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca