Procedure with OUT Parameter in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Stored Procedure with OUT Parameter in Oracle

In this article, I am going to discuss How to Create a Stored Procedure with the OUT Parameter in Oracle with Examples. Please read our previous article where we discussed How to Create a Stored Procedure with IN Parameter in Oracle with Examples.

Stored Procedure with OUT Parameter in Oracle

Let us try to learn how to create a stored procedure with the OUT parameter in the Oracle database. The IN Parameters in Oracle Stored Procedure are used for bringing the values into the procedure for execution. On the other hand, the OUT Parameters are used to carry a value out of the procedure after execution.

Note: IN parameter is the default parameter. Whenever we create a procedure without the IN or OUT parameter, then oracle will consider them as IN parameters.

Syntax to Create OUT Parameter in Oracle:

CREATE OR REPLACE PROCEDURE UPDATE_SALARY
(
   P_EMP_ID OUT NUMBER, 
   P_AMOUNT OUT NUMBER
)
IS

If we don’t mention IN in the parameter section, then oracle by default takes the IN parameter mode. To understand this, let us take an example and see how the OUT parameter works. Let us go ahead and create a procedure that takes the EMPLOYEE_ID as a parameter and returns the first name and salary. We will be using the bind variables in order to print the OUT parameters variables.

CREATE OR REPLACE PROCEDURE QUERY_EMP
(
    P_EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE, 
    P_F_NAME OUT EMPLOYEES.FIRST_NAME%TYPE, 
    P_SAL OUT EMPLOYEES.SALARY%TYPE
 )
IS
BEGIN
   SELECT FIRST_NAME, SALARY
   INTO P_F_NAME, P_SAL
   FROM
   EMPLOYEES
   WHERE EMPLOYEE_ID=P_EMP_ID;
   EXCEPTION
   WHEN OTHERS THEN 
   DBMS_OUTPUT.PUT_LINE(SQLCODE);
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

From the above SQL Statements, you can see the procedure name is QUERY_EMP and we have declared three variables P_EMP_ID, P_F_NAME, and P_SAL. We have mentioned P_EMP_ID as the EMPLOYEES table and taken the EMPLOYEE_ID. We have not mentioned the parameter mode, so oracle takes the parameter mode as the default IN parameter. The second parameter P_F_NAME is mentioned with OUT and P_SAL is also mentioned with OUT. So, we will be sending the variables P_F_NAME and P_SAL to the calling parameters and the value will be sent to the calling environment.

The main logic of the procedure is taking the EMPLOYEE_ID as input and selecting the first name and salary from the table EMPLOYEES according to the EMPLOYEE_ID. So, the first name will be stored in the P_F_NAME variable and the salary will be stored in the P_SAL variable.

We have mentioned the exception with the SQLCODE and SQLERROR messages. Let us go ahead and compile this procedure.

So, the procedure is now compiled. Now, we must declare two bind variables that will be used to print the OUT parameter from the procedures. We will be declaring variables as B_FIRST_NAME which will print the first name and B_SAL which prints the salary of the employee. The syntax is given below.

VARIABLE B_FIRST_NAME VARCHAR2(100)
VARIABLE B_SAL NUMBER

Note: When we use the bind variables with VARCHAR2 we must define the size. When we use the bind variables with numbers no need to mention the size.

So, the bind variables are executed. So, the variables are now defined. Now let us go ahead and execute the procedure. The procedure execution is the same as the IN parameter as follows.

EXECUTE QUERY_EMP (105,:B_FIRST_NAME, :B_SAL)

We are using the out parameters (bind variables) B_FIRST_NAME and B_SAL. When we execute this procedure the variables P_F_NAME will be stored in B_FIRST_NAME and P_SAL will be stored in B_SAL. Let us execute the procedure.

So, the procedure is now executed. The value of P_F_NAME is stored in B_FIRST_NAME and the value of P_SAL will be stored in B_SAL. Let us now print these variables to check the result by executing the following statement.

PRINT B_FIRST_NAME B_SAL;

So, the values are now printed. We have selected the EMPLOYEE_ID as 105. So, the first name of the EMPLOYEE_ID 105 is David and the salary is 4800.

This method is very complicated. There is another way to use the OUT Parameters. Instead of using the bind variables, we declare two variables and then execute the procedure and store the OUT parameters in those variables as follows.

DECLARE
V_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
V_SAL EMPLOYEES.SALARY%TYPE;
BEGIN
  QUERY_EMP(105,V_FIRST_NAME,V_SAL );
  DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME);
  DBMS_OUTPUT.PUT_LINE(V_SAL);
END;

You can see that here we have declared two variables V_FIRST_NAME and V_SAL which are not binding variables, and in the PL/SQL block we have executed the procedure where the OUT parameters will be stored into V_FIRST_NAME and V_SAL. After execution, we will be printing the variables V_FIRST_NAME and V_SAL using the DBMS_OUTPUT.PUT_LINE. Let us execute this procedure.

So, the procedure is completed successfully. Let us go ahead and check the DBMS output and see if the procedure is executed or not.

You can see from the DBMS output that the First name and the salary are printed. So, in this procedure, we have defined three parameters where the first parameter i.e. P_EMP_ID is IN parameter and the other two parameters P_F_NAME and P_SAL are the out parameters. These parameters sent the value back to the calling environment and the variables V_FIRST_NAME and V_SAL printed the values of the OUT parameters.

In the next article, we will discuss IN OUT Parameters in Oracle Stored Procedure with Examples. Here, in this article, I try to explain OUT Parameters in Oracle Stored Procedure with Examples. I hope you enjoy this Oracle Stored Procedure with OUT Parameters article.