Alex Poole

United Kingdom

stackexchange.com/users/98359

Age: 42

I've been picking up hints, tips and information from a variety of sources over the years, but somehow have always been a taker and not a giver. I'm finally getting involved and trying to give something back to the community. Still learning far more than contributing though.

I'm a bit of a jack-of-all-trades in a small UK-based team within a US subsidiary of a Japanese company, doing work for a large UK telecoms provider. Production support, developer, part-time (a.k.a accidental) DBA in dev/test, analyst, consultant... whatever needs doing right now, generally. Mostly Oracle (since about 1995, versions 6 to 11g), Java, WebLogic, shell scripting, HTML, CSS, a bit of C when pushed, but whatever's needed today. Work is mostly on Oracle Linux, but dragged into Windows and networking issues fairly often, and a Mac user at home.

4h
comment Shreading Data From Oracle XMLType
Can you expand the sample XML a bit? You only show one analysis with one response, so the structure isn't obvious (to me, anyway).
8h
comment Oracle SQL Query Where Timestamp is in Last 1 hour Using Systimestamp
Out of interest, what does the first query return if you use - interval '1' hour instead? You're implicitly converting to a date type to subtract (1/24), which loses the time zone.
11h
answered How to get data from resultset to a list in java from oracle without loop?
12h
comment How to get data from resultset to a list in java from oracle without loop?
Stuck with what? You don't have enough memory to hold all million items in the list? Do you really need to, and all at once? If so can you increase the JVM memory settings, and/or add memory to the machine? We have no idea what your application is doing, why it's loading the list, what it will do with it, or what your environment is, so it isn't clear what the real problem is, never mind what the solution might be. Sorry.
12h
comment Oracle Indexes EXECUTE object privilege
It isn't documented though, so perhaps something Oracle is using internally, or a mistake. It doesn't mean anything to execute an index, and I'm not sure why your edit refers to them as procedures? For now I'm going with 'none of this makes sense' *8-)
12h
comment How to get data from resultset to a list in java from oracle without loop?
The resultset object doesn't contain all the data though - it executes the query but doesn't pull data back, the actual rows of the result set are fetched in batches as you need them, via next(). That's why the fetch size is important. Surely you're expecting memory usage to leap if you're sticking a million items into your list?
12h
comment Has anyone successfully connected Geogig to an Oracle Database?
It would be nice if it showed you how it was actually connecting, wouldn't it. I doubt the --schema flag is useful for Oracle though? And it might be picky about SID vs. service name. But I'm entirely guessing.
12h
comment How to get data from resultset to a list in java from oracle without loop?
It would be interesting to see how you came to that conclusion.
13h
comment Oracle Indexes EXECUTE object privilege
Can you include an example of what you mean - where you see that privilege, and if it's in your database now, a query that shows it? Are you perhaps confusing index with indextype, or a function for a function-based index?
13h
comment Issue to generate dynamic xml with XMLElement
Sorry, don't see how it can generate multiple elements in each row (which is what the error suggests is happening); any tags in fields would be encoded so shouldn't cause confusion. Unless any of the columns you're including are themselves XMLType, maybe... (Not relevant, but XMLElement returns an XMLType not a CLOB, and you could use distinct instead of your group by, and not sure if you really need the subquery.)
15h
comment java.sql.SQLException: ORA-01000: maximum open cursors exceeded
While that would simplify the finally block slightly for the connection variable, it wouldn't solve the problem of the result sets and prepared statements being left open?
15h
comment java.sql.SQLException: ORA-01000: maximum open cursors exceeded
You might also want to consider batching all the inserts up.
16h
comment How to get data from resultset to a list in java from oracle without loop?
The loop itself is using too much resource? Or fetching is? What fetchsize do you have - maybe you're really seeing an effect of too many small DB fetches when they could be batched up much more efficiently? If you need all million records in memory then it doesn't really matter how you get them, that won't affect how much space each one uses up, right?
16h
comment 00984. 00000 - "column not allowed here"
Sure, but not creating it and dropping it immediately. Who knows what is in the OP's mind though *8-)
16h
comment 00984. 00000 - "column not allowed here"
I assume it's an exercise; otherwise why bother with the table anyway, why not just use the plain select on its own? Either way creating and dropping the table seems... excessive.
16h
answered Loop index variable use is invalid in PL/SQL Collections
16h
comment 00984. 00000 - "column not allowed here"
Bind variables rather than concatenation, surely?
16h
comment How to get data from resultset to a list in java from oracle without loop?
You could possibly do something with DB objects and table types that get retrieved into arrays, but that's likely to be complicated and depends on your data and queries anyway.
17h
comment Oracle linguistic index not used when SQL contains parameter with LIKE
@VinceJS - well I've learnt something new about your specific problem; added information from an Oracle tech note which makes my general observation a bit redundant...
17h
revised Oracle linguistic index not used when SQL contains parameter with LIKE
Added information from Oracle note for this specific circumstance
1 2 3 4 5