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
Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Advantages of Invoker Rights The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In su

Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

Literature review

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Using FORALL and BULK COLLECT Together You can unite the BULK COLLECT clause with the FORALL statement, in that case, the SQL engine bulk-binds column values incrementally. In

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for