Back to: Oracle Tutorials for Beginners and Professionals
Oracle Stored Procedure with IN Parameter with Examples
In this article, I am going to discuss Oracle Stored Procedure with IN Parameter with Examples. Please read our previous article where we discussed the basic concepts of Stored Procedures in Oracle.
Example to Understand IN-Parameter in Oracle Stored Procedure:
Let us Understand the IN-Parameter in Oracle Stored Procedure with an Example. Let us use the same example that we used for explaining the syntax in our previous article.
CREATE OR REPLACE PROCEDURE UPDATE_SALARY ( P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER ) IS BEGIN UPDATE EMPLOYEES SET SALARY=SALARY+P_AMOUNT WHERE EMPLOYEE_ID=P_EMP_ID; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE); DBMS_OUTPUT.PUT_LINE (SQLERRM); END;
In this procedure, we have mentioned the procedure name as UPDATE_SALARY. We have mentioned two parameters P_EMP_ID and P_AMOUNT which stores the employee id and salary that needs to be incremented. The parameter mode is IN which indicates these parameters are input parameters and the value needs to be sent while calling the procedure.
If we need any variables which are only specific to this stored procedure, then those variables can be defined between the IS and begin. We have mentioned the logic below. While calling the stored procedure we need to pass values for the P_EMP_ID and P_AMOUNT parameters. Then based on the P_EMP_ID parameter value, it will update the Employee salary (i.e. incrementing the employee salary with the value received by the P_AMOUNT parameter).
UPDATE EMPLOYEES
SET SALARY=SALARY+P_AMOUNT
WHERE EMPLOYEE_ID=P_EMP_ID;
COMMIT;
Then, we have mentioned the exception below. The exception is for others and the output is mentioned below. In our upcoming articles, we will discuss Exceptions in Stored Procedures in detail.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
So, let us go ahead and run the procedure. The procedure will be compiled.
The procedure is now compiled. We do not have any errors. So, we have created a stored procedure that takes two values as inputs and then updates the salary in the EMPLOYEES table. The employee_id is fetched from the P_EMP_ID parameter and the salary amount is fetched from the P_AMOUNT parameter of the stored procedures. These two parameters are input parameters i.e. they bring the value into the stored procedure and while calling the stored procedure, we need to pass values for these two input parameters.
Now, let us try to see what the error looks like when there is any error in the procedure code. Let us try to remove the semicolon from the line “where employee_id =P_EMP_ID”.
CREATE OR REPLACE PROCEDURE UPDATE_SALARY ( P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER ) IS BEGIN UPDATE EMPLOYEES SET SALARY=SALARY+P_AMOUNT WHERE EMPLOYEE_ID=P_EMP_ID COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE); DBMS_OUTPUT.PUT_LINE (SQLERRM); END;
Let us now try to run this procedure and see what the error of the procedure looks like.
So, here you can see the error. It shows the Error: check compiler log. To check the compiler log, click on View -> Logs as shown in the below image.
You can see the error as the ORA-00933 SQL command not properly ended. We can double-click on the error which navigates to the error line and we can fix the error. There is another way to check the error. We can run the following select query to check the errors.
SELECT * FROM USER_ERRORS WHERE NAME = ‘UPDATE_SALARY’;
You can see there are two errors in the USER_ERRORS. We have a dictionary called USER_ERRORS where the name should be the procedure name ‘UPDATE_SALARY’. Now, let us try to fix the error and compile the procedure again by modifying the procedure code as follows.
CREATE OR REPLACE PROCEDURE UPDATE_SALARY ( P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER ) IS BEGIN UPDATE EMPLOYEES SET SALARY=SALARY+P_AMOUNT WHERE EMPLOYEE_ID=P_EMP_ID; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE); DBMS_OUTPUT.PUT_LINE (SQLERRM); END;
Here, you can see that UPDATE_SALARY stored procedure is compiled fine. Let us try to check the USER_ERRORS dictionary and see if there are any errors or not.
You can see there are no errors in the USER_ERRORS dictionary in Oracle. Now, let us try to compile this procedure in the command prompt while connecting to the database. Let us go ahead and connect to the database.
So, now we are connected to the database. Let us create or replace the procedure statement again and remove the semicolon and run the statement again as shown in the below image.
Here, you should get the following errors. At the end of the lines, we must mention the ‘/’ slash.
From the output, you can see it is giving one warning which states that the Procedure is created with compilation errors. In order to view the compilation errors, we have to run the command “show errors” as follows.
Command: SHOW ERRORS
So, we got the same errors as the SQL developer. Let us fix this error and execute the procedure code again as follows.
Now, you can see the stored procedure is created successfully. Let us check the procedure in the schema.
Here, you can see the procedure is present as an object inside the hr schema. Let us go ahead and check the procedure. As we are working on the table employees. Let us go ahead and select the employee_id as 100 and see the salary of the employee_id whose id is 100 by executing the following SQL query.
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=100;
You can see the salary of the employee is 24000. Now, let us execute the procedure as follows:
EXECUTE UPDATE_SALARY(100, 150);
Here, we are calling the stored procedure by passing 100 as the value for the P_EMP_ID parameter and 150 as the value for the P_AMOUNT parameter. So, from the procedure code, you can see we are incrementing the Salary of the employee by 150 where the Employee Id is 100. Now, let us run this procedure. The salary will be 24150 and it will be incremented by 150. Let us execute the code.
The procedure is successfully completed. Let us see the value in the table EMPLOYEES and salary of the employee_id 100 by executing the following SQL statement.
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=100;
Using Anonymous Block in Oracle to Call Stored Procedures
We can even manually enter the values of the employee_id and salary as well. We need to mention them in the anonymous block and run this block. For a better understanding, please have a look at the following code.
BEGIN
UPDATE_SALARY (&EMP_ID, &AMOUNT);
END;
Let us go ahead and run this statement.
We can see a prompt asking for the emp_id. Let us enter the emp_id as 100. We are giving the amount value as 200.
If we click on Ok then the output will be displayed as below.
So, this shows that the procedure successfully completed, So, we have seen two methods to execute the procedure. The first method is to execute the procedure normally and the second method is to place the executing code between begin and end. We can see the information of the procedures in a dictionary table called USER_OBJECTS. Let us run a query to check the procedure details as follows.
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME=’UPDATE_SALARY’;
You can see the object name is UPDATE_SALARY and the OBJECT_TYPE is the procedure and the created time and the LAST_DDL_TIME status and more details.
We can even check the code behind the procedure by using the dictionary called USER_SOURCE. Let us run a query on the dictionary USER_SOURCE and see the code of the stored procedure UPDATE_SALARY by executing the following query.
SELECT * FROM USER_SOURCE WHERE NAME=’UPDATE_SALARY’ ORDER BY LINE;
You can see the code of the procedure UPDATE_SALARY. You can drop the stored procedure in oracle by using the below statement.
DROP PROCEDURE UPDATE_SALARY;
Note: By the way, if we create a procedure, we can drop the procedure. The user hr is the owner of the procedure. The owner of the procedure can drop the procedure.
In the next article, we will discuss OUT-Parameter in Oracle Stored Procedure with Examples. Here, in this article, I try to explain IN-Parameter in Oracle Stored Procedure with Examples. I hope you enjoy this Oracle Stored Procedure with IN Parameter article.
