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

[Anthony T. <ant...@gm...>]

It is possible -- at least so long as I understand what you are trying to
do. Here is an example:

NUM_ENTRIES  = 25 # maximum number of entries that can be expected

connection = cx_Oracle.Connection("user/pw@tns")
cursor = connection.cursor()
returningVar = cursor.arrayvar(int, NUM_ENTRIES)
cursor.execute("""
        begin
            delete from TestExecuteMany
            where rownum <= 5
            returning IntCol bulk collect into :output;
        end;""",
        output = returningVar)
for intCol in returningVar.getvalue():
    print "DELETED", intCol

Hope that helps.

Anthony


On Mon, Apr 29, 2013 at 2:00 PM, Paul Moore <p.f...@gm...> wrote:

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