Procedures/Functions with ROWTYPE Parameters Defaulted to NULL

  • Article

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