Migrating Oracle to SQL Server using SSMA - Error O2SS0101 A parameter has been omitted, so there is no default value
By Bill Ramos and Badal Bordia, Advaiya Inc.
This blog post covers the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot get description for packaged function or a packaged procedure call when a parameter has been omitted.
A package is an encapsulated collection of related program objects (for example, procedures, functions, variables, constants, cursors, and exceptions) stored together in the database. To execute a function or procedure that is defined in a package, you'll have to prefix the function name (through required parameters) along with the package name
Error O2SS0101 A parameter has been omitted, so there is no default value
Background
Whenever you invoke a function or procedure that is defined in a package with omitting invalid parameter, SSMA generated an error. When no values are passed to a packaged function or a packaged procedure call, the omitted parameters holding no default values causes this error.
Possible Remedies
Consider the below example of Oracle. In this example we have created a package (math_pkg) that contains a function (get_sum)
Package Specification and Package Body
CREATE OR REPLACE PACKAGE math_pkg AS
FUNCTION get_sum (n1 INT, n2 INT DEFAULT 0, n3 INT DEFAULT 0) RETURN INT;
END math_pkg;
CREATE OR REPLACE PACKAGE BODY math_pkg AS
FUNCTION get_sum (n1 INT, n2 INT DEFAULT 0, n3 INT DEFAULT 0) RETURN INT IS
BEGIN
RETURN n1 + n2 + n3;
END;
END math_pkg;
Statement
DECLARE
addition INT;
BEGIN
addition := math_pkg.get_sum();
END;
When SSMA tries to convert the above statement, it does not resolve the operations of called function (get_sum)that is invoked without required parameter passing or wrong number of arguments. Hence generates error “O2SS0101: A parameter has been omitted, so there is no default value: n1”.
![clip_image001[4]](https://msdntnarchive.z22.web.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/83/metablogapi/8787.clip_image0014_27FAE529.jpg "clip_image001[4]")
The solution of the above error is to rewrite the code in Oracle. Actually in Oracle when you call the above package in the statement, it also raises the PL/SQL error of wrong number or types of arguments in call to 'GET_SUM'.
So you have to rectify the problem in Oracle itself by assigning optional value to parameter within a function. By doing this we are matching the number and types of argument required to invoke the function. To make a parameter optional you can set a default value in the case if the value is not passed, the default value will be taken. In Oracle the first two parameters are mandatory and only the last parameter is optional.
Below is the rewrite code of an above Oracle example- In below function defined in a package we are assigning default value (n1 INT DEFAULT 0) to the first parameter of get_sum() function.
CREATE OR REPLACE PACKAGE math_pkg AS
FUNCTION get_sum (n1 INT DEFAULT 0, n2 INT DEFAULT 0, n3 INT DEFAULT 0) RETURN INT;
END math_pkg;
CREATE OR REPLACE PACKAGE BODY math_pkg AS
FUNCTION get_sum (n1 INT DEFAULT 0, n2 INT DEFAULT 0, n3 INT DEFAULT 0) RETURN INT IS
BEGIN
RETURN n1 + n2 + n3;
END;
END math_pkg;
SSMA will now properly convert the code.
Related Errors
There are several other errors related to “Invalid parameter” that you may encounter. These include the following:
· Error O2SS0102 Procedure (function) call is missing a parameter
· Error O2SS0104 Unpackaged function call is missing a parameter
References
For more information, check out the following references: