# oceanbase-plsql

Part of **OCEANBASE**

# OceanBase PL/SQL Development and Debugging

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Display PL/SQL Output | Synchronous | Display output from PL/SQL programs using DBMS_OUTPUT. |
| Debug PL Program | Synchronous | Debug PL/SQL programs using DBMS_DEBUG functions. |
| Enable Debug Mode | Synchronous | Enable debug mode using the DEBUG_ON API. |
| Delete Breakpoint | Synchronous | Delete a breakpoint using the DELETE_BREAKPOINT API. |
| Detach Session | Synchronous | Detach a debugging session using the DETACH_SESSION API. |
| Disable Breakpoint | Synchronous | Disable a breakpoint using the DISABLE_BREAKPOINT API. |
| Enable Breakpoint | Synchronous | Set a breakpoint for debugging database code. |
| Get Variable Value | Synchronous | Retrieve the current value of a variable during debugging. |
| Initialize Debug Session | Synchronous | Start a new debugging session. |

## API Calling Mode

### Authentication
No external authentication is required. These are built-in PL/SQL packages (`DBMS_OUTPUT`, `DBMS_DEBUG`) executed directly within an OceanBase database session. Access is controlled by standard database privileges (e.g., `DEBUG CONNECT SESSION`, `DEBUG ANY PROCEDURE`).

### Service Endpoint
These APIs are not RESTful—they are native PL/SQL procedures and functions called via SQL within an OceanBase database session. There is no HTTP endpoint. Execution occurs inside the database engine.

### Synchronous Calling Pattern
All operations follow a synchronous calling pattern:
1. Connect to an OceanBase database using a SQL client (e.g., OBClient, JDBC, ODP).
2. Execute PL/SQL blocks that invoke procedures/functions from `DBMS_OUTPUT` or `DBMS_DEBUG`.
3. Results (e.g., output lines, variable values, session IDs) are returned immediately in the same session.
4. For debugging workflows:
   - One session acts as the **target session** (runs the PL/SQL code to debug).
   - Another session acts as the **debug session** (controls breakpoints, inspects state).
   - The debug session calls `INITIALIZE`, sets breakpoints, and uses `CONTINUE`/`GET_VALUE` to interact with the paused target session.

## Parameter Reference

### Display PL/SQL Output

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| line | string | false | null | max length 32767 characters | The message to be written to the output buffer. |
| buffer_size | integer | false | 1000 | range 1-10000 | The maximum number of lines that can be stored in the output buffer. |

### Debug PL Program

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| breakflags | string | false | null | One or more of: break_next_line, break_any_call, break_any_return, break_return, abort_execution | Flags to control debugging behavior during continuation. Can be combined. |
| info_requested | string | false | null | One or more of: info_getStackDepth, info_getBreakpoint, info_getLineinfo | Flags to request specific runtime information from the debug session. |

### Enable Debug Mode

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| no_client_side_plsql_engine | BOOLEAN | false | TRUE | null | Leave this parameter to its default value unless the debugging session is executed from a client-side PL engine. |
| immediate | BOOLEAN | false | FALSE | null | If the value of this parameter is TRUE, the interpreter immediately switches from regular mode to debug mode for the duration of the call. |

### Delete Breakpoint

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| breakpoint | BINARY_INTEGER | true | null | null | The breakpoint number set by a previous call to SET_BREAKPOINT. |

### Disable Breakpoint

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| breakpoint | BINARY_INTEGER | true | null | null | The breakpoint number set by a previous call to SET_BREAKPOINT. |

### Enable Breakpoint

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| breakpoint | BINARY_INTEGER | true | null | null | The breakpoint number set by a previous call to SET_BREAKPOINT. |

### Get Variable Value

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| variable_name | VARCHAR2 | true | null | null | The name of the variable or parameter. |
| frame# | BINARY_INTEGER | true | null | null | The frame in which the variable or parameter is located. 0 means the current subprogram. |
| scalar_value | OUT VARCHAR2 | true | null | null | The value. |
| format | VARCHAR2 | false | NULL | This version does not support this parameter. | The date format to use. |

### Initialize Debug Session

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| debug_session_id | VARCHAR2 | false | NULL | null | The ID of the target session. A unique ID is generated if the value of this parameter is NULL. |
| diagnostics | BINARY_INTEGER | false | 0 | one of: 0, 1 | Specifies whether to dump the output of diagnostic to the tracefile. Valid values are: 0 (No diagnostic, default) or 1 (Print diagnostic). |

## Code Examples

### Display Debug Messages - SQL - all

```sql
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => 1000);
  DBMS_OUTPUT.PUT_LINE('Debug message: Starting process');
  DBMS_OUTPUT.PUT_LINE('Processing data...');
  -- Simulate some work
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration ' || i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Process completed.');
END;
/

-- Retrieve output
SET SERVEROUTPUT ON;
DECLARE
  output_lines SYS.DBMSOUTPUT_LINES_ARRAY;
  num_lines NUMBER;
BEGIN
  DBMS_OUTPUT.GET_LINES(output_lines, num_lines);
  FOR i IN 1..num_lines LOOP
    DBMS_OUTPUT.PUT_LINE(output_lines(i));
  END LOOP;
END;
/
```

### Enable Debug Mode Immediately - SQL - all

```sql
BEGIN
  DBMS_DEBUG.DEBUG_ON(immediate => TRUE);
END;
```

### Delete a Breakpoint - SQL - all

```sql
BEGIN
  DBMS_DEBUG.DELETE_BREAKPOINT(1);
END;
```

### Initialize Debug Session with Diagnostics - SQL - all

```sql
DECLARE
  session_id VARCHAR2(50);
BEGIN
  session_id := DBMS_DEBUG.INITIALIZE(diagnostics => 1);
  DBMS_OUTPUT.PUT_LINE('Debug session initialized with ID: ' || session_id);
END;
```

### Enable a Previously Disabled Breakpoint - SQL - all

```sql
DBMS_DEBUG.ENABLE_BREAKPOINT (breakpoint => 123);
```

### Function Signature for Variable Inspection - PL/SQL - all

```plsql
DBMS_DEBUG.GET_VALUE (
   variable_name  IN  VARCHAR2,
   frame#         IN  BINARY_INTEGER,
   scalar_value   OUT VARCHAR2,
   format         IN  VARCHAR2 := NULL)
RETURN BINARY_INTEGER;
```

## Response Format

**Key Fields**:
- `output_lines` — Array of strings retrieved from the DBMS_OUTPUT buffer
- `stack_depth` — Current depth of the call stack during debugging
- `breakpoint_number` — Identifier of the breakpoint where execution paused
- `line_info` — Source code location (unit, line number) of current execution point
- `variable_values` — Runtime values of inspected variables
- `debug_session_id` — Unique identifier for the initialized debug session
- `return_value` — Status code from GET_VALUE (e.g., success or error constant)

## Error Handling

| Error Code (Code) | Description (Description) | Recommended Action (Recommended Action) |
|-------------------|----------------------------|----------------------------------------|
| ORA-20000 | Indicates that DBMS_OUTPUT has not been enabled or the buffer is full. | Call `DBMS_OUTPUT.ENABLE` before writing messages; increase buffer size if needed. |
| ORA-20001 | Raised when attempting to retrieve output lines after the session has ended. | Retrieve output within the same session before it terminates. |
| error_no_such_breakpt | The specified breakpoint does not exist. | Verify the breakpoint number was returned by `SET_BREAKPOINT` and hasn’t been deleted. |
| error_idle_breakpt | Cannot delete/disable/enable an unused breakpoint. | Ensure the breakpoint was properly set and is in an active state. |
| error_stale_breakpt | The program unit was redefined since the breakpoint was set. | Recompile the program unit with debug info and reset breakpoints. |
| error_bogus_frame | The specified frame does not exist. | Use valid frame numbers (0 = current); inspect stack depth first. |
| error_no_debug_info | The entrypoint does not have debug information. | Recompile the PL/SQL unit with `DEBUG` flag enabled. |
| error_no_such_object | variable_name does not exist in the frame specified by frame#. | Verify variable name and scope; check for typos. |
| error_unknown_type | Cannot identify the type information in the debug information. | Ensure the PL/SQL unit was compiled with full debug info. |
| error_nullvalue | The value is NULL. | Handle NULL values explicitly in your debug logic. |
| error_indexed_table | The object is a table but no index is provided. | Provide an index when accessing collection elements. |
| ORA-01031 | Insufficient privileges. The caller or debug role must have the DEBUG CONNECT SESSION privilege. | Grant `DEBUG CONNECT SESSION` to the user or debug role. |
| ORA-06512 | Internal error during debug session initialization. | Validate debug role configuration and password. |
| ORA-04031 | Memory allocation failure during session initialization. | Check system memory resources; reduce concurrent sessions. |

## Common Questions

Q: How do I see output from DBMS_OUTPUT.PUT_LINE?
A: You must enable output with `DBMS_OUTPUT.ENABLE` and configure your SQL client to display it (e.g., `SET SERVEROUTPUT ON` in SQL*Plus or compatible clients).

Q: Why do I get "no debug info" errors when trying to debug?
A: Your PL/SQL code must be compiled with debug information. Recreate the procedure/function with the `DEBUG` keyword: `CREATE OR REPLACE PROCEDURE ... DEBUG IS ...`.

Q: Do I need two sessions to debug PL/SQL code?
A: Yes. One session runs the target code (target session), and another controls debugging (debug session). The debug session attaches to the target using `INITIALIZE`.

Q: Are these debugging features available in all OceanBase editions?
A: Yes, DBMS_OUTPUT and DBMS_DEBUG are built-in packages available in all OceanBase Database editions at no extra cost.

Q: What privileges are required to use DBMS_DEBUG?
A: You need `DEBUG CONNECT SESSION` to initialize a debug session. To debug procedures owned by other users, you also need `DEBUG ANY PROCEDURE`.

## Pricing & Billing

### Billing Model
Free. These are built-in database features included with OceanBase Database and do not incur additional usage-based charges.

### Free Tier
Unlimited usage within the context of your OceanBase database instance. No separate free tier limits apply.

### Billing Notes
Debugging and output functions are part of the core database engine and are not billed separately from your database instance usage.