Re: [cx-oracle-users] cx-oracle-users Digest, Vol 81, Issue 18

[Tamás G. <gt...@gt...>]

Date: Fri, 19 Apr 2013 09:56:40 -0700

> From: Glyph <gl...@tw...>
> Subject: Re: [cx-oracle-users] an old question
> To: cx-...@li...
> Message-ID: <D50...@tw...>
> Content-Type: text/plain; charset="iso-8859-1"
>
>
> 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
>

If you don't mind ugly solutions, then a PIPELINED function can return rows
just before  deletions:

CREATE PACKAGE ugly IS
  TYPE recs_typ IS TABLE OF foo%ROWTYPE INDEX BY PLS_INTEGER;

  CREATE FUNCTION hack(id IN INTEGER) RETURN recs_typ PIPELINED;
END;
/
CREATE PACKAGE BODY ugly IS
  CREATE FUNCTION hack(id IN INTEGER) RETURN recs_typ PIPELINED IS
    row foo%ROWTYPE;
  BEGIN
    FOR cur IN (SELECT ROWID FROM foo WHERE bar = id) LOOP
      SELECT * INTO row WHERE ROWID = cur.ROWID;
      PIPE ROW(sor);
      DELETE foo WHERE ROWID = cur.ROWID;
END;
/


GThomas