# Oracle PL/SQL Procedure Parameter Types in 19c - Link: https://ultimasolution.pl/oracle-procedure-parameter-types/ - Published: 2024-11-01T23:20:51+01:00 - Author: Karol Preiskorn 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: ```EnlighterJSRAW 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. ```EnlighterJSRAW 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. ```EnlighterJSRAW 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: ```EnlighterJSRAW 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. ```EnlighterJSRAW CREATE OR REPLACE PROCEDURE add_bonus ( p_salary IN OUT NUMBER, p_bonus IN NUMBER ) AS BEGIN p_salary := p_salary + p_bonus; END; / ``` ```EnlighterJSRAW 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: ```EnlighterJSRAW 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; / ``` ```EnlighterJSRAW 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 ```EnlighterJSRAW -- 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`: ```EnlighterJSRAW 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. ```EnlighterJSRAW 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: ```EnlighterJSRAW 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. ```EnlighterJSRAW 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 ```EnlighterJSRAW 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. ```EnlighterJSRAW 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: ```EnlighterJSRAW 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: ```EnlighterJSRAW 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. ```EnlighterJSRAW 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.