how to Execute Stored Procedure in SQL Developer?

You can execute a stored procedure in SQL Developer in two ways. Below are the examples:

1. Execute Stored Procedure Using Run PL/SQL in SQL Developer

1. Open SQL Developer and connect to the Oracle Database.
2. Then left side in Connections pane, expand the schema node in which you want to execute the stored procedure.
3. Then expand the Procedures node and select the stored procedure you want to execute and do the right click on it.
4. Then from the shortcut menu select Run option.
5. The Run PL/SQL window will open.
6. If the stored procedure has some parameters, then specify the parameters above right corner, as shown below in the image. Then click on the OK button to execute. You can notice in the below picture that I specified the parameter file name, but it is showing its name without single quotes in the PL/SQL block section. It will give the error on execution, so it is better to modify the variables in the below PL/SQL block section and click on the OK button to execute.

2. Execute Stored Procedure Using SQL Editor

1. To execute the stored procedure using SQL editor, which is the better approach, follow above mentioned five steps.
2. Then from the Run PL/SQL window copy the code from PL/SQL block section and close the window.
3. Then click on the menu File > New and select SQL file or press Alt+F10 to open SQL worksheet.
4. Then in the SQL editor paste the copied code.
5. Here you can edit the values of the variable, as shown below. Then execute the stored procedure by pressing F5 or F9 or by clicking on the Run button above SQL worksheet.

DECLARE
  I_EMPNO NUMBER;
  O_TOTAL_SALARY NUMBER;
BEGIN
  I_EMPNO := 7654;

  COMPUTE_SAL(
    I_EMPNO => I_EMPNO,
    O_TOTAL_SALARY => O_TOTAL_SALARY
  );
 
  :O_TOTAL_SALARY := O_TOTAL_SALARY;

END;

Output

PL/SQL procedure successfully completed.

The advantage of running a stored procedure in SQL worksheet is, you can see the output whether it is successful or fail, also easily can change parameters to run multiple time, which is time-saving.

See also:

• Learn How to Execute a Procedure in Toad For Oracle
• How to Execute a Procedure Inside a Package in Oracle
• How to Schedule a Job in Oracle SQL Developer?