DBMS_TYPES - PolarDB - Alibaba Cloud Documentation Center

DBMS_TYPES is a built-in package commonly used in PL/SQL that provides operations and data type definitions related to dynamic types, such as REF CURSOR and large objects (LOB). Through the DBMS_TYPES package, you can easily handle advanced PL/SQL data types, enhancing the flexibility and adaptability of your database development.

Prerequisites

The supported versions of PolarDB for PostgreSQL (Compatible with Oracle) are as follows:

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

Note

To view the minor engine version, go to the console to check the minor engine version, or use the SHOW polardb_version; statement. If the minor engine version requirement is not met, upgrade the minor engine version.

Benefits

Dynamic handling types: Provides functional support for dynamic data types (such as dynamic cursor REF CURSOR and large objects LOB), significantly enhancing PL/SQL dynamic processing capabilities.
Type definitions and operations: The package provides built-in constants and subtypes that allow for more flexible control over data type behavior.
Advanced data processing: Simplifies complex data transfer and dynamic query construction, such as creating dynamic result sets and performing cross-platform data operations.

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 that your queries and table structures comply with PolarDB's usage specifications.
Dynamic cursor REF CURSOR: When working with dynamic cursors, ensure that the structure of the query's result set matches the cursor's definition to avoid type mismatch errors.

Reference

DBMS_TYPES provides numerous constants and types that enhance support for dynamic queries and complex data types in PL/SQL programs. Key elements are listed below:

Constants

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

Note

The TYPECODE_BLOB and TYPECODE_CLOB constants are particularly useful when working with large objects and can be combined with other built-in packages (such as DBMS_LOB) for powerful 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 the DBMS_TYPES.TYPECODE_BDOUBLE constant to dynamically determine the type of a variable:

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;

Result:

It is a double