Introduction:
In this post, we will explain how to use the DBMS_SQL.RETURN_RESULT procedure in Oracle PL/SQL to return a result set from a stored procedure and then consume that result set in a C# application using ADO.NET. The DBMS_SQL.RETURN_RESULT procedure allows us to return a cursor from a PL/SQL procedure, which can be handy when dealing with dynamic SQL queries or complex data retrieval scenarios.
Prerequisites:
Before proceeding, make sure you have the following:
- An Oracle database with the necessary permissions to create and execute PL/SQL procedures.
- Oracle Data Provider for .NET (ODP.NET) installed in your C# application project.
Creating the PL/SQL Procedure:
Let’s start by creating the PL/SQL procedure that utilizes the DBMS_SQL.RETURN_RESULT procedure to return a result set.
Writing the C# Code:
Next, let us write the C# code to call the PL/SQL procedure and retrieve the result set using ADO.NET.
The above code for executing a stored procedure and populating a DataTable object is quite similar for SQL Server as well
Output:
- Below is from PL-Sql
- And below is from .Net
Conclusion:
In this post, we have seen how to use the DBMS_SQL.RETURN_RESULT procedure in an Oracle PL/SQL procedure to return a result set from the database. Additionally, we have learned how to consume this returned result set in a C# application using ADO.NET and Oracle Data Provider for .NET (ODP.NET).
