DBMS_TYPES-PolarDB(PolarDB)-阿里云帮助中心

DBMS_TYPES is a built-in PL/SQL package that provides type constants and subtype definitions for working with dynamic data types, including REF CURSOR and large objects (LOB).

Prerequisites

Before you use DBMS_TYPES, ensure your cluster meets the following version requirement:

Oracle syntax compatibility 2.0 (minor engine version 2.0.14.17.34.0 or later)

To check the minor engine version, go to the console or run SHOW polardb_version;. To upgrade, see Upgrade the minor engine version.

Benefits

Dynamic type handling: Supports dynamic data types such as REF CURSOR and LOB, enabling dynamic query and large object processing in PL/SQL.
Type constants and subtypes: Provides built-in constants and subtypes for precise control over data type behavior.
Simplified data processing: Reduces the complexity of dynamic query construction and cross-type data transfer.

Notes

Compatibility: DBMS_TYPES is a PL/SQL built-in package in PolarDB for PostgreSQL (Compatible with Oracle), similar to standard implementations in other databases. Ensure your queries and table structures comply with PolarDB usage specifications.
Dynamic cursor type matching: When using dynamic cursors, the query result set structure must match the cursor definition to avoid type mismatch errors.

Reference

Constants

Constant	Description
`DBMS_TYPES.TYPECODE_NUMBER`	Represents the `NUMBER` type
`DBMS_TYPES.TYPECODE_VARCHAR2`	Represents the `VARCHAR2` type
`DBMS_TYPES.TYPECODE_DATE`	Represents the `DATE` type
`DBMS_TYPES.TYPECODE_BLOB`	Represents the `BLOB` type
`DBMS_TYPES.TYPECODE_CLOB`	Represents the `CLOB` type

TYPECODE_BLOB and TYPECODE_CLOB are useful for large object operations. Use them with DBMS_LOB for extended functionality.

Child types

Child type	Description
`SYS_REFCURSOR`	A PL/SQL dynamic cursor type used to dynamically return query result sets from stored procedures

Example

The following example uses DBMS_TYPES.TYPECODE_BDOUBLE to determine the type of a variable at runtime:

DECLARE
    l_typecode PLS_INTEGER;
    v_data ANYDATA := anydata.ConvertBDouble(null);
    v_type ANYTYPE;
BEGIN
    l_typecode := v_data.GETTYPE(v_type);

    IF l_typecode = DBMS_TYPES.TYPECODE_BDOUBLE THEN
        DBMS_OUTPUT.PUT_LINE('It is a double');
    ELSE
        DBMS_OUTPUT.PUT_LINE('It is not a double');
    END IF;
END;

Output:

It is a double