IN OUT Parameters in Oracle Procedure

Back to: Oracle Tutorials for Beginners and Professionals

IN OUT Parameters in Oracle Stored Procedure

In this article, I am going to discuss IN/OUT Parameters in Oracle Stored Procedure with Examples. Please read our previous two articles where we discussed How to Create Procedures with IN Parameters and OUT Parameters in Oracle.

IN/OUT Parameters in Oracle Stored Procedure

Let us try to learn about IN OUT Parameter in Oracle Stored Procedure. The calling environment will send the value to IN OUT parameter. The procedure will do some changes and send it back to the calling environment using the OUT Parameters. Let us see an example to understand it better. The IN Out parameter is not commonly used. But now we are just learning it.

Let us take a 10-digit number and make changes to the 10-digit number. We are taking a number as “1234567890”. We will be formatting this number from 1234567890 to 123 (45) 67890 using the oracle stored procedure. So, the number will be changed as we are separating the first 3 digits and putting the next two digits in a bracket, and leaving the last 5 digits. Let us create a procedure according to it by executing the following SQL Statements.

CREATE OR REPLACE PROCEDURE FORMATE_TEL
(
   P_TEL IN OUT VARCHAR2
)
IS
BEGIN
   P_TEL:=SUBSTR(P_TEL,1,3)||'('||SUBSTR(P_TEL,4,2)||')'||SUBSTR(P_TEL,7);
END;

You can see the procedure name is FORMAT_TELEPHONE where the P_TEL is the IN OUT parameter which takes the 10-digit number as input and changes the format of the number and send it back to the calling environment. In the PL/SQL block we are separating the first 3 variables and adding a ‘(‘ next to it and inserting the next two values and adding another ‘)’ and then adding the next 5 values. In this way, we are separating the 10-digit number into multiple formats but in a single variable. Let us go ahead and compile the procedure.

So, the procedure is now compiled. In order to execute this stored procedure, we need to declare a bind variable as follows.

VARIABLE B_TELEPHONE VARCHAR2(20);

So, the bind variable is now declared. In order to execute the procedure, we need to assign a number to the bind variable. Let us go ahead and assign the value to the bind variable as follows.

EXECUTE :B_TELEPHONE:='1234567890';

So, now the bind variable is assigned the value 1234567890. Now, let us execute the procedure with the bind variable as follows.

EXECUTE FORMATE_TEL(:B_TELEPHONE);

So, the procedure is now executed. Let us go ahead and print the bind variable B_TELEPEHONE and see if the procedure is executed correctly or not as follows.

PRINT B_TELEPHONE;

So, the format of the number is changed now. The procedure is working fine. There is another way to do this IN OUT parameter instead of using the bind variable. Declare a variable and execute the procedure in the PL/SQL block and print the variable in the DBMS output. This is more optimized than using the bind variables.

DECLARE
V_TEL VARCHAR2(100):='1234567890';
BEGIN
   FORMATE_TEL(V_TEL);
   DBMS_OUTPUT.PUT_LINE(V_TEL);
END;

We have declared the variable V_TEL with the number ‘1234567890’ as VARCHAR2. We will be calling the procedure with the variable V_TEL. Once the procedure is called then the V_TEL will be stored with the updated value as the OUT parameter. We will be printing the V_TEL parameter using DBMS output.

So, the procedure is now executed. Let us go ahead and check the DBMS output and see if this method works or not.

So, this is how we use the IN/OUT Parameter Mode with Oracle Stored Procedure.

Comparison of Parameter Modes in Oracle

We have three parameter modes IN, OUT, IN/OUT parameter modes. We will be distinguishing each parameter mode from other modes.

IN	OUT	IN/OUT
Default Mode	Must be Specified	Must be Specified
Values are passed into a subprogram	Returned to the calling environment	Passed into subprogram returned to calling environment
The formal parameter acts as a constant	Uninitialized variable	Initialized variable
The actual parameter can be a literal expression, constant, or initialized variable	Must be a variable	Must be a variable
Can be assigned a default value	Cannot be assigned a default value	Cannot be assigned a default value

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