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
DEFINEfollowed by the variable name to list the variable's value and type. - Type
DEFINEwithout 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
SYSDATEandSYSTIMESTAMP - System packages such as
DBMS_OUTPUTandDBMS_RANDOM - Use of the
DUALtable
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