All the examples of using PL/SQL I can find end up looking like this (this example taken from Wikipedia):
FOR RecordIndex IN (SELECT person_code FROM people_table) LOOP DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code); END LOOP;
In other words, they all have one thing in common: when it comes time to actually output the data somewhere, they stick it in
DBMS_OUTPUT, which seems to be a pretty useless place for an application to process it.
If I wanted to retrieve data from an Oracle database using a PL/SQL feature as if this data were the result of a plain SQL query, how would I do it? For example, if I wanted to process the rows deleted by a
DELETE ... RETURNING ... INTO SQL statement in the same way that I would process the results of
SELECT ... FROM ...?
I do not want to modify the schema of the database or create any stored procedures; I'd just like to do
cursor.execute("begin; ... something; end"); results = cursor.fetchall().
Particularly, I do not want to create a variable with
cursor.var(), since that API is not portable between database implementations. (Obviously the SQL won't be portable either, but it's a generally accepted fact that one needs to generate custom SQL strings for different database backends no matter what.)
With Oracle 12c you will be able to define a temporary PL/SQL function and use it in a SELECT statement:
WITH FUNCTION x(param) <body> END x; SELECT x(p) FROM t
This is one SQL statement, a select from which you can fetch rows in the usual way. Unfortunately Oracle 12c has not been released yet...