Manage stored procedures -

Overview

After you log on to OceanBase Developer Center (ODC), you can click the name of a connection on the homepage to go to the corresponding connection management page. In the left-side navigation pane, you can click Stored Procedure to get a list of stored procedures in the current database.

In the stored procedure list, you can view the Structure tree of an object, or right-click the object to perform some Management actions on the object. You can also double-click the name of the stored procedure in the list to go to the Stored procedure management page.

Structure tree

You can click the expand icon before the name of the target stored procedure in the stored procedure list, and the structure tree of the stored procedure is displayed in a drop-down list, as shown in the following figure.

The structure tree of a stored procedure can have up to three levels. When you right-click an item in the tree, the shortcut menu displays some actions that you can perform on the item. For more information about these actions, see the Management actions section. In this section, you can find the table describing the management options.

Management actions

The names of stored procedure objects support ascending order by the head character by default in the stored procedure list.

Shortcut function

The following functions are provided in the upper right corner of the tree:

Action	Description
Create	Click this option to create stored procedure.
Filter	Click this option to display valid/invalid stored procedures.
Batch Compile	Click this option to batch compile the stored procedure object. Including: compile all objects and compile invalid objects.
Refresh	Click this option to refresh the structure tree after you perform management actions on the stored procedure, so that the structure tree displays the latest information.

Right-click function

Right-click the target object in the structure tree. A shortcut menu appears, displaying some management options provided by ODC for you to conveniently manage the target object. The following table describes the management options.

Action	Description
View	Click this option to go to the Stored procedure management page, where you can view comprehensive information such as the basic information, parameters, and code of the stored procedure.
Create	Click this option to go to the Create Stored Procedure page, where you can create a stored procedure as prompted.
Edit	Click this option to go to the Stored procedure editing page, which displays the statements that define the stored procedure and where you can edit the stored procedure.
Compile	Click this option to compile the stored procedure.
Run	Click this option to execute the stored procedure. Note ODC V4.0.0 and later support setting the parameter value as DEFAULT, NULL or empty string.
Export	Export single table, see Single table export and import.
Download	Download the SQL file for the stored procedure object.
Delete	Click this option to delete the stored procedure.
Refresh	Click this option to refresh the structure tree after you perform management actions on the stored procedure so that the structure tree displays the latest information. Note We recommend that you upgrade to OBServer V2.2.77 or later. This is because some versions of OBServer have been found with PL/SQL debugging issues, which may compromise the OBServer stability. ODC V3.2.2 or earlier does not support the debugging feature when you connect ODC to the target instance by using OBProxy. To use the debugging feature, directly connect ODC to the target instance. ODC V3.2.2 and later support PL/SQL debugging when you connect to OBServer by using OBProxy.

Stored procedure management page

Database objects / / /> Manage stored objects > Parameters

You can double-click the name of a stored procedure in the function list to go to the stored procedure management page. The stored procedure management page displays the following information:

Section	Description
Basic Information	This section displays the basic information about the stored procedure in fields such as Stored Procedure Name, Created By, Creation At, and Last Modified At.
Parameter	This section displays the information about the parameters in fields such as Name, Mode, Data Type, Length, and Default Value.Click the refresh icon to refresh the parameters.
DDL	This section displays the script that defines the stored procedure and provides the Edit, Find, Format, and Refresh buttons. Note Click the Edit icon to go to the PL object editing page.

Stored procedure editing page

Right-click the target item in the stored procedure list. In the menu that appears, click Edit to go to the stored procedure editing page. In the code area of the editing page, the statements that define the stored procedure are displayed. You can edit the statements. In addition, the toolbar provides the following buttons:

Button	Description
Confirm modification	Click this button to apply the current modification.
Compile	Click this button to compile the statements on the current page. Note During the compilation, a code line is highlighted if it contains the name of an external object that cannot be referenced. After the compilation, the Compilation Result tab displays the compilation status. If alerts exist, the alert details are displayed.
Run	Click this button to execute the statements in the code area.
Debug	Click this button to enter the debug mode, which allows you to edit the PL or SQL object in the code area. After the debugging, you can close the debugging window.
Format	Click this button to apply formatting, such as indentation, line break, and keyword highlighting, to the selected SQL statements or all the SQL statements in the current SQL window.
Find and Replace	You can enter text in the search field to find the specific content and enter text in the replacement field to replace the content found.
Undo	Click this button to undo the last operation.
Redo	Click this button to reverse an Undo operation.
Case Sensitivity	The system supports three capitalization options: All Caps, All Lowercase, and Capitalize First Letter. Click the corresponding option to convert the selected statements in the script to the desired capitalization format.
Indent	You can add indents to or delete indents from the statements that you selected.
Comments	You can click Add Comments to convert the statements that you selected into comments or click Delete Comment to convert comments to SQL statements.
IN Value Conversion	Click this button to convert the copied rows or columns into the specified format during queries. After you paste the copied data to the SQL editing area, select the copied data and click IN Value Conversion to convert it into the in('A','B') format. Column values are separated with line breaks. Row values are separated with spaces or tabs.

Stored procedure output page

After the stored procedure is executed, you can view the information of execution results in the Output and DBMS Output tabs.

Tab	Description
Output	Displays the status, parameters, parameter types, parameter values, return type, and return value of the stored procedure.
DBMS Output	Displays DBMS outputs of the stored procedure.