How to run a stored procedure in Oracle SQL Developer?

Executing Stored Procedures in Oracle SQL Developer

Learn how to effectively run and manage stored procedures within Oracle SQL Developer, from basic execution to handling parameters and viewing results.

Stored procedures are powerful tools in Oracle databases, allowing you to encapsulate complex business logic, improve performance, and enhance security. Oracle SQL Developer provides a user-friendly interface to interact with these procedures. This article will guide you through the process of executing stored procedures, understanding their parameters, and interpreting the results.

Understanding Stored Procedures and Their Components

Before executing a stored procedure, it's crucial to understand its definition, including its name, parameters (input, output, and in-out), and return types. A stored procedure is a named PL/SQL block that is stored in the database and can be called repeatedly. Unlike functions, procedures do not necessarily return a value, but they can modify data and use OUT or IN OUT parameters to pass data back to the caller.

flowchart TD
    A[Start] --> B{Procedure Call};
    B --> C{Check Parameters};
    C -- Valid --> D[Execute PL/SQL Logic];
    D --> E{Handle OUT/IN OUT Parameters};
    E --> F[Commit/Rollback (if applicable)];
    F --> G[End Procedure];
    C -- Invalid --> H[Error Handling];
    H --> G;

General flow of a stored procedure execution

Executing a Stored Procedure Without Parameters

For stored procedures that do not require any input parameters, the execution is straightforward. You can call them directly using a simple EXEC command or by right-clicking the procedure in the SQL Developer navigator.

EXEC my_schema.my_procedure_no_params;
-- Or, if the procedure is in your default schema:
EXEC my_procedure_no_params;

Executing a stored procedure without parameters using EXEC

Executing Stored Procedures with Parameters

Most stored procedures accept one or more parameters. These can be IN (input), OUT (output), or IN OUT (both input and output). When executing a procedure with parameters, you need to provide values for IN and IN OUT parameters and declare variables to capture OUT and IN OUT values.

DECLARE
    v_output_message VARCHAR2(100);
    v_input_id NUMBER := 101;
BEGIN
    my_schema.my_procedure_with_params(
        p_input_id => v_input_id,
        p_output_msg => v_output_message
    );
    DBMS_OUTPUT.PUT_LINE('Output Message: ' || v_output_message);
END;
/

Executing a stored procedure with IN and OUT parameters in an anonymous PL/SQL block

1. Locate the Stored Procedure

In SQL Developer, expand your database connection, then navigate to 'Procedures' or 'Packages' (if the procedure is part of a package). Find the stored procedure you wish to execute.

2. Open the Run/Debug Dialog

Right-click on the stored procedure name and select 'Run...' or 'Debug...'. This will open a dialog box where you can specify parameter values.

3. Enter Parameter Values

For each IN or IN OUT parameter, enter the appropriate value in the 'Value' column. For OUT parameters, SQL Developer will automatically generate a placeholder variable. You can also modify the data type if needed.

4. Execute the Procedure

Click the 'OK' button. SQL Developer will generate and execute an anonymous PL/SQL block in a new SQL Worksheet. The results, including any DBMS_OUTPUT messages and OUT parameter values, will be displayed in the 'Script Output' or 'DBMS Output' pane.

5. Review Results

Examine the output for any error messages, DBMS_OUTPUT messages, and the values returned by OUT or IN OUT parameters. If the procedure performs DML operations, check the affected tables.