🔄 Using SYS_REFCURSOR to Return Data in PL/SQL and Integrate with APIs (C#, ASP.NET)
Technology professional with over 15 years of experience delivering innovative, scalable, and secure solutions — especially within the financial sector. I bring deep expertise in Oracle PL/SQL (9+ years), designing robust data architectures that ensure performance and reliability. On the back-end side, I’ve spent 6 years building enterprise-grade applications using .NET, applying best practices like TDD and clean code to deliver high-quality solutions. In addition to my backend strengths, I have 6 years of experience with PHP and JavaScript, allowing me to develop full-stack web applications that combine strong performance with intuitive user interfaces. I've led and contributed to projects involving digital account management, integration of VISA credit and debit transactions, modernization of payment systems, financial analysis tools, and fraud prevention strategies. Academically, I hold a postgraduate certificate in .NET Architecture and an MBA in IT Project Management, blending technical skill with business acumen. Over the past 6 years, I’ve also taken on leadership roles — managing teams, mentoring developers, and driving strategic initiatives. I'm fluent in agile methodologies and make consistent use of tools like Azure Boards to coordinate tasks and align team performance with delivery goals.
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
