SQL data types can be directly used in PL. However, PL has different length limits on some variables from those in SQL.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Note
For more information about SQL data types, see Built-in data types.
The following table lists the data types that have different maximum lengths in PL and SQL.
| Data type | Maximum length in PL | Maximum length in SQL |
|---|---|---|
| VARCHAR2 | 32,767 bytes | 4,000 bytes |
| NVARCHAR2 | 32,767 bytes | 4,000 bytes |
| BLOB | 48 M | (4 GB - 1) * database_block_size |
| CLOB | 48 M | (4 GB - 1) * database_block_size |
If you assign a value with a length exceeding the specified length to a variable, the database raises an exception.
obclient> DECLARE
mychar VARCHAR2(3 CHAR);
BEGIN
mychar := 'abc ';
END;
/
OBE-06502: PL/SQL: numeric or value error
For a string object, you can use the RTRIM() function to remove consecutive spaces after the string. Here is an example:
obclient> DECLARE
mychar VARCHAR2(3 CHAR);
BEGIN
mychar := rtrim('abc ');
END;
/
Query OK, 0 rows affected