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

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

On 30 April 2013 09:17, Glyph <gl...@tw...> wrote:

> I do appreciate you taking the time to help, even if your answer wasn't
> directly useful to me.  I realize what I'm looking for is a bit esoteric.
>  "What you are trying to do is impossible" is a totally acceptable answer -
> I am really just trying to understand *why* Oracle makes this impossible.
>

I think the big issue here is that people with an Oracle background (like
me) just don't understand the question, because fundamentally the
principles are different. (I say this because I've had endless frustrating
conversations with a friend who's an Ingres specialist, and we always end
up realising that neither of us *actually* understands the fundamental
terms that the other person is using, even though we thought we did...)

This may be basic stuff you already know, but for purposes of clarity,
Oracle runs statements in 3 steps - parse, execute and fetch. Only select
statements need a fetch phase, all other statements run in the execute
phase. Think of it as "only select statements produce a cursor you can
fetch from" if you like.

The basic problem here is that PL/SQL is not a select, so it completes in
the execute phase. With PL/SQL you can *create* cursors, and pass them
round, but they are not "the result of the execute phase" and so won't ever
be accessible directly from the cx_Oracle cursor object. That's why you
need a variable of some sort.

One exception is that you can *wrap* your PL/SQL in a select. But to do
that you need a defined function in the schema - that's where the
suggestion of a pipelined function comes from. With that you'd do
cursor.execute("select * from
table(function_doing_your_work_and_piping_rows())") and then read the
cursor. But yes, that requires a function defined in the schema. And it's
going to return a specific set of columns (so you need one per table, in
effect). The reason for the latter restriction is that Oracle expects
cursors to be statically defined (SYS_REFCURSOR is an exception, but that's
getting a bit deep for now).

Someone said that you may be able to have an "inline" function definition
in your SQL in the not yet released 12c. That might help you, but I presume
you'd want a solution that worked for a currently released version :-)


> The database-specificity of cursor.var() makes sense to me, idiomatically,
> but the restricted size of query results seems odd.  For example, if I want
> to 'delete...returning...into...' a million rows, rather than the default
> allowed ~1000 or so, how can I do that?  Would a pipelined procedure with
> PIPE ROW address this problem, and the general expectation is just that I'd
> be able to put such a procedure into my schema?
>

I believe that how that works is that you're returning the data into a
PL/SQL array, then converting that into a cursor. So there's a materialised
array of values in there, and allowing for a million entries will have a
memory cost. You could declare an array that big, if the memory wasn't an
issue to you, but again it's about static objects, you have to declare
*some* limit up front.

Yes a procedure with PIPE ROW would address this - that's the key point
about PIPE ROW is taht it sends the rows "on demand" rather than
materialising the whole array.

Paul