Conversion of Oracle %TYPE to Microsoft SQL Server

Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.

You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.

Check out the relevant toolkit documentation.

Ispirer Oracle to SQL Server Migration overview.

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 
  