Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

Conversion of Oracle %TYPE to Microsoft SQL Server

The Oracle %TYPE attribute adapts code as table definitions change. The %TYPE attribute provides the datatype of a variable or database column. If the column's type changes, your variable uses the correct type at run time. This provides data independence and reduces maintenance costs.

Microsoft SQL Server does not support and has no an equivalent of Oracle %TYPE.

SQLWays changes Oracle variable declaration with the %TYPE attribute to the Microsoft SQL Server variable declaration with the same data type as column's data type in the database table or as variable's data type.

Examples of the conversion:

TABLE 25. Using table_name.column_name%TYPE
Oracle
Microsoft SQL Server
CREATE PROCEDURE  ORA_SP_TYPE IS 
v_name  ora.ora_rt.Name%TYPE; 
BEGIN 
   SELECT Name 
      INTO v_name 
      FROM ora_rt 
      WHERE ID = 1; 
END; 
CREATE PROCEDURE ORA.ORA_SP_TYPE  AS 
BEGIN 
   DECLARE @v_name VARCHAR(10) 
   select @v_name = Name FROM ora_rt WHERE ID = 1 
END 
  

Remarks: in the table ora_rt there is column: Name of VARCHAR2(10).

TABLE 26. Using variable%TYPE
Oracle
Microsoft SQL Server
CREATE procedure ora_sp_type1 
  IS 
v_name1  varchar(10); 
v_name  v_name1%TYPE; 
BEGIN 
   SELECT Name 
      INTO v_name 
      FROM ora_rt 
      WHERE ID = 1; 
END; 
CREATE procedure ORA.ORA_SP_TYPE1 
   AS 
BEGIN 
   DECLARE @v_name1  VARCHAR(10) 
   DECLARE @v_name VARCHAR(10) 
   select @v_name = Name FROM ora_rt WHERE ID = 1 
END 
  


Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.