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.
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
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
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”.
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”.
When you changed the SSMA project setting as mentioned above and execute the statement in SSMA, it get converted successfully as shown below.
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
For more information, check out the following references:
· Migrating Oracle to SQL Server 2008 White Paper