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

[Paul M. <p.f...@gm...>]

OK, got you. (BTW, sorry for the delay in responding, your mail only just
arrived on the list).

I don't think this is possible. DELETE RETURNING is a PL/SQL construct
which gives you the deleted rows back as  a collection. You *might* be able
to put those into a refcursor somehow, but frankly I can't think of a way
that doesn't need you to create a stored procedure/function in the
database. If you can do that, a pipelined function would probably give you
what you need. (Someone else posted an example elsewhere in the thread that
you could probably get to work).

Sorry I can't be of more help...
Paul


On 19 April 2013 17:56, Glyph <gl...@tw...> wrote:

>
> 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
>
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>