Oracle PL/SQL Procedure Parameter Types in 19c

In Oracle Database 19c, a PL/SQL procedure parameter can use the IN, OUT, or IN OUT mode. IN is the default and passes a value into the procedure. OUT returns a value to the caller, while IN OUT accepts an initial value and returns its modified value. This guide explains the parameter modes, safe datatype choices, default values, named notation, NOCOPY, and SYS_REFCURSOR result sets.

Procedure parameter syntax in Oracle 19c

A parameter declaration contains a name, an optional mode, and a datatype:

CREATE OR REPLACE PROCEDURE procedure_name (
    p_input        IN     VARCHAR2,
    p_output       OUT    NUMBER,
    p_input_output IN OUT DATE
) AS
BEGIN
    NULL;
END;
/

The mode is optional because Oracle uses IN when no mode is written. Use a consistent prefix such as p_ for parameters and l_ for local variables so the source is easy to read.

IN parameters

An IN parameter supplies a value to the procedure. It is read-only inside the procedure and is the normal choice for search criteria, configuration values, and business inputs.

CREATE OR REPLACE PROCEDURE get_employee_name (
    p_employee_id IN employees.employee_id%TYPE
) AS
    l_name employees.first_name%TYPE;
BEGIN
    SELECT first_name
    INTO l_name
    FROM employees
    WHERE employee_id = p_employee_id;

    DBMS_OUTPUT.PUT_LINE(l_name);
END;
/

Use bind variables or procedure parameters instead of concatenating user input into dynamic SQL. This reduces SQL injection risk and can improve cursor sharing.

OUT parameters

An OUT parameter returns a value to the caller. It is useful when a procedure must return one or more scalar values, a status code, a message, or a cursor.

CREATE OR REPLACE PROCEDURE get_employee_salary (
    p_employee_id IN  employees.employee_id%TYPE,
    p_salary      OUT employees.salary%TYPE
) AS
BEGIN
    SELECT salary
    INTO p_salary
    FROM employees
    WHERE employee_id = p_employee_id;
END;
/

Call the procedure with a variable:

DECLARE
    l_salary employees.salary%TYPE;
BEGIN
    get_employee_salary(100, l_salary);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || l_salary);
END;
/

Do not rely on the caller’s previous value of an OUT variable after an exception. Define and document the error behavior explicitly.

IN OUT parameters

An IN OUT parameter receives an initial value, changes it, and returns the new value through the same parameter. This is useful for transformations but creates more side effects than a separate input and output.

CREATE OR REPLACE PROCEDURE add_bonus (
    p_salary IN OUT NUMBER,
    p_bonus  IN NUMBER
) AS
BEGIN
    p_salary := p_salary + p_bonus;
END;
/

DECLARE
    l_salary NUMBER := 5000;
BEGIN
    add_bonus(l_salary, 500);
    DBMS_OUTPUT.PUT_LINE(l_salary); -- 5500
END;
/

Default parameter values

Use DEFAULT when a parameter is optional:

CREATE OR REPLACE PROCEDURE log_message (
    p_message IN VARCHAR2,
    p_level   IN VARCHAR2 DEFAULT 'INFO'
) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('[' || p_level || '] ' || p_message);
END;
/

BEGIN
    log_message('Started');
    log_message('Failed', 'ERROR');
    log_message(
        p_message => 'Completed',
        p_level   => 'INFO'
    );
END;
/

Named notation is safer than positional notation when several parameters have the same datatype or when optional parameters are added later.

Positional and named notation

-- Positional notation
get_employee_salary(100, l_salary);

-- Named notation
get_employee_salary(
    p_employee_id => 100,
    p_salary      => l_salary
);

Named notation documents the meaning of each argument and reduces the chance of swapping two values of the same datatype. It is especially useful for procedures with many parameters.

Use %TYPE and %ROWTYPE for stable interfaces

For a parameter based on a table column, prefer %TYPE:

p_employee_id employees.employee_id%TYPE

This keeps the declaration aligned with the column datatype and avoids repeating a hard-coded NUMBER or VARCHAR2 definition. %ROWTYPE can represent a complete table row in PL/SQL, but it is often less convenient as a public interface because callers must construct a compatible record.

Return a result set with SYS_REFCURSOR

Oracle Database 19c supports SYS_REFCURSOR for returning a query result from a PL/SQL procedure. It is a weak REF CURSOR type: the procedure can open it for different query shapes, but the caller must fetch columns that match the selected result.

CREATE OR REPLACE PROCEDURE get_employees (
    p_department_id IN  employees.department_id%TYPE,
    p_result        OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_result FOR
        SELECT employee_id,
               first_name,
               last_name,
               salary
        FROM employees
        WHERE department_id = p_department_id
        ORDER BY employee_id;
END;
/

The caller fetches and closes the cursor:

DECLARE
    l_cursor SYS_REFCURSOR;
    l_id     employees.employee_id%TYPE;
    l_name   employees.first_name%TYPE;
    l_last   employees.last_name%TYPE;
    l_salary employees.salary%TYPE;
BEGIN
    get_employees(10, l_cursor);

    LOOP
        FETCH l_cursor INTO l_id, l_name, l_last, l_salary;
        EXIT WHEN l_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(l_id || ': ' || l_name || ' ' || l_last);
    END LOOP;

    CLOSE l_cursor;
END;
/

The caller owns the returned cursor. Always close it after fetching to release server-side resources. Add a deterministic ORDER BY when consumers depend on row order.

SYS_REFCURSOR versus a typed REF CURSOR

Type	Strength	Limitation
SYS_REFCURSOR	Flexible and convenient for application clients	Weak type checking; the caller must know the result shape
Typed REF CURSOR	Compile-time row-shape checking	Less flexible when query result shapes change
Scalar OUT	Simple return value	Not suitable for multiple rows
Collection OUT	Structured PL/SQL result	Client binding support varies

Use SYS_REFCURSOR when flexibility and client interoperability are more important than compile-time row-shape checking. Use a typed cursor when the result contract should be stricter.

Using NOCOPY with OUT and IN OUT

NOCOPY is a performance hint for OUT and IN OUT parameters. It can reduce copying for large values, but it can also make changes visible before normal procedure completion.

CREATE OR REPLACE PROCEDURE normalize_name (
    p_name IN OUT NOCOPY VARCHAR2
) AS
BEGIN
    p_name := UPPER(TRIM(p_name));
END;
/

Do not assume that NOCOPY is always faster. Test normal and exception paths with representative data before using it in a public API.

Oracle 19c client and datatype considerations

• Oracle 19c supports IN, OUT, IN OUT, default values, %TYPE, and SYS_REFCURSOR.
• SYS_REFCURSOR is not unique to Oracle 19c; it is also available in earlier Oracle releases.
• PL/SQL datatypes are not always directly supported by SQL clients. For example, BOOLEAN procedure parameters can require client-specific handling.
• JDBC, ODP.NET, SQL*Plus, SQLcl, and other drivers can bind OUT cursors differently.
• Test the procedure through the same client and driver used by the application.
• Use named notation and explicit datatype anchoring when procedures are part of a long-lived application interface.

Error handling and parameter design

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Employee was not found'
        );

Make error behavior part of the procedure contract. Do not silently convert errors into null output values. When a procedure opens a cursor locally, close it in the exception path when necessary. When a cursor is returned through an OUT parameter, document whether the caller or procedure is responsible for closing it.

Package procedure example with typed parameters

A package specification is a useful public contract: callers see the parameter modes and datatypes, while the implementation can change internally. The example below uses %TYPE so the API follows the table definition and uses a defaulted IN parameter for optional behavior.

CREATE OR REPLACE PACKAGE order_api AS
  PROCEDURE create_order(
    p_customer_id IN  orders.customer_id%TYPE,
    p_order_id    OUT orders.order_id%TYPE,
    p_status      OUT VARCHAR2,
    p_validate    IN  BOOLEAN DEFAULT TRUE
  );
END order_api;
/

CREATE OR REPLACE PACKAGE BODY order_api AS
  PROCEDURE create_order(
    p_customer_id IN  orders.customer_id%TYPE,
    p_order_id    OUT orders.order_id%TYPE,
    p_status      OUT VARCHAR2,
    p_validate    IN  BOOLEAN DEFAULT TRUE
  ) IS
  BEGIN
    IF p_validate AND p_customer_id IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, 'Customer ID is required');
    END IF;

    INSERT INTO orders (customer_id, order_status)
    VALUES (p_customer_id, 'NEW')
    RETURNING order_id INTO p_order_id;

    p_status := 'CREATED';
  EXCEPTION
    WHEN OTHERS THEN
      p_status := 'FAILED';
      RAISE;
  END create_order;
END order_api;
/

Because p_validate is a PL/SQL BOOLEAN, call this procedure from PL/SQL rather than directly from SQL. The caller must declare variables for every OUT parameter:

DECLARE
  l_order_id orders.order_id%TYPE;
  l_status   VARCHAR2(20);
BEGIN
  order_api.create_order(
    p_customer_id => 1001,
    p_order_id    => l_order_id,
    p_status      => l_status
  );

  DBMS_OUTPUT.PUT_LINE(l_status || ': ' || l_order_id);
END;
/

Collection parameters and bulk processing

For multiple values, define a collection type in the package specification. This keeps the type visible to callers and avoids a long list of scalar parameters:

CREATE OR REPLACE PACKAGE employee_api AS
  TYPE t_employee_id_list IS TABLE OF employees.employee_id%TYPE
    INDEX BY PLS_INTEGER;

  PROCEDURE mark_reviewed(
    p_employee_ids IN t_employee_id_list,
    p_count        OUT PLS_INTEGER
  );
END employee_api;
/

CREATE OR REPLACE PACKAGE BODY employee_api AS
  PROCEDURE mark_reviewed(
    p_employee_ids IN t_employee_id_list,
    p_count        OUT PLS_INTEGER
  ) IS
  BEGIN
    p_count := 0;
    FOR i IN 1 .. p_employee_ids.COUNT LOOP
      UPDATE employees
      SET review_status = 'REVIEWED'
      WHERE employee_id = p_employee_ids(i);
      p_count := p_count + SQL%ROWCOUNT;
    END LOOP;
  END mark_reviewed;
END employee_api;
/

For large collections, prefer FORALL with a suitable exception strategy instead of issuing one SQL statement per loop iteration. Keep transaction ownership with the caller unless the package explicitly documents that it commits.

Overloaded procedures and parameter design

PL/SQL supports overloading when the parameter signatures are distinguishable. Use it for related operations, but avoid overloads that differ only by numeric subtypes or could be ambiguous with NULL literals.

CREATE OR REPLACE PACKAGE conversion_api AS
  PROCEDURE normalize(p_value IN VARCHAR2, p_result OUT VARCHAR2);
  PROCEDURE normalize(p_value IN NUMBER,   p_result OUT NUMBER);
END conversion_api;
/

-- Named notation makes the intended overload and role clearer:
DECLARE
  l_text VARCHAR2(100);
BEGIN
  conversion_api.normalize(
    p_value  => '  Oracle 19c  ',
    p_result => l_text
  );
END;
/

Choose parameter names that describe the contract, keep OUT values deterministic on successful execution, and document whether callers or the procedure own COMMIT and ROLLBACK.

Oracle procedure parameter checklist

• Use IN unless the procedure must return or modify a parameter.
• Prefer %TYPE for table-related scalar parameters.
• Use named notation for procedures with many or optional parameters.
• Use SYS_REFCURSOR for flexible query results returned to clients.
• Close returned cursors in the caller.
• Test NOCOPY with exception paths before adopting it.
• Verify datatype and cursor binding with the production client driver.
• Use bind variables when dynamic SQL is required.

Frequently Asked Questions

What are the parameter modes in an Oracle procedure?

Oracle PL/SQL procedures support IN, OUT, and IN OUT parameters. IN is the default mode.

What is the default parameter mode in PL/SQL?

The default mode is IN. It passes a value into the procedure without returning a modified value through that parameter.

What is the difference between OUT and IN OUT?

OUT returns a value without requiring an initial value from the caller. IN OUT receives an initial value, modifies it, and returns the modified value.

Does Oracle 19c support SYS_REFCURSOR?

Yes. Oracle Database 19c supports SYS_REFCURSOR for returning query results from PL/SQL procedures. The feature is also available in earlier Oracle releases.

Should I use SYS_REFCURSOR or a typed REF CURSOR?

Use SYS_REFCURSOR for flexible result sets and application clients. Use a typed REF CURSOR when compile-time row-shape checking is more important.

Does NOCOPY always improve performance?

No. NOCOPY can reduce copying for some large values, but it changes parameter behavior during exceptions and should be tested carefully.