Edit

Share via

O2SS0101: Invalid parameter omitting (Error)

This article covers the reasons why SQL Server Migration Assistant (SSMA) for Oracle can't get a description for packaged function, or a packaged procedure call, when a parameter is omitted.

Background

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 must prefix the function name (through required parameters) along with the package name.

Whenever you invoke a function or procedure that is defined in a package omitting required parameters, SSMA generated an error.

Example

In the following example, you create a package MATH_PKG that contains a function GET_SUM.

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;

Here's an example PL/SQL statement that calls the function:

DECLARE
    addition INT;
BEGIN
    addition := MATH_PKG.GET_SUM();
END;

When SSMA tries to convert the previous statement, it can't resolve the function GET_SUM that is invoked, without the required parameter or wrong number of arguments; hence it generates the following error message:

O2SS0101: Invalid parameter omitting

Possible remedies

Rewrite the code in Oracle. In fact, in Oracle when you call the previous package in the statement, it also raises the PL/SQL error of wrong number or types of arguments, in the call to GET_SUM.

You have to rectify the problem in Oracle itself by assigning an optional value to the parameter within the function, matching the number and types of argument required to invoke the function. To make a parameter optional, you can set a default value. If the value isn't passed, the default value is taken.

In the following rewrite, assign the default value (n1 INT DEFAULT 0) to the first parameter of the 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 now properly converts the code.