how to run stored proc in oracle sql developer tool

How to Run a Stored Procedure in Oracle SQL Developer Tool

Oracle SQL Developer is a powerful tool that allows users to interact with their Oracle database using a graphical user interface. One of the many features it offers is the ability to run stored procedures, which are reusable blocks of code that can be executed multiple times with different parameters. In this article, we will explore how to run a stored procedure in Oracle SQL Developer.

To get started, you need to open SQL Developer and connect to your Oracle database. You may need to unlock the HR account as a user with DBA privileges if it is locked. Once you have connected to your database, you can create a new PL/SQL stored function by clicking on the 'New' button in the toolbar or by using the keyboard shortcut Ctrl+N.

In the 'Create Stored Function' dialog box, enter a name for your function and select the return type. For this example, we will use the name 'TOTAL_REMUNERATION' and the return type 'NUMBER'. In the 'Function Body' text area, enter the following code:

```

CREATE OR REPLACE FUNCTION TOTAL_REMUNERATION(p_employee_id NUMBER)

RETURN NUMBER

v_total_remuneration NUMBER;

BEGIN

SELECT SUM(salary) + (nvl(commission, 0)) INTO v_total_remuneration

FROM employees

WHERE employee_id = p_employee_id;

RETURN v_total_remuneration;

END TOTAL_REMUNERATION;

```

This code creates a stored function that calculates the total remuneration for an employee based on their salary and commission. The 'CREATE OR REPLACE' statement is used to create or replace the existing function with the same name.

To run this stored procedure, you need to open a SQL Worksheet or SQL*Plus and execute the following grant statement:

```

GRANT CREATE PROCEDURE TO HR;

```

This grants the create procedure privilege to the HR user. You can then call the stored procedure by executing the following code in your SQL Worksheet or SQL*Plus:

```

BEGIN

DBMS_OUTPUT.PUT_LINE(TOTAL_REMUNERATION(100));

END;

```

This will execute the stored procedure and display the total remuneration for employee 100.

In conclusion, running a stored procedure in Oracle SQL Developer is a straightforward process that requires creating a PL/SQL stored function and granting the necessary privileges. By following these steps, you can reuse your business logic code and improve performance by reducing the amount of data transferred between the database and client programs.

How to run stored proc in oracle sql developer tool