🔄 Using SYS_REFCURSOR to Return Data in PL/SQL and Integrate with APIs (C#, ASP.NET)

SYS_REFCURSOR is a predefined weak cursor type in Oracle that allows returning query results dynamically. It’s ideal for procedures with output parameters, especially when integrating with external APIs.

Among the many ways to develop a program, using SYS_REFCURSOR as an output parameter has definitely made my work easier, allowing me to return data in a simple way when dealing with SQL queries instead of JSON, which can sometimes be more labor-intensive. Utilizing this resource helps with integration into other programming languages.

In this article, I have presented in a simple way how it is called using C# and ASP.NET.

Let’s dig in!

---

🧠 Why use SYS_REFCURSOR?

✅ Dynamic and generic return format
✅ Perfect for consumption via C#, Java, Python, etc.
✅ Simplifies communication between databases and APIs

📦 PL/SQL Example:

---

💻 Consuming the Procedure in a C# Console App

You can access the procedure above with a simple C# program using Oracle.ManagedDataAccess.Client:

---

🌐 Transforming into an API with ASP.NET Core

Now, let’s package everything into a RESTful API endpoint:

📡 How to Call via GET and Expected Response

Now, assuming the API is running on http://localhost:5000, you can call this endpoint using a GET request:

GET http://localhost:5000/api/employees/10

🔹 Expected JSON Response

---

🧩 Conclusion

With this setup, you can:

✅ Create procedures that return dynamic data using SYS_REFCURSOR
✅ Consume these procedures with C#
✅ Expose the data as a RESTful API endpoint using ASP.NET Core

#PLSQL #Oracle #SYSREFCURSOR #CSharp #NET #ASP.NET #Backend #DatabaseTips #SoftwareDevelopment #DevLife #CoffeeAndCode #APIs