How to Execute Function in Oracle With Parameters

In Oracle, you can execute functions that accept parameters in several ways. Functions are commonly used to return a single value based on the input you provide. Below are two common approaches to executing an Oracle function with parameters.

1. Execute the Function Using a SELECT Statement

You can call a function directly within a SQL SELECT statement. This is the simplest and most common method, especially when you want to quickly retrieve the result.

SELECT get_emp_job(7566) FROM DUAL;

Output:

GET_EMP_JOB(7566)
----------------------------------------
MANAGER
1 row selected.

In this example, the function get_emp_job is called with the employee number 7566 as the parameter. The result, which is the job title, is displayed as output.

2. Execute the Function Using a PL/SQL Block

You can also execute the function inside a PL/SQL block, store the result in a variable, and then display the output using DBMS_OUTPUT.PUT_LINE.

SET SERVEROUTPUT ON;
DECLARE
    v_job emp.job%TYPE;
BEGIN
    v_job := get_emp_job(7566);
    DBMS_OUTPUT.PUT_LINE(v_job);
END;

Output:

MANAGER
PL/SQL procedure successfully completed.

This approach is useful when you want to use the function’s return value in further processing within your PL/SQL code.

Creating the GET_EMP_JOB Function

Below is the definition of the GET_EMP_JOB function used in the above examples. This function accepts an employee number as an input parameter and returns the corresponding job title from the emp table.

CREATE OR REPLACE FUNCTION GET_EMP_JOB(i_empno IN emp.empno%TYPE)
RETURN VARCHAR2
IS
    CURSOR c_ename(p_empno emp.empno%TYPE) IS
        SELECT job
        FROM emp
        WHERE empno = p_empno;
    v_job emp.job%TYPE;
BEGIN
    OPEN c_ename(i_empno);
    FETCH c_ename INTO v_job;
    CLOSE c_ename;
    RETURN v_job;
END get_emp_job;

• The function takes i_empno as an input parameter.
• A cursor c_ename is declared to select the job for the given employee number.
• The function opens the cursor, fetches the job into the variable v_job, and then closes the cursor.
• Finally, it returns the job title for the specified employee.

By using these methods, you can easily execute Oracle functions with parameters, either directly in SQL or within your PL/SQL code.