A Comprehensive Guide to Executing Stored Procedures in Oracle SQL Developer: From Basics to Advanced Techniques

In Oracle database development, stored procedures are essential tools for encapsulating business logic, but correctly invoking them in SQL Developer, especially when dealing with complex parameter types, often perplexes developers. Based on actual Q&A data, this article systematically introduces multiple invocation methods and provides a detailed analysis of solutions to common issues.

Basic Methods for Stored Procedure Invocation

Oracle SQL Developer offers several approaches to execute stored procedures, each suited to different scenarios. The most straightforward method is through the Object Navigator: in the left-hand object panel of SQL Developer, locate the target stored procedure, right-click, and select the "Run" option (or use the shortcut Ctrl+F11). This automatically generates a test harness containing the basic invocation code for the stored procedure.

For example, for a simple stored procedure get_maxsal_by_dept(dno number, maxsal out number), the generated test code is:

DECLARE
  DNO NUMBER;
  MAXSAL NUMBER;
BEGIN
  DNO := NULL;

  GET_MAXSAL_BY_DEPT(
    DNO => DNO,
    MAXSAL => MAXSAL
  );
  DBMS_OUTPUT.PUT_LINE('MAXSAL = ' || MAXSAL);
END;

Developers can set input parameter values in the pop-up dialog and then execute. Output results appear in the "Running - Log" panel. This method is particularly suitable for quick testing and debugging, as it automatically handles parameter declaration and basic output.

Challenges with REF CURSOR Parameters

When stored procedures include REF CURSOR type parameters, the situation becomes more complex. REF CURSORs are used to return multi-row result sets, but SQL Developer's default test harness may not directly display this data. For instance, consider a stored procedure that returns a list of employees:

CREATE OR REPLACE PROCEDURE get_account
(
    Vret_val OUT NUMBER,
    Vtran_count IN OUT NUMBER,
    Vmessage_count IN OUT NUMBER,
    Vaccount_id IN NUMBER,
    rc1 IN OUT SYS_REFCURSOR
) AS
BEGIN
  -- Business logic code
  OPEN rc1 FOR SELECT * FROM accounts WHERE account_id = Vaccount_id;
END;

Many developers encounter errors when attempting direct invocation, such as "PLS-00306: wrong number or types of arguments" or "PLS-00201: identifier must be declared." These errors typically stem from improper parameter passing or incorrect usage of SQL*Plus commands.

Correct Usage of SQL*Plus Commands

In the SQL Developer worksheet, traditional SQL*Plus commands can be used to invoke stored procedures, but attention must be paid to the execution mode. SQL*Plus commands (e.g., VAR, EXEC) are not standard SQL statements, so they cannot be executed using the "Execute Statement" (F9) function; instead, the "Run Script" (F5) mode must be used.

A common mistake is receiving an ORA-00900 error when executing VAR rc REFCURSOR alone, as the command is misinterpreted as a SQL statement. The correct approach is to run all SQL*Plus commands as a complete script.

For the aforementioned get_account procedure, the correct invocation script is:

VAR ret1 NUMBER
VAR tran_cnt NUMBER
VAR msg_cnt NUMBER
VAR rc REFCURSOR

EXEC :tran_cnt := 0
EXEC :msg_cnt := 123

EXEC get_account(Vret_val => :ret1, 
                  Vtran_count => :tran_cnt, 
                  Vmessage_count => :msg_cnt, 
                  Vaccount_id => 1,
                  rc1 => :rc)

PRINT tran_cnt
PRINT rc

Key points of this script include: declaring variables for each OUT or IN OUT parameter, referencing these variables with a colon prefix, and displaying results via the PRINT command. Note that stored procedure invocation does not use assignment syntax, as procedures do not return values (unlike functions).

Advanced Debugging and Custom Test Harnesses

For complex stored procedures, SQL Developer's auto-generated test harness may lack flexibility. Developers can manually create custom PL/SQL blocks to better control the execution flow. For example, to handle REF CURSOR output, the following code can be written:

DECLARE
  v_ret_val NUMBER;
  v_tran_count NUMBER := 0;
  v_msg_count NUMBER := 123;
  v_rc SYS_REFCURSOR;
  v_rec accounts%ROWTYPE;
BEGIN
  get_account(Vret_val => v_ret_val,
              Vtran_count => v_tran_count,
              Vmessage_count => v_msg_count,
              Vaccount_id => 1,
              rc1 => v_rc);

  LOOP
    FETCH v_rc INTO v_rec;
    EXIT WHEN v_rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Account ID: ' || v_rec.account_id);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Transaction Count: ' || v_tran_count);
END;

This approach allows for finer result processing and error capture, making it particularly suitable for integration into automated test scripts.

Common Issues and Solutions

In practice, developers frequently encounter the following problems:

1. Parameter Type Mismatches: Ensure that variables of the correct type are provided for each OUT parameter, while IN parameters can be passed as literals.
2. SQL*Plus Command Execution Errors: Always use the "Run Script" mode to execute code blocks containing commands like VAR and EXEC.
3. REF CURSOR Display Issues: Use the PRINT command or custom PL/SQL loops to extract and display cursor data.
4. Error Message Interpretation: ORA and PLS error codes provide detailed diagnostic information; read them carefully to identify the root cause.

By mastering these techniques, developers can efficiently execute various stored procedures in Oracle SQL Developer, whether for simple data retrieval or complex business logic encapsulation.