# oceanbase-data_management

Part of **OCEANBASE**

# OceanBase Data Management

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Convert Data Types | Synchronous | Convert between different data types using implicit or explicit conversion functions like TO_CHAR, TO_NUMBER, and HEXTORAW. |
| Manage LOB Data | Synchronous | Manage large object (LOB) data using the DBMS_LOB package for BLOBs and CLOBs. |
| Erase LOB Data | Synchronous | Remove data from a LOB column using the ERASE stored procedure. |
| Get LOB Length | Synchronous | Retrieve the length of a LOB value in bytes or characters. |
| Write LOB Data | Synchronous | Write data to LOB storage locations, overwriting existing content at a specified offset. |
| Append Data to LOB | Synchronous | Add data to the end of an existing LOB using the WRITEAPPEND procedure. |
| Manipulate Raw Data | Synchronous | Perform bitwise operations and conversions on RAW binary data using UTL_RAW functions. |
| Encode Data | Synchronous | Convert data between encoding formats (Base64, URL, MIME) using UTL_ENCODE functions. |
| Convert Hex to Raw | Synchronous | Transform a hexadecimal string into its raw binary representation using HEXTORAW. |
| Define FLOAT Column | Synchronous | Create a table column with the FLOAT data type and specify binary precision. |
| Convert Timestamp with Timezone | Synchronous | Associate a time zone with a TIMESTAMP value using the FROM_TZ function. |
| Get Current Timestamp with Timezone | Synchronous | Get the current timestamp in the session’s time zone with optional fractional second precision. |

## API Calling Patterns

### Authentication Method
OceanBase Data Management functions are executed directly within the database via SQL or PL/SQL. No external API authentication is required. Access is controlled through standard database user permissions and connection credentials.

- Use your OceanBase database username and password when connecting via JDBC, ODP, or other database clients.
- Environment variables like `OB_USER` and `OB_PASSWORD` may be used by client applications (not standardized by OceanBase).

### Service Endpoint
These functions are not REST APIs but built-in SQL/PL/SQL features of the OceanBase database engine. They are invoked through:

- Direct SQL queries (`SELECT`, `INSERT`, etc.)
- PL/SQL blocks or stored procedures
- Database clients (e.g., OBClient, JDBC, ODP)

No HTTP endpoints are involved. Execution occurs within the context of a connected database session.

### Synchronous Pattern
All data management operations are **synchronous**:
1. Submit a SQL statement or PL/SQL call that uses one of the supported functions (e.g., `DBMS_LOB.WRITE`, `UTL_ENCODE.BASE64_ENCODE`, `FROM_TZ`).
2. The database executes the operation immediately within the transaction context.
3. Results (or errors) are returned as part of the query response.

Example flow for writing LOB data:
```sql
DECLARE
  l_clob CLOB;
BEGIN
  SELECT my_clob_col INTO l_clob FROM my_table WHERE id = 1 FOR UPDATE;
  DBMS_LOB.WRITE(l_clob, 5, 1, 'Hello');
  COMMIT;
END;
```

## Parameter Reference

### Erase LOB Data

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| lob_loc | BLOB or CLOB | Yes | — | — | The locator of the LOB to be erased. |
| amount | INTEGER | Yes | — | ≥ 1; actual erased amount may differ if end of LOB is reached | Amount of data to erase (bytes for BLOB, characters for CLOB). |
| offset | INTEGER | No | 1 | ≥ 1 and ≤ LOBMAXSIZE | Absolute offset from start of LOB (1-based). |

### Write LOB Data

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| lob_loc | IN OUT NOCOPY BLOB or CLOB | Yes | — | — | Locator for the internal LOB to write to. |
| amount | INTEGER | Yes | — | ≥ 1 and ≤ 32767 | Amount of data to write (bytes for BLOB, characters for CLOB). |
| offset | INTEGER | Yes | — | ≥ 1 and ≤ LOBMAXSIZE | Offset from start of LOB (1-based). |
| buffer | RAW or VARCHAR2 | Yes | — | — | Buffer containing data to write. |

### Append Data to LOB

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| lob_loc | IN OUT NOCOPY BLOB or CLOB | Yes | — | — | Locator of the internal LOB to append to. |
| amount | INTEGER | Yes | — | 1–32767 bytes/characters | Amount of data to append. |
| buffer | RAW or VARCHAR | Yes | — | If amount > buffer size, error; if less, only part is written | Buffer for the write operation. |

### Convert Hex to Raw

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| char | string | Yes | — | — | Hexadecimal string (CHAR, VARCHAR2, NCHAR, or NVARCHAR2). |

### Define FLOAT Column

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| p | integer | No | — | 1–126 | Binary precision for the FLOAT column. |

### Convert Timestamp with Timezone

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| timestamp_value | TIMESTAMP | Yes | — | — | A TIMESTAMP value without time zone. |
| time_zone_value | string | Yes | — | — | Time zone string (e.g., '-03:00', 'UTC'). |

### Get Current Timestamp with Timezone

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| precision | integer | No | 6 | 0–9 | Precision of fractional seconds. |

## Code Examples

### Implicit Data Type Conversion - JavaScript - all

```javascript
SELECT 5 * 10 + '2' FROM  DUAL;
```

### Explicit Date Formatting - JavaScript - all

```javascript
SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD') FROM DUAL;
```

### Hex to Raw Conversion - SQL - all

```sql
select HEXTORAW('A123')  from dual;
```

### FLOAT Column Definition and Usage - JavaScript - all

```javascript
CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));
INSERT INTO test VALUES (1.23, 1.23);
INSERT INTO test VALUES (7.89, 7.89);
INSERT INTO test VALUES (12.79, 12.79);
INSERT INTO test VALUES (123.45, 123.45);

SELECT * FROM test;
```

### Timestamp with Time Zone - SQL - all

```sql
SELECT FROM_TZ(TIMESTAMP '2020-03-28 08:00:00', '-03:00') FROM DUAL;
```

### Current Timestamp with Session Time Zone - JavaScript - all

```javascript
ALTER SESSION SET TIME_ZONE = '+08:00';
SELECT CURRENT_TIMESTAMP(3) FROM DUAL;
```

## Response Format

### Get LOB Length

```json
12345
```

**Key Fields**:
- `length` — The length of the LOB in bytes (BLOB) or characters (CLOB), including zero-byte fillers.

### Convert Hex to Raw

```json
"+------------------+\n| HEXTORAW('A123') |\n+------------------+\n| A123             |\n+------------------+"
```

**Key Fields**:
- `HEXTORAW('A123')` — The resulting RAW value displayed in hexadecimal format.

### Define FLOAT Column

```json
"+-----------+----------+\n| col1          |   col2   |\n+-----------+----------+\n|    1.23   |   1.2    |\n+-----------+----------+\n|    7.89   |   7.9    |\n+-----------+----------+\n|    12.79  |   13     |\n+-----------+----------+\n|    123.45 |   120    |\n+-----------+----------+"
```

**Key Fields**:
- `col1` — Original NUMBER value.
- `col2` — FLOAT value with precision-based rounding.

### Convert Timestamp with Timezone

```json
"+------------------------------------------------+\n| FROM_TZ(TIMESTAMP'2020-03-2808:00:00','-03:00') |\n+------------------------------------------------+\n| 2020-03-28 08:00:00.000000000 -03:00           |\n+------------------------------------------------+"
```

**Key Fields**:
- `FROM_TZ(...)` — Resulting TIMESTAMP WITH TIME ZONE value.

### Get Current Timestamp with Timezone

```json
"+------------------------------------+\n| CURRENT_TIMESTAMP                  |\n+------------------------------------+\n| 2020-03-08 01:49:31.219066 -05:00  |\n+------------------------------------+"
```

**Key Fields**:
- `CURRENT_TIMESTAMP` — Current timestamp in session time zone with fractional seconds and offset.

## Error Handling

| Error Code (Code) | Description (Description) | Recommended Action (Recommended Action) |
|-------------------|----------------------------|----------------------------------------|
| VALUE_ERROR | Thrown when any input parameter is NULL. | Ensure all required parameters are non-NULL before calling the procedure. |
| INVALID_ARGVAL | Thrown when offset or amount is out of valid range: offset < 1 or > LOBMAXSIZE, or amount < 1 or > LOBMAXSIZE. | Validate offset and amount against LOB size and allowed ranges (e.g., amount ≤ 32767 for WRITE/WRITEAPPEND). |
| QUERY_WRITE | Thrown when LOB write is not allowed within a query context. | Perform LOB write operations outside of SELECT statements (e.g., in PL/SQL blocks or DML with proper locking). |
| BUFFERING_ENABLED | Thrown when the operation cannot be performed because LOB buffering is enabled. | Disable LOB buffering or use compatible operations; consult DBMS_LOB documentation for buffering rules. |
| SECUREFILE_OUTOFBOUNDS | A write operation past the end of a LOB having FRAGMENT_* was attempted. | Ensure write offset and amount do not exceed allocated LOB storage limits. |

## FAQ

Q: Do I need an API key to use OceanBase data management functions?
A: No. These are built-in SQL and PL/SQL functions executed within the database. Authentication is handled via standard database login credentials.

Q: Can I use these functions in SELECT statements?
A: Read-only functions like GETLENGTH, HEXTORAW, FROM_TZ, and CURRENT_TIMESTAMP can be used in SELECT. Write operations (WRITE, WRITEAPPEND, ERASE) require PL/SQL or DML contexts and cannot run inside queries due to the QUERY_WRITE restriction.

Q: What is the maximum size of data I can write to a LOB in one operation?
A: For WRITE and WRITEAPPEND, the `amount` parameter must be between 1 and 32,767 bytes (for BLOB) or characters (for CLOB).

Q: Are these functions available in both Oracle and MySQL modes of OceanBase?
A: Most functions described (DBMS_LOB, UTL_RAW, UTL_ENCODE, FROM_TZ) are available in Oracle mode. MySQL mode has different built-in functions; check compatibility if using MySQL mode.

Q: How does FLOAT precision work in OceanBase?
A: FLOAT(p) uses binary precision (1–126 bits), which is converted to approximate decimal precision. For example, FLOAT(5) provides about 2-digit decimal precision, leading to rounding as shown in examples.

## Pricing & Billing

### Billing Model
All data management functions are part of the core OceanBase database engine and incur **no additional charges**. Usage is included in your standard OceanBase database licensing or instance fees.

### Free Tier
No separate free tier applies—these functions are freely usable within any OceanBase deployment (self-managed or cloud).

### Usage Limits
No specific quotas or rate limits are imposed on these functions beyond general database resource constraints (CPU, memory, I/O). LOB operations are subject to standard transaction and storage limits of your OceanBase instance.