Re: an old question

Meyer Matthias <[email protected]> Tue, 30 Apr 2013 11:09:07 +0200

Newsgroups	gmane.comp.python.db.cx-oracle
Message-ID	<CA51EF2F90C73544A335AF4AF859E188013CD8B229@CLMBX02.celesiogroup.com>

Hi,

I think Chris is correct. I also like his approach with SELECT.. FOR UPDATE

If I read correctly, I think, what the OP is trying to do is some kind of historization. But there are already a number of patterns for that. If I remember correctly there are 3 ways:
- You add a %_old column for each column you want historized. So you have for each column exactly one historic data item.
- You add a version_id column. This gives you complete history, but makes queries harder and asks for a clever index. Also you could add a few views, which gives you all the "current" records. Some people also add a is_current column, to ease filtering.

- You add a history-table into which you move all old records.
For all 3 patterns, you add audit-columns in which you record when and why you changed the record.

But maybe the OP can tell us more about what he is actually trying to do.

Kind regards and thanks for all the interesting stuff I learned, reading this thread..

Matthias

________________________________________
Von: Chris Gould [mailto:[email protected]]
Gesendet: Dienstag, 30. April 2013 10:50
An: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
Betreff: Re: [cx-oracle-users] an old question

Hi -
I've been following the discussion in this thread and have to say I think the objective of the whole exercise is flawed. You seem to be trying to write a set of wrappers to make Oracle appear to behave in the same way as other RDBMS, and I think that isn't realistic. To get the best out of any database you need to exploit its features and avoid its "quirks". Oracle's features include returning a data-sets as a REFCURSOR where you don't know in advance how many records are in the set - you and Oracle only find that out when you fetch past the last row. Similarly, if you're talking of deleting a million rows and trying to return all the deleted data into a collection of some sort that gets passed back to the invoker, what are you going to do with that data? It sounds like you'd be better off trying a "SELECT .. FOR UPDATE" and then deleting each row - inefficient in Oracle, but that seems to be of secondary importance.

I really think the further you go with this, the more problems you're likely to encounter and you're never going to get any decent performance out of what is a very expensive RDBMS.

On 30 April 2013 09:17, Glyph <glyph-wgPraSjSqjuEakHPNWoOSQC/[email protected]> wrote:

On Apr 30, 2013, at 1:04 AM, Mark Harrison <[email protected]> wrote:

On 4/30/13 12:21 AM, Glyph wrote:

As I keep pointing out:

Currently, I'm doing this with host variables, but that's a limited solution [...]

You're trying but apparent failing to make your point...

I tried helping you out earlier, but your response left me not
caring a lot whether or not you solved your problem or not.

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.

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?

-glyph

------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

GEHE Informatik Services GmbH & Co. KG, Sitz: Stuttgart, AG Stuttgart, HRA 12167, UST-IdNr. DE 811 655 252
Persönlich haftende Gesellschafterin: GEHE Pharma Handel GmbH, Sitz: Stuttgart, AG Stuttgart, HRB 14591,
Geschäftsführer: André Blümel (Vorsitzender), Rainer Baumgärtner, Dr. Peter Schreiner
Weitere Gesellschafter: GEHE Informatik Services-Verwaltungs GmbH, Sitz: Stuttgart, AG Stuttgart, HRB 15843
Geschäftsführer: Rainer Baumgärtner, Dr. Stefan Grill