Overloaded programs in a package

PL allows overloaded programs in a package. Overloaded programs are two or more programs that have the same name but have different parameters and variables, parameter sequences, or data types. You can select a program to call based on the type of input parameters.

The following example shows a package specification. Methods of the query_obdept function are selected based on whether the input parameter is of a numeric or string type. Then, features are implemented in the package body definition.

CREATE OR REPLACE PACKAGE obdemo_pack1
IS
    DeptRec obdept%ROWTYPE;
    v_sqlcode NUMBER;
    v_sqlerr    VARCHAR2(2048);
    FUNCTION query_obdept(dept_no IN NUMBER)
        RETURN INTEGER;
    FUNCTION query_obdept(dept_no IN VARCHAR2)
        RETURN INTEGER;
END obdemo_pack1;
/

Notes on client calls to overloaded programs

When calling overloaded stored procedures or functions within a package through client drivers such as JDBC, if the driver does not send the data types of OUT parameters or NULL parameters to OBServer during the Execute phase, or if the OBServer version does not support Prepare with parameter types, the system may be unable to correctly match the appropriate overloaded version based on parameter types.

Before V4.4.2 BP2, in this scenario, the system would select the first program with the same name by default. This could lead to unexpected execution results that are difficult to notice. Starting from V4.4.2 BP2, the system directly reports an error in this scenario, indicating that it cannot determine which overloaded version should be called.

To avoid this issue, we recommend that you:

• Use OceanBase Connector/J V2.4.16 or later.
• Enable the obIncludeOutOrNullParamTypeInfo=true parameter in the JDBC URL.
• Explicitly call registerOutParameter() for OUT parameters, and explicitly call setNull() for NULL parameters while specifying the SQL type.

Here is an example:

// Enable obIncludeOutOrNullParamTypeInfo in the JDBC URL
String url = "jdbc:oceanbase://localhost:2881/test?obIncludeOutOrNullParamTypeInfo=true";
try (Connection conn = DriverManager.getConnection(url, user, password);
     CallableStatement cs = conn.prepareCall("{call obdemo_pack1.get_dept_info(?, ?)}")) {
    // Input a NUMBER type and register the type for the OUT parameter.
    cs.setInt(1, 100);
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.execute();
    String deptName = cs.getString(2);

    // When a NULL parameter is passed in, explicitly specify the SQL type.
    cs.setNull(1, Types.NUMERIC);
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.execute();
}