Re: [cx-oracle-users] an old question

[Glyph <gl...@tw...>]

On Apr 19, 2013, at 1:04 AM, Paul Moore <p.f...@gm...> wrote:

> On 18 April 2013 23:48, Glyph <gl...@tw...> wrote:
> Thanks for responding, but, unfortunately, no.  I put a comment there, but in short; I know that it's unusual, but there are cases where inline PL/SQL can do things that regular SQL can't.
> 
> Is there any way to emit data back to the application from an inline block like this?
> 
> Can you give an example of your actual code? In general, the answer to your question is "no", because PL/SQL blocks are not select statements and therefore do not return rows via the cursor. But there are ways, depending on the details of what you want to do. Your requirement to not modify the schema (specifically not creating stored procedures) makes it hard, though...

I want to do something like this:

cursor.execute("""
begin
-- mumble declare 'something' somehow
delete from foo where bar = 1 returning baz into :something;
-- send rows from :something into the output that will come from .fetchall();
end;
""")
cursor.fetchall()

because the desire to atomically get rows out of a 'delete' statement is not a table-specific stored procedure.  I also don't want to create any temporary tables, because again, this is not in any way specific to one table; I just want to know what it is that I deleted or updated when I look at the results of a delete or update statement.

Currently, I'm doing this with host variables, but that's a limited solution because I have to know ahead of time whether I expect a single row, or multiple rows, and if I expect multiple rows, array variables can hold a maximum of 4000 elements (or is it 4000 bytes? documentation seems unclear).

Anyway, generating these as "regular" database output seems like a much saner and more scalable option, so I'd love to know how to do it.

Thanks for your time!

-glyph