Migrating Oracle to SQL Server using SSMA - Error O2SS0104 Unpackaged function call is missing a parameter
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”.
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.
Related Errors
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: