Implicit Statement Results 📤¶
Mentor's Note: Before Oracle 12c, if a Procedure wanted to return a list of employees to a Java app, you had to use a
REF CURSOR. It was messy. Now, you can just "Return" the result set directly! 💡
🌟 The Scenario: The Direct Delivery 🚚¶
Imagine you order a meal. - Old Way (Ref Cursor): The waiter gives you a token. You take the token to the kitchen to get your food. - New Way (Implicit Result): The waiter just brings the food directly to your table. No extra steps! ✅
💻 1. The RETURN_RESULT Method¶
We use the built-in package DBMS_SQL to hand a cursor back to the calling environment.
CREATE OR REPLACE PROCEDURE get_top_earners AS
c_emp SYS_REFCURSOR;
BEGIN
-- 1. Open the cursor
OPEN c_emp FOR
SELECT first_name, salary FROM employees
WHERE salary > 10000;
-- 2. Return the result directly!
DBMS_SQL.RETURN_RESULT(c_emp);
END;
/
💻 2. Testing the Output¶
When you run this in SQL Developer or SQL*Plus, the rows will just appear in your output window automatically.
EXEC get_top_earners;
-- Result:
-- FIRST_NAME | SALARY
-- -----------|-------
-- Vishnu | 15000
-- Arjun | 12000
🛡️ 3. Architect's Note: Modern Integration 🛡️¶
This feature was created to make Oracle compatible with features in SQL Server and MySQL.
- Benefits: It is much easier for Reporting Tools (like PowerBI or Tableau) to call a procedure and get data back instantly.
- Tip: Only use this for End-User Reporting. If you need to pass data between two PL/SQL blocks, a standard REF CURSOR is still the better choice. ⚡