Compile and debug PL objects

This topic provides information on compiling and debugging PL objects.

Background information

Procedural language (PL) is an extension to SQL. PL incorporates characteristics of programming languages based on SQL statements, organizes data operations and query statements in PL code, and implements complex features by using logic judgment and loops.

You can use PL to program a variety of programs that have advanced features. PL allows you to encapsulate the business logic in a database. This ensures better abstraction and higher security, reduces network interactions, and accelerates calls, thereby improving database performance.

PL program development is important daily work for database service developers. Like the support module for SQL statements, the PL debugging module is also of great significance. Therefore, developers require a PL development section and a debugging feature during programming.

You can use PL statements to compile database objects, such as functions, stored procedures, packages, and triggers.

OceanBase Developer Center (ODC) V2.2.0 and later support the creation, compilation, running, and debugging of PL objects and anonymous blocks. In the editing section of the Anonymous Blocks window, you can compile PL statements and edit and debug the created PL objects. We recommend that you use the latest version of ODC for better user experience.

Prerequisites

• You have installed OceanBase Database V2.2.73 or later, because earlier versions do not support the PL debugging feature. We recommend that you use the latest version of OceanBase Database or versions later than V3.2.3.

• The PL debugging feature is not supported for instances that are connected to OceanBase Cloud.

• The PL debugging feature requires direct connection to OBServer. When using a version later than ODC V3.2.2 and connecting through ODP, ODC will try to directly connect to OBServer. If the connection between ODC and OBServer succeeds, then you can also use the PL debugging feature.

• You have installed the debugging packages such as DBMS_DEBUG and DBMS_OUTPUT in the connected database.

• You have obtained the debugging permissions for OceanBase Database V4.0.0 or later. Before debugging, run the following commands as the system administrator to grant the debugging permissions to the debugging user: GRANT DEBUG CONNECT SESSION TO <user>; GRANT DEBUG ANY PROCEDURE TO <user>;.

PL compilation

Assume that you want to create a stored procedure named PROC_VARCHAR2 in the GSH database.

1. Create a stored procedure in the SQL window.

   

   CREATE OR REPLACE PROCEDURE PROC_VARCHAR2(p1 in VARCHAR2, p2 out VARCHAR2, p3 in out VARCHAR2)
   as
   v1 varchar2(64) := 'hello,oceanbase';
   begin
   dbms_output.put_line(p1);
   dbms_output.put_line(p3);
    p2 := 'hello,odc';
   end;
   

2. In the left-side navigation pane of the SQL development window, click the More icon next to the stored procedure PROC_VARCHAR2 in the list of store procedures, and then select Compile in the drop-down list.

   

3. Compile the PL statement and view the compilation result.

   

PL debugging

1. In the left-side navigation pane of the SQL development window, click the More icon next to the stored procedure PROC_VARCHAR2 in the list of store procedures, and then select Debug in the drop-down list.

   

2. Set the parameters and click OK.

   Notice

   For OceanBase Database V4.0.0 and later, you must obtain the debugging permission before debugging. Here is the syntax: GRANT DEBUG CONNECT SESSION TO GSH; GRANT DEBUG ANY PROCEDURE TO GSH;.

   

3. Go to the debugging page and click Batch Execute to execute the PL statements.

   

References

• Edit and execute SQL statements

• Command-line window

• Edit and export results

• Analyze SQL statements

• Generate mock data

• Manage database changes

• Manage partitions

• Synchronize shadow tables

• Change lock-free structures