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.

21h
comment Oracle INSERT: Column Value equal to Sequence Number's Nextval generated in another column
@blerontin - It's talked about here: "Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once ... for each row ...", and more relevantly for this specific example, the two paragraphs after the bullet list.
Dec
15
comment Subtracting Dates in Oracle - Number or Interval Datatype?
This doesn't attempt to answer the question, and isn't valid syntax for Oracle; did you miss the Oracle tags and references in the title and question?
Dec
3
awarded Nice Answer
Nov
30
comment Grant permissions on schema to specific schema in Oracle
The short answer is no, but you could do this with a stored procedure that executes dynamic SQL; it's a bit messy and potentially could leave security holes (if implemented badly). What is the objection to create any?
Nov
30
comment Oracle db separated schemas for one user
You would have to have separate users to have separate schemas; but why do you want/need the seperation? Security, logical separation, duplicate object names, something else?
Nov
21
comment How to drop oracle user starting with colon (:)
@Jeff - you'd just need to enclose the name in double quotes in that case (see the docs) but in static SQL; the dynamic part here is specifically for the user name. This works but is slight overkill for a procedure or other object with a quoted identifier.
Nov
20
revised Comparison between dates in SQL using to_timestamp
Fixed typo
Nov
20
answered Comparison between dates in SQL using to_timestamp
Nov
20
revised How to generate XML-output from Oracle
Formatting
Nov
20
comment How to generate XML-output from Oracle
You can see the options and syntax in the documentation. For your desired output you may want to look at dbms_xmlgen.get_xml; see this example.
Nov
20
comment Error in Oracle Code with non-equijoin operation
@kb4000 - I've turned it into an answer (see the help centre!), which seems a bit artificial now but the alternative is to close the question as no longer reproducible and this might just be useful for someone else one day.
Nov
20
answered Error in Oracle Code with non-equijoin operation
Nov
20
awarded Good Answer
Nov
19
answered Create partition is using timestamp with local time zone [Oracle 11g]
Nov
19
comment Create partition is using timestamp with local time zone [Oracle 11g]
You're getting ORA-14020, not ORA-14751: Invalid data type for partitioning column of an interval partitioned table? I think you're right though...
Nov
19
comment Select on anothr users connection does not work
possible duplicate of ORA-00942: table or view does not exist error is given even though I granted Select privilege to the user
Nov
19
comment Select on anothr users connection does not work
When you query are you prefixing the table name with the schema it belongs to? You could have a public synonym but you haven't shown that either. (I hope you don't mean SYS owns it... don't create objects under built-in accounts).
Nov
19
revised Invoking SQLLoader from java, issues loading a file with name that contains @-signs
Added Java env equivalent
Nov
19
comment Invoking SQLLoader from java, issues loading a file with name that contains @-signs
@Santoshbudhe - I would think you can just include it as another element in the env array? It belongs there, not as part of cmd. My example was from a shell, I hadn't realised you were invoking SQL*Loader from Java.
Nov
19
comment ResultSet.next() is too slow for an oracle ref cursor
Maybe you have a bad execution plan for query which includes r; if you're on 10g I'd wonder about bind variable peeking, particularly if it's intermittent. Can you check the execution plans for both queries (using the same bind values)?
1 2 3 4 5