Resolving PL/SQL Delimiter Conflicts in OceanBase Oracle Tenants

The author, a senior database expert with extensive experience in MySQL, PostgreSQL, and MongoDB, encountered a PL/SQL delimiter problem while working on an OceanBase Oracle tenant.

Attempting to generate random data with an INSERT statement revealed that the built‑in package dbms_random was missing, prompting the import of dbms_random.sql and dbms_random_body.sql from the OceanBase admin directory.

After importing, creating a simple function to compute a square root resulted in repeated ORA‑00900 syntax errors when executed in the OceanBase obclient console:

create or replace function tt return number is
  v1 number;
  v2 number;
begin
  v1 := 10;
  v2 := sqrt(-2 * ln(v1)/v1);
  return v2;
end;
/

Running the same function in a native Oracle environment succeeded, indicating the issue was specific to OceanBase.

Further testing showed that simplifying the expression to v2 := sqrt(-2 * ln(v1)); allowed the function to compile, revealing that the default PL/SQL delimiter '/' conflicted with the division operator '/' in the expression.

The correct solution is to change the delimiter before creating the function. Using delimiter // separates the function definition from the division operator, allowing successful creation:

delimiter //
create or replace function tt return number is
  v1 number;
  v2 number;
begin
  v1 := 10;
  v2 := sqrt(-2 * ln(v1)/v1);
  return v2;
end;
//

This adjustment resolves the syntax errors, and the function is created without issues. The article shares this insight for other OceanBase users facing similar PL/SQL delimiter challenges.