Re: [cx-oracle-users] an old question
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] an old question
|
From: Meyer M. <Mat...@ge...> - 2013-04-30 09:28:11
|
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:chr...@to...] Gesendet: Dienstag, 30. April 2013 10:50 An: cx-...@li... 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. C On 30 April 2013 09:17, Glyph <gl...@tw...> wrote: On Apr 30, 2013, at 1:04 AM, Mark Harrison <mh...@pi...> 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-...@li... 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 ______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________ |
