Migrating Oracle to SQL Server using SSMA - Error O2SS0104 Unpackaged function call is missing a parameter

  • Article

By Bill Ramos and Mayank Bhanawat, Advaiya Inc.

This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t support the function statement whenever the number of parameter passed during the function call is less than the expected number of parameters.

Error O2SS0104 Unpackaged function call is missing a parameter

Background

When calling functions in Oracle, you can pass parameters by using:

· Positional notation. Parameters are specified in the order in which they are declared in the procedure.

· Named notation. The name of each parameter is specified along with its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant.

· Mixed notation. The first parameters are specified with positional notation, and then they are switched to named notation for the last parameters .

Whenever parameter is omitted in a stand-alone function call, SSMA generates following error: Error O2SS0104 Unpackaged function call is missing a parameter

Possible Remedies

Consider the example below, in which we have created a Function and passing one default parameter in that Function:

CREATE OR REPLACE FUNCTION MULTIPLICATION (

   a int ,

   b int DEFAULT 1

)

return number

as

begin

 return a*b;

end;

/

select MULTIPLICATION(20) FROM DUAL;

 

When SSMA tries to convert the above statement, it generates following error: “Error O2SS0104 Unpackaged function call is missing a parameter”.

Error_O2SS0104

 

To resolve this error, you must ensure that the SSMA project settings are selected properly. Select the SSMA project settings in the following way:

· Navigate to the Tools menu, and select Project Settings. Then, click the General tab.

· Now, under ‘Statements Conversion’ , select ‘Convert calls to subprograms that use default arguments’ to “YES”.

Error_O2SS104_1 

When you changed the SSMA project setting as mentioned above and execute the statement in SSMA, it get converted successfully as shown below.

Error_O2SS0104_2

 

There are several other errors related to “Function” that you may encounter that are solved in a similar way. These include the following:

· Error O2SS0102: Procedure (function) call is missing a parameter

· Error O2SS0105: Function (procedure) call is missing a parameter

References

For more information, check out the following references:

· Migrating Oracle to SQL Server 2008 White Paper