# oceanbase-operations

Part of **OCEANBASE**

# OceanBase Database Utility Operations

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|--------|----------|------|
| Format Datetime | Synchronous | Format datetime values using database functions. |
| Generate Random Number | Synchronous | Generate random numbers using DBMS_RANDOM. |
| Manipulate Large Objects | Synchronous | Manipulate large objects (LOBs) using DBMS_LOB functions. |
| Manage Locks | Synchronous | Manage database locks using DBMS_LOCK. |
| Get Database Timezone | Synchronous | Get the database timezone using the DBTIMEZONE function. |
| Define Column | Synchronous | Define columns using the DEFINE_COLUMN API. |
| Fetch Data with Cursor Attributes | Synchronous | Fetch data using cursor attributes. |
| Enable DBMS Output Buffering | Synchronous | Activate output buffering for debugging purposes. |
| Execute Cursor | Synchronous | Run a cursor to fetch query results. |
| Fetch Row from Cursor | Synchronous | Retrieve a row from an open cursor. |
| Get Last Inserted ID | Synchronous | Retrieve the auto-increment ID of the last inserted row. |
| Get Row Count and Auto Increment | Synchronous | Access information functions to get row count after LIMIT and last auto-increment value. |
| Initialize Random Seed | Synchronous | Set the seed for random number generation. |
| Get Line from Buffer | Synchronous | Read a line from the output buffer. |
| Get Current Date | Synchronous | Get the current date using the CURRENT_DATE function. |
| Perform Date Time Arithmetic | Synchronous | Perform date and time arithmetic using date and time operators. |
| Get Bytes Length | Synchronous | Determine the number of bytes used to store a value using the VSIZE function. |
| Format Numeric Value | Synchronous | Apply formatting rules to numeric values for display purposes. |
| Aggregate Column Values | Synchronous | Concatenate multiple column values into a single string using WM_CONCAT. |
| Calculate Variance | Synchronous | Compute the variance of a dataset using the VARIANCE function. |
| Calculate Dense Rank | Synchronous | Calculate dense rank using the DENSE_RANK function. |
| Select First or Last Value | Synchronous | Retrieve the first or last value from an ordered set using the KEEP function. |
| Conditional Value Comparison | Synchronous | Perform conditional value comparison using the DECODE function. |
| Calculate Exponential | Synchronous | Compute the exponential value of a number using the EXP function. |
| Floor Numeric Value | Synchronous | Round a number down to the nearest integer using FLOOR. |
| Find Maximum Value | Synchronous | Return the greatest value from a list of expressions using GREATEST. |
| InitCap | Synchronous | Convert the first character of each word to uppercase and the rest to lowercase. |
| General SQL Functions | Synchronous | Access a comprehensive set of functions for date/time operations, string manipulation, data type conversion, mathematical calculations, and conditional evaluation. |
| Calculate Cumulative Distribution | Synchronous | Calculate cumulative distribution using the CUME_DIST function. |
| Get First Value in Ordered Set | Synchronous | Retrieve the first value from an ordered data set using the FIRST_VALUE function. |
| Calculate Window Function | Synchronous | Perform analytical calculations using window functions over result sets. |

## API Calling Patterns

### Authentication
OceanBase Database Utility Operations do not require explicit authentication as they are executed within the context of an established database connection. The operations are performed using standard SQL queries and PL/SQL procedures through your existing database client.

- No special headers required
- Authentication is handled by your database connection credentials
- Use standard database connection methods (username/password, SSL certificates, etc.)

### Service Endpoint
OceanBase Database Utility Operations are executed directly against your OceanBase database instance. There is no separate API endpoint - these operations are performed through standard SQL interfaces.

- Connect to your OceanBase database using standard connection methods
- Execute SQL queries and PL/SQL procedures directly
- Operations are available in all OceanBase deployment types (standalone, distributed)

### Synchronous Pattern
All OceanBase Database Utility Operations follow a synchronous calling pattern:

1. Establish a database connection using your preferred client (MySQL client, JDBC, ODBC, etc.)
2. Execute the SQL function or PL/SQL procedure directly
3. Receive the result immediately in the response
4. Handle any errors that may occur during execution

For example:
- Simple functions: `SELECT TO_DATE('31 Aug 2020', 'DD MON YYYY') FROM DUAL;`
- PL/SQL procedures: `BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;`
- Cursor operations: Use DECLARE, OPEN, FETCH, CLOSE blocks in PL/SQL

## Parameter Reference

### Format Datetime

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| format_model | string | true | null | Must be a valid combination of format elements from the table. Total length cannot exceed 22 characters. | The datetime format model to use for conversion. Must match the input string structure exactly. |
| input_string | string | true | null | Must match the format specified by the format_model. Missing or mismatched elements will cause an error. | The input string containing date/time data to be converted. |

### Generate Random Number

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| low | NUMBER | false | null | null | The minimum value of the random number. The number can be equal to the value of this parameter. |
| high | NUMBER | false | null | null | The upper bound of the random number. The number generated is smaller than the value of this parameter. |

### Define Column

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| c | INTEGER | true | null | null | The ID of the cursor for the row that is being defined to be selected. |
| position | INTEGER | true | null | null | The relative position of the column in the row that is being defined. This first column in a statement has position 1. |
| column | NUMBER, VARCHAR2, DATE, BINARY_FLOAT, BINARY_DOUBLE, BLOB, RAW | true | null | null | The value of the column to define. The type of this value determines the type of the column to define. |
| column_size | INTEGER | false | null | null | The maximum size of the column value in bytes. |

### Enable DBMS Output Buffering

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| buffer_size | INTEGER | false | 20000 | range 2,000-1,000,000 or NULL | The maximum amount of buffered information in bytes. To set the buffer size to unlimited, set buffer_size to NULL. |

### Execute Cursor

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| cursor_id | INTEGER | true | null | null | The ID of the cursor that you want to execute. |

### Fetch Row from Cursor

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| c | integer | true | null | null | The ID of the cursor. |

### Get Last Inserted ID

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| identity | Uint | false | 0 | Range: [0, 18446744073709551615] | The last auto-incremented ID value generated in the current session. |

### Initialize Random Seed

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| val | BINARY_INTEGER | true | null | null | The seed used to generate random numbers. |

### Get Line from Buffer

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| line | VARCHAR2 | true | null | Size must be >= 32767 | Returns one line of information from the buffer without the last line break character. You must declare the type of the parameter as VARCHAR2 with a size equal to or greater than 32767 to avoid errors. |
| status | INTEGER | true | null | 0 = success, 1 = end of buffer | If the call is successful, the value of this parameter is 0. If no more lines exist in the buffer, the value of this parameter is 1. |

### Get Bytes Length

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| X | any | true | null | null | The value for which the byte size is calculated. Can be any data type, but typically used with string values. |

### Format Numeric Value

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| number | numeric | true | null | null | The numeric value to be formatted. |
| fmt | string | false | null | Must be a valid format string using standard format elements like '9', '0', '.', etc. | The format model used to convert the number to a string. If omitted, the system uses a default format that retains all significant digits. |

### Aggregate Column Values

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| OVER | string | false | null | null | You can use the OVER clause to define a window over the data on which the function operates. |
| measure_expr | any | true | null | null | An expression of any type. Null values in the measure column are ignored. |
| DISTINCT | boolean | false | null | null | If you specify the DISTINCT operator, duplicates are eliminated from the result set. |

### Calculate Variance

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| DISTINCT | keyword | false | null | null | Removes duplicate values from the column and ignores NULL values in the column during the query. |
| UNIQUE | keyword | false | null | null | Removes duplicate values from the column and ignores NULL values in the column during the query. |
| ALL | keyword | false | ALL | null | Retains duplicate values in the column and ignores NULL values in the column during the query. Default value: ALL. |
| expr | expression | true | null | null | The data column or expression of the numeric type, character type, date type, or other types. |
| OVER | clause | false | null | Cannot be used with ORDER BY or WINDOWING clauses if DISTINCT or UNIQUE is specified. | Uses the OVER clause to define a window for calculation. |

### Calculate Dense Rank

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| OVER | clause | true | null | null | Uses the OVER clause to define a window for calculation. |

### Select First or Last Value

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| OVER | clause | false | null | null | You can use the OVER clause to define a window over the data on which the function operates. |
| expr | expression | true | null | null | An expression of any type. Null values in the measure column are ignored. |

### Conditional Value Comparison

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| condition | NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2 | true | null | null | The expression to compare against the search values. |
| search 1...search n | NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2 | true | null | null | The values to compare with the condition. These cannot be conditional expressions. |
| result 1...result n | NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2 | true | null | null | The value returned if the corresponding search matches the condition. |
| default | NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2 | true | null | null | The value returned if no search value matches the condition. |

### Calculate Exponential

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| numeric_expression | number | true | null | null | The expression of the exact numeric data types or the approximate numeric data types: NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE. |

### Floor Numeric Value

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| numeric_expression | any numeric type | true | null | null | The expression of the exact numeric data types or the approximate numeric data types: NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE. |

### Find Maximum Value

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| expr | expression or expression list | true | null | null | An expression or an expression list. The data type can be NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB. |

### InitCap

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| c1 | string | true | null | null | The string to be processed. The string type can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. |

### General SQL Functions

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| str | string | false | null | null | The input string to be processed by the function. |
| expr | expression | false | null | null | An expression that is evaluated by the function. |
| N | integer | false | null | null | A numeric value used as an argument in various functions. |
| scale | integer | false | 0 | 0 to 6 | Specifies the precision of fractional seconds in time-related functions. |
| unit | string | false | null | MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH | Specifies the unit of time interval for date/time arithmetic functions. |
| format | string | false | null | null | Specifies the output format for date/time formatting functions. |
| pattern | string | false | null | null | A regular expression pattern used for pattern matching functions. |
| position | integer | false | 1 | positive integer | Specifies the starting position for searching within a string. |
| occurrence | integer | false | 1 | integer greater than 0 | Specifies the occurrence number of a match in pattern matching functions. |
| match_param | string | false | null | i (case-insensitive), c (case-sensitive) | Specifies case sensitivity for pattern matching. |
| subexpr | integer | false | 0 | integer >= 0 | Specifies which group of the regular expression to use for matching. |

### Calculate Cumulative Distribution

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| expr | expression | true | null | null | The expression of the NUMBER type or the types that can be implicitly converted to the NUMBER data type. |
| {RESPECT \| IGNORE} NULLS | flag | false | RESPECT NULLS | one of: RESPECT NULLS, IGNORE NULLS | Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration. |

### Get First Value in Ordered Set

| Parameter | Type | Required | Default | Constraints | Description |
|------|------|------|--------|------|------|
| expr | any | false | null | null | The parameter type is not limited. |
| OVER | clause | true | null | null | Uses the OVER clause to define a window for calculation. |
| {RESPECT \| IGNORE} NULLS | flag | false | RESPECT NULLS | one of: RESPECT NULLS, IGNORE NULLS | Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration. |
| FROM { FIRST \| LAST } | direction | false | FIRST | one of: FIRST, LAST | Specifies whether the calculation starts from the first or last row of the window. The default value is FROM FIRST. If you specify IGNORE NULLS, FIRST_VALUE returns the first non-null value in the set. If all the values are NULL, NULL is returned. |

## Code Examples

### Format Datetime - SQL - all

```sql
SELECT TO_DATE( '31 Aug 2020', 'DD MON YYYY' ) FROM DUAL;

+----------------------------------+
| TO_DATE('31AUG2020','DDMONYYYY') |
+----------------------------------+
| 2020-08-31 00:00:00              |
+----------------------------------+
```

### Generate Random Number - SQL - all

```sql
SELECT DBMS_RANDOM.VALUE FROM DUAL;

SELECT DBMS_RANDOM.VALUE(10, 20) FROM DUAL;
```

### Get Database Timezone - SQL - all

```sql
SELECT DBTIMEZONE FROM DUAL;
```

### Define Column - JavaScript - all

```javascript
DBMS_SQL.DEFINE_COLUMN (c IN INTEGER,
                          position IN INTEGER,
                          column IN NUMBER);

DBMS_SQL.DEFINE_COLUMN (c IN INTEGER,
                          position IN INTEGER,
                          column IN VARCHAR2);

DBMS_SQL.DEFINE_COLUMN (c IN INTEGER,
                          position IN INTEGER,
                          column IN VARCHAR2,
                          column_size IN INTEGER);

DBMS_SQL.DEFINE_COLUMN(c in integer, 
                          position in integer, 
                          column in date);

DBMS_SQL.DEFINE_COLUMN(c in integer, 
                          position in integer, 
                          column in binary_float);

DBMS_SQL.DEFINE_COLUMN(c in integer, 
                          position in integer, 
                          column in binary_double);

DBMS_SQL.DEFINE_COLUMN(c in integer, 
                          position in integer, 
                          column in blob);

DBMS_SQL.DEFINE_COLUMN(c in integer, 
                          position in integer, 
                          column in raw,
                          column_size IN INTEGER);
```

### Fetch Data with Cursor Attributes - JavaScript - all

```javascript
obclient>CREATE TABLE employees (
      employee_id INT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      manager_id INT,
      salary NUMERIC
    );
Query OK, 0 rows affected (0.05 sec)

obclient>INSERT INTO employees VALUES(111, 'DEL', 'FA BEN', 1, 1500);
Query OK, 1 rows affected (0.05 sec)

obclient>INSERT INTO employees VALUES(112, 'AXEL', 'BELL', 1, 1000);
Query OK, 1 rows affected (0.05 sec)

obclient>INSERT INTO employees VALUES(113, 'CRIS',  'RACHAR', 1, 1000);

Query OK, 1 rows affected (0.05 sec)

obclient> DECLARE
        CURSOR c_emp IS SELECT first_name, salary
            FROM employees where rownum<5;
        v_ename  employees.first_name%TYPE;
        v_sal    employees.salary%TYPE;
     BEGIN
         OPEN c_emp;
         FETCH c_emp INTO v_ename, v_sal;
         WHILE c_emp%FOUND LOOP
             DBMS_OUTPUT.PUT_LINE(v_ename'''s salary is 'to_char(v_sal) );
             FETCH c_emp INTO v_ename, v_sal;
         END LOOP;
         CLOSE c_emp;
END;
/
Query OK, 0 rows affected (0.05 sec)

DEL's salary is 1500
AXEL's salary is 1000
CRIS's salary is 1000
```

### Enable DBMS Output Buffering - SQL - all

```sql
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => NULL);
END;
```

### Get Current Date - JavaScript - all

```javascript
ALTER SESSION SET TIME_ZONE = '-05:00';
SELECT CURRENT_DATE FROM DUAL;
```

### Perform Date Time Arithmetic - JavaScript - all

```javascript
OceanBase (root@oceanbase)> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00                       |
+-------------------------------------------+
1 row in set (0.01 sec)
```

## Response Format

```json
{
  "output_value": "2020-08-31 00:00:00"
}
```

**Key Fields**:
- `output_value` — The formatted datetime value returned by the function

## Error Handling

| Error Code | Description | Recommended Action |
|---------------|--------------------|-----------------------------|
| ORA-01830 | The date format picture ends before converting the entire input string. This occurs when the format model is incomplete or does not match the input string. | Ensure your format model matches the input string structure exactly. Check that all elements in your input string are accounted for in the format model. |
| ERROR-12702 | Invalid NLS parameter string used in SQL function. This happens when attempting to set unsupported language parameters like 'Traditional Chinese'. | Use only supported NLS parameters. Check the documentation for valid language parameter values. |
| ORA-01722 | Invalid number error when the input cannot be converted to a numeric type. | Verify that your input can be properly converted to the expected numeric type. Check for non-numeric characters in numeric fields. |
| ORA-01861 | Invalid format model error when the format string is not valid or contains unsupported characters. | Use only valid format elements in your format string. Refer to the documentation for supported format elements. |
| ORA-00936 | Missing expression error. Occurs if any required parameter is omitted or improperly formatted. | Ensure all required parameters are provided with proper syntax. Check for missing commas or parentheses. |
| 1166 | Unknown column name error when referencing a non-existent column in a function. | Verify that all column names exist in your table schema. Check for typos in column names. |
| 1582 | Incorrect parameter count error when calling a function with insufficient arguments. | Provide the correct number of arguments for the function you're calling. Refer to the function documentation for parameter requirements. |
| 42703 | Invalid column name error when using a column that does not exist in the table. | Ensure all referenced columns exist in the tables you're querying. Check your table schema for the correct column names. |

## Common Questions

Q: How do I handle datetime formatting errors like ORA-01830?
A: This error occurs when your format model doesn't match your input string. Ensure every part of your input string is represented in your format model. For example, if your input is '31 Aug 2020', your format should be 'DD MON YYYY' (not 'DD MON YYY').

Q: What's the difference between DBMS_OUTPUT.PUT_LINE and GET_LINE?
A: PUT_LINE writes output to a buffer, while GET_LINE reads from that buffer. You need to enable buffering with DBMS_OUTPUT.ENABLE first, then use PUT_LINE to write messages, and finally retrieve them with GET_LINE in your application code.

Q: How do I get the last inserted auto-increment ID?
A: Use `SELECT @@identity;` to retrieve the last auto-increment ID generated in your current session. This is equivalent to the last_insert_id variable.

Q: Can I use window functions like DENSE_RANK with partitioning?
A: Yes, you can use the OVER clause with PARTITION BY to apply window functions like DENSE_RANK to specific groups of rows. For example: `DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC)`.

Q: What happens when I pass NULL to the VSIZE function?
A: The VSIZE function returns NULL when the input is NULL. No error is raised - it simply propagates the NULL value as the result.