Support for Oracle-compatible mode in OBClient

OBClient supports connecting to tenants of OceanBase Database in Oracle-compatible mode and provides the following features specific to Oracle-compatible mode.

Overview

When OBClient connects to a tenant in Oracle-compatible mode, it automatically recognizes Oracle syntax features, including:

• Using / as the statement separator (within PL/SQL blocks)
• Oracle-style string and date formats
• Oracle system functions and packages
• PL/SQL syntax support

Support for DBMS_OUTPUT

OBClient supports Oracle's DBMS_OUTPUT package, which can display output information from stored procedures on the client. Use the SET SERVEROUTPUT command to control whether to pull and display DBMS_OUTPUT output after a query:

SET SERVEROUTPUT ON;
SET SERVEROUTPUT OFF;

conn command (Oracle compatibility)

OBClient is compatible with Oracle SQL*Plus's conn command for switching connections.

Syntax

conn [username[/password][@connect_identifier]];

Examples

Switch connections within the same tenant

Switch to another database within the same tenant:

obclient> conn tom@oboraclegbk#obcluster421/tom;

Switch connections across tenants

Switch to another tenant:

obclient> conn SYS@oboracle#obcluster421/WElcome12#_;

SQL*Plus compatible format

Use the SQL*Plus compatible format:

obclient> conn tom/tom;

Description

The conn command is an alias for the connect command and is fully compatible with the syntax of Oracle SQL*Plus. Using the conn command disconnects the current connection and establishes a new one.

define command

The define command is used to define user variables and is compatible with the variable substitution feature of Oracle SQL*Plus.

Syntax

DEFINE [variable] [= value]

Description

• Defines (names) a substitution variable and assigns it a CHAR value.
• Type DEFINE followed by the variable name to list the variable's value and type.
• Type DEFINE without parameters to list the values and types of all substitution variables.

Examples

obclient> DEFINE name = 'Alice';
obclient> SELECT 'Hello, &name' FROM dual;
Hello, Alice

obclient> DEFINE name
DEFINE NAME = "Alice" (CHAR)

obclient> DEFINE
DEFINE _DATE = "2024-01-01" (CHAR)
DEFINE NAME = "Alice" (CHAR)

Use with SET DEFINE

Using the define command together with the SET DEFINE command allows you to control variable substitution behavior. For more information, see SET variables.

/(slash) separator

OBClient supports using / as a statement separator, especially when executing PL/SQL blocks.

Description

• Executes the most recently executed SQL command or the PL/SQL block stored in the SQL buffer.
• The slash command is similar to RUN, but does not list commands.
• Executing SQL commands or PL/SQL blocks using the slash command does not change the current line number in the SQL buffer, unless the buffer contains an error.

Examples

Use / as a separator in a PL/SQL block:

obclient> BEGIN
    ->   DBMS_OUTPUT.PUT_LINE('Hello, OceanBase!');
    -> END;
    -> /
Hello, OceanBase!

PL/SQL procedure successfully completed.

Differences from other separators

• ;: SQL statement separator
• \g: Executes the current SQL statement
• \G: Displays query results vertically
• /: Mainly used as a separator for PL/SQL blocks, compatible with Oracle SQL*Plus

PL/SQL support

OBClient supports PL/SQL syntax in Oracle-compatible mode, including:

• Creating and executing stored procedures, functions, and packages
• Anonymous PL/SQL blocks
• Oracle data types and functions

Example of creating a stored procedure

obclient> CREATE OR REPLACE PROCEDURE hello_world AS
    -> BEGIN
    ->   DBMS_OUTPUT.PUT_LINE('Hello, World!');
    -> END;
    -> /

Procedure created.

obclient> EXEC hello_world;
Hello, World!

PL/SQL procedure successfully completed.

Example of creating a function

obclient> CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)
    -> RETURN NUMBER AS
    -> BEGIN
    ->   RETURN a + b;
    -> END;
    -> /

Function created.

obclient> SELECT add_numbers(10, 20) FROM dual;
ADD_NUMBERS(10,20)
------------------
                30

Oracle syntax compatibility

When OBClient connects to an Oracle-compatible tenant, it automatically recognizes the following Oracle syntax features:

Data types

• Oracle data types such as NUMBER, VARCHAR2, CHAR, DATE, and TIMESTAMP
• Large object types such as BLOB, CLOB, and NCLOB

System functions and packages

• Date and time functions such as SYSDATE and SYSTIMESTAMP
• System packages such as DBMS_OUTPUT and DBMS_RANDOM
• Use of the DUAL table

String and date formats

• Oracle-style string concatenation (||)
• Oracle date format functions (TO_CHAR, TO_DATE, etc.)

Example

obclient> SELECT SYSDATE FROM dual;
SYSDATE
----------
2024-01-01 12:00:00

obclient> SELECT 'Hello' || ' ' || 'World' AS greeting FROM dual;
GREETING
----------
Hello World

obclient> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
----------------------------------------
2024-01-01 12:00:00

References

• Overview of OBClient
• Quick start with OBClient
• Interactive command reference for OBClient
• SET variables