Procedures and Functions

Last modified: October 23, 2019

Stored procedure (function) is a set of statements that resides on server side and can be executed. Using dbForge Studio you can create, edit, execute and delete stored procedures.

Creating a Stored Procedure

1. In the Database Explorer window, right-click the Procedures folder, and then click New Procedure. A new template document appears.
2. Fill in a template with statements. dbForge Studio adds the CREATE/REPLACE words for you.
3. Save the document.

Editing a Stored Procedure

1. In the Database Explorer window, expand the Procedures folder.
2. Right-click a required procedure, and then click Edit.

Note

Once you have saved a procedure, its name can not be changed.

To delete a stored procedure, right-click a required procedure, and then click Delete.

Executing a Stored Procedure

1. In the Database Explorer window, expand the Procedures folder.
2. Right-click a required procedure, and then click Execute.

If a procedure accepts parameters you will see the Parameters dialog box where you can set initial values for the procedure arguments. Once the routine is executed, you will see appropriate record in the Output window.

Stored procedures, as well as many other Oracle objects, have status. It means that procedures may be valid or invalid. An invalid procedure probably references invalid objects, or has errors in its text, or has some other problems. For example, a stored procedure may suddenly go invalid if it was working with some tables and those tables were recreated. To fix the problem you have to compile the stored procedure or function. For this, click Compile on the procedure’s shortcut menu. Note that if the procedure is a part of a package, you have to recompile the whole package.

To use default values of stored routine parameters delete the parameters with default values in the Edit Parameters dialog box.

Expanding Stored Procedures

This feature allows to simplify debugging by replacing a call to a stored procedure with the stored procedure body - it takes the content of the stored procedure and substitutes the call in your query to it. To apply this feature to the needed code, select the Convert CALL to Script option on the shortcut menu.