How to Create Stored Procedures in Oracle

Galaxy Glossary

How do I create and use stored procedures in Oracle?

An Oracle stored procedure is a pre-compiled PL/SQL block that can be invoked by name to run multiple SQL statements in one call.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is an Oracle stored procedure?

An Oracle stored procedure is a precompiled PL/SQL program saved in the database. It can be called by name to run multiple SQL statements and business logic in a single round-trip.

Why use stored procedures for ecommerce data?

Ecommerce systems benefit from procedures by encapsulating order processing, stock adjustments, and reporting logic.This reduces duplicated SQL in application code and improves performance thanks to server-side execution.

How do I create a simple stored procedure?

Use CREATE OR REPLACE PROCEDURE, declare parameters, place SQL between BEGIN and END, then compile. Oracle stores the compiled code in the data dictionary.

Step-by-step example

1. Open your SQL client.
2. Write the procedure definition shown below.
3. Run it to compile.
4.Call the procedure with EXEC or within PL/SQL blocks.

How do I pass parameters to a procedure?

Add IN, OUT, or IN OUT parameters in the header. IN passes values, OUT returns values, IN OUT does both. Default values simplify calls.

How do I execute a stored procedure?

Use EXEC procedure_name(value1, value2) inside SQL*Plus or CALL within JDBC.You can also invoke it inside another PL/SQL block.

How do I update data inside a procedure?

Place INSERT, UPDATE, and DELETE statements inside the BEGIN...END block. Commit logic can be explicit (COMMIT) or rely on the caller’s transaction control.

Best practices for production

Keep procedures small, name them verb-first, validate inputs, use exceptions for error handling, and grant EXECUTE privileges to least-privilege roles.

Common pitfalls and fixes

Watch for unhandled exceptions and implicit commits after DDL.Always test parameter modes and transaction scopes in a staging environment.

.

Why How to Create Stored Procedures in Oracle is important

How to Create Stored Procedures in Oracle Example Usage


-- Call from SQL*Plus or SQL Developer
VARIABLE new_id NUMBER;
EXEC create_order(p_customer_id => 42, p_product_id => 9, p_quantity => 2, p_order_id => :new_id);
PRINT new_id;

How to Create Stored Procedures in Oracle Syntax


CREATE [OR REPLACE] PROCEDURE procedure_name
    (parameter_name datatype [IN | OUT | IN OUT] [DEFAULT default_val], ...)
AUTHID CURRENT_USER -- optional
IS -- or AS
    -- variable declarations
BEGIN
    -- SQL and PL/SQL statements
EXCEPTION
    WHEN others THEN
        -- error handling
END procedure_name;
/

-- Ecommerce example
CREATE OR REPLACE PROCEDURE create_order (
    p_customer_id   IN  Orders.customer_id%TYPE,
    p_product_id    IN  Products.id%TYPE,
    p_quantity      IN  OrderItems.quantity%TYPE,
    p_order_id      OUT Orders.id%TYPE
) IS
BEGIN
    INSERT INTO Orders(id, customer_id, order_date, total_amount)
    VALUES (Orders_seq.NEXTVAL, p_customer_id, SYSDATE, 0)
    RETURNING id INTO p_order_id;

    INSERT INTO OrderItems(id, order_id, product_id, quantity)
    VALUES (OrderItems_seq.NEXTVAL, p_order_id, p_product_id, p_quantity);

    UPDATE Products
    SET stock = stock - p_quantity
    WHERE id = p_product_id;
END create_order;
/

Common Mistakes

Skipping EXECUTE privilege grants: procedures compile but applications fail with ORA-06550. Fix by GRANT EXECUTE ON procedure_name TO app_role;.
Ignoring exception handling: unhandled errors roll back the caller’s transaction. Wrap logic in BEGIN…EXCEPTION…END and log errors with PRAGMA AUTONOMOUS_TRANSACTION.

Frequently Asked Questions (FAQs)

Can I debug an Oracle stored procedure?

Yes. Use DBMS_OUTPUT for simple prints or attach a debugger in SQL Developer to set breakpoints and inspect variables.

Do procedures run in their own transaction?

No. They run inside the caller’s session. Commit or rollback outside unless the procedure contains explicit COMMIT or ROLLBACK.

How do I change a procedure without breaking callers?

Use defaulted parameters and keep the name stable. CREATE OR REPLACE preserves existing grants so applications continue to work.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie