Data type conversion, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Data type conversion

Parameter and keyword description - sql cursor, Parameter and Keyword Descr...

Parameter and Keyword Description: SQL: This SQL is the name of the implicit SQL cursor. %FOUND: This attribute results TRUE if an INSERT, DELETE, or UPDATE state

Advantages of invoker rights, Advantages of Invoker Rights The Invoker-...

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

Components of an object type - parameter self, Parameter SELF in pl/sql ...

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Bulk binds advantages, Bulk Binds advantages In the Embedded Oracle RDB...

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Error handling in pl/sql, Error Handling The PL/SQL makes it easy to de...

Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

Extension and and in sql, Extension and AND in SQL The theory book giv...

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Sql query for testing triggers and stored procedures , Description: Dem...

Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Benefit of the dynamic sql pl sql, Benefit of the dynamic SQL: This pa...

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Using first and last - collection method, Using FIRST and LAST FIRST a...

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Using for update, Using FOR UPDATE If you declare a cursor which will ...

Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd