How do I retrieve data from Oracle as a result of a query using an anonymous PL / SQL block in Python?

advertisements

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...