The %ROWTYPE attribute in Oracle defines the particular record type of a row in a table.   A common usage of %ROWTYPE attribute is to have variables declared as ROWTYPE of a table to transfer data in and out of a procedure or function.  An IN ROWTYPE parameter of a function or procedure can be set with a default value.  Often, the IN ROWTYPE parameter is defaulted to NULL.  For example,

PROCEDURE proc_foo_rowtype( 
row_a employees%ROWTYPE DEFAULT NULL )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ID = ' || NVL(TO_CHAR(row_a.employeeID), 'NULL'));
DBMS_OUTPUT.PUT_LINE('NAME = ' || NVL(TO_CHAR(row_a.firstName), 'NULL'));
END proc_foo_rowtype;

Given the example above, employee table has two rows: employeeID and firstName.  When the convert record as a list of separated variables (found under Record conversion in Project Settings for SSMA for Oracle) is set to Yes, SSMA will create separate parameters for each row of the employees table.
PROCEDURE dbo.PROC_FOO_ROWTYPE 
@row_a$EMPLOYEEID float(53) = NULL,
@row_a$FIRSTNAME nvarchar(max) = NULL
AS
BEGIN
PRINT 'ID = ' + isnull(CAST(@row_a$EMPLOYEEID AS varchar(max)), 'NULL')
PRINT 'NAME = ' + isnull(CAST(@row_a$FIRSTNAME AS varchar(max)), 'NULL')
END

Note that when the ROWTYPE parameter is defaulted to NULL, SSMA will also have the converted parameters default to NULL as shown above.     

Now, let’s have a little fun by having a ROWTYPE parameter in a nested procedure.  Here’s an example:

PROCEDURE PROC_FOO_OUTER 
IS
empRow employees%ROWTYPE;
 procedure proc_foo_inner( 
row_a IN employees%ROWTYPE default null)
IS

BEGIN
DBMS_OUTPUT.PUT_LINE(‘First name = ' || NVL(TO_CHAR(row_a.FirstName), 'NULL'));
DBMS_OUTPUT.PUT_LINE(‘Last Name = ' || NVL(TO_CHAR(row_a.LastName), 'NULL'));
END proc_foo_inner;
BEGIN 
empRow.LastName := 'Smith';
empRow.FirstName := 'John';

proc_foo_inner();
proc_foo_inner(empRow);

END PROC_FOO_OUTER;
                        

This example is quite straightforward.    Let’s assume there is an employee table with FirstName and LastName columns of nvarchar2(20) and nvarchar2(40) respectively.   The executing this procedure in Oracle would the following result:

First name = NULL

Last name = NULL

First name = John

Last name = Smith

 Now, let’s convert this procedure to SQL Server 2012 using SSMA for Oracle.  We will set the following settings in SSMA as such

  • ·         local modules conversion is set to Inline
  • ·         convert record as a list of separated variables set to Yes

The first setting is to convert the inner procedure into nested block Begin..End.  The second setting will create separate variables for FirstName and LastName.

Below is the result of the conversion.  There are two nested blocks corresponding to the respective inner procedures.  Each nested block contains its own variables for LastName and FirstName.   For the block representing proc_foo_inner(), the two variables are set to NULL.  For the block representing proc_foo_inner(empRow), the variables are set to the proper empRow values.

PROCEDURE dbo.PROC_FOO_OUTER 
AS
BEGIN

DECLARE
@empRow$LASTNAME nvarchar(40),
@empRow$FIRSTNAME nvarchar(20),


SET @empRow$LASTNAME = 'Smith'
SET @empRow$FIRSTNAME = 'John'

BEGIN /* proc_foo_inner() */
DECLARE
@proc_foo_inner$row_a$LASTNAME nvarchar(max)

DECLARE
@proc_foo_inner$row_a$FIRSTNAME nvarchar(max)

SET @proc_foo_inner$row_a$LASTNAME = NULL
SET @proc_foo_inner$row_a$FIRSTNAME = NULL

BEGIN
PRINT 'Last name = ' + isnull(CAST(@proc_foo_inner$row_a$FIRSTNAME AS varchar(max)), 'NULL')
PRINT 'First Name = ' + isnull(CAST(@proc_foo_inner$row_a$LASTNAME AS varchar(max)), 'NULL')
END
END
BEGIN /* proc_foo_inner(empRow) */ 
DECLARE
@proc_foo_inner$row_a$LASTNAME$2 nvarchar(max)

DECLARE
@proc_foo_inner$row_a$FIRSTNAME$2 nvarchar(max)


SET @proc_foo_inner$row_a$LASTNAME$2 = @empRow$LASTNAME
SET @proc_foo_inner$row_a$FIRSTNAME$2 = @empRow$FIRSTNAME

BEGIN
PRINT 'Last name = ' + isnull(CAST(@proc_foo_inner$row_a$FIRSTNAME$2 AS varchar(max)), 'NULL')
PRINT 'First Name = ' + isnull(CAST(@proc_foo_inner$row_a$LASTNAME$2 AS varchar(max)), 'NULL')
END
END
END