17.05 - Example: Preventing Memory Overflow Errors for INOUT Parameters - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

If the size of an output value returned to an INOUT parameter is larger than the memory the system had allocated for the input value for that parameter, the CALL request fails and returns an overflow error to the requestor. See Rules For Specifying Input And Output Parameters.

The following examples illustrate this. Suppose you have created an SQL stored procedure named myintz with an INOUT parameter.

Example 1:

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     CALL myintz(32767);

The smallest data type the system can fit 32,767 into is SMALLINT, so the system allocates 2 bytes for the parameter and sets the type as SMALLINT irrespective of the data type assigned to the INOUT parameter when the procedure was created. If this CALL returns a value of 32,768 or more, the system treats the result as a memory overflow for a SMALLINT variable and returns an error.

Example 2:

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     CALL myintz(CAST ('32767' AS INTEGER));

In this case, the system recognizes the input value is an INTEGER value because of the explicit casting, so it allocates 4 bytes for the parameter. Then, when the CALL returns 32,768 to the INOUT parameter, the value is within the scope of an INTEGER, the request completes successfully, and no memory allocation error occurs.

     CALL myintz(cast ('32767' AS INTEGER));
      *** Procedure has been executed.
      *** Total elapsed time was 42 seconds.

Similarly, if the client program code calling a procedure explicitly defines the data type for the parameter, and the specified data type is capable of containing a larger output value than the default type for the input value, the request completes successfully.

The following examples illustrate this using a Java external stored procedure named myint2 called from a Java client application. Note that the basic principle applies for any stored procedure, not just external stored procedures. Assume the value returned to the INOUT parameter is 32,768 in both cases.

Example 3:

Suppose you invoke the Java external stored procedure myint2 as follows:

     stmt.executeUpdate("call myint2(32767);");

The outcome of this call is a memory overflow error, which is identical to that seen in the first example.

Example 4:

Suppose a Java client program invokes a Java external procedure as follows:

      StringBuffer prepCall = new StringBuffer("CALL myint2(?);");

      CallableStatement cStmt = con.prepareCall(prepCall.toString());

      cStmt.setInt(1, integerVar[0]);
      cStmt.registerOutParameter(1, Types.INTEGER);

      // Making a procedure call
      System.out.println("Before executeUpdate()...");

      System.out.println("Value after executeUpdate(): "
                         + cStmt.getInt(1));

      integerVar[0] = cStmt.getInt(1);

This CALL succeeds because the calling Java program explicitly defined the INOUT parameter as an INTEGER data type, so the system allocates an appropriate amount of memory for an INTEGER input value. As a result, no overflow error occurs when the returned value can be contained within the memory allocated to an INTEGER type.