How To Execute An Oracle Stored Procedure? – Plsql

To execute an Oracle stored procedure in PL/SQL, you need to call the procedure from a PL/SQL block, SQL*Plus, or any Oracle SQL tool that supports executing PL/SQL code. Here’s how you can do it:

1. Executing a Stored Procedure in SQL*Plus or Oracle SQL Developer

In SQL*Plus, Oracle SQL Developer, or similar tools, you can execute a stored procedure using the EXEC command or by calling it within an anonymous PL/SQL block.

Syntax for executing in SQL*Plus:

EXEC procedure_name(arguments);

Example:

Suppose you have a stored procedure named my_procedure that takes two parameters: p_id (an integer) and p_name (a string), and does not return anything (a void procedure).

EXEC my_procedure(1001, 'John Doe');

This will execute the stored procedure with the given arguments.

2. Executing a Stored Procedure in an Anonymous PL/SQL Block

You can also call a stored procedure inside an anonymous PL/SQL block using the BEGIN and END keywords.

Syntax:

BEGIN
    procedure_name(arguments);
END;

Example:

BEGIN
    my_procedure(1001, 'John Doe');
END;
/

• The BEGIN and END keywords define the PL/SQL anonymous block.
• The / at the end of the block is used in SQL*Plus or SQLcl to execute the block.

3. Executing a Stored Procedure with OUT Parameters

If your stored procedure has OUT parameters (i.e., it returns values), you need to use variables to capture the output.

Example:

Suppose you have a stored procedure that takes an IN parameter and returns a result via an OUT parameter.

CREATE PROCEDURE get_employee_name(
    p_id IN NUMBER,
    p_name OUT VARCHAR2
) AS
BEGIN
    SELECT name INTO p_name FROM employees WHERE id = p_id;
END;

To call this procedure and capture the output in an anonymous PL/SQL block:

DECLARE
    v_name VARCHAR2(100);
BEGIN
    get_employee_name(1001, v_name);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

4. Executing a Stored Procedure in a Specific Schema

If the stored procedure belongs to a different schema, you can specify the schema name like this:

EXEC schema_name.procedure_name(arguments);

For example:

EXEC hr.get_employee_name(1001, 'John Doe');

5. Executing a Stored Procedure from a Programming Language (e.g., Java, Python)

You can also execute an Oracle stored procedure from a programming language using database connectors (e.g., JDBC for Java, cx_Oracle for Python). The exact method will depend on the language, but here’s an example using Python with cx_Oracle:

import cx_Oracle

# Establish connection
conn = cx_Oracle.connect('username/password@hostname:port/service_name')
cursor = conn.cursor()

# Call stored procedure
cursor.callproc('my_procedure', [1001, 'John Doe'])

# Close connection
cursor.close()
conn.close()