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.

1h
revised Regex: Detect any substring of the alphabet?
Added recursive CTE version
2h
comment Regex: Detect any substring of the alphabet?
@DavidZemon - that's SQL Fiddle; try looking at the plaintext output (form the 'Run SQL' button/drop-down combo control). Let me know if I haven't explained anything though.
2h
revised Regex: Detect any substring of the alphabet?
added 104 characters in body
2h
revised Regex: Detect any substring of the alphabet?
added 1747 characters in body
3h
answered Regex: Detect any substring of the alphabet?
3h
comment Data for specific date
trunc(sysdate, 'MM') will give you the first of the current month more simply. But you haven't said how you're determining there is no data, or how you're running the query. Might be simple to run it, see how many rows were found, and run it again with the previous month (via add_months). Too many unknowns in your process though.
3h
comment Handling unique constraint (SYSTEM.SYS_C006997) violated
This might be driver-specific; which driver and version are you using to connect to the database?
3h
comment Data for specific date
If you mean your table doesn't yet have any data for the first of the current month, then you can either check for that and then decide which month to query; or query both months' data and discard the previous month if you have anything for the current month in the result set. But that affects aggregation depending on what you're reporting on. It's rather vague at the moment.
4h
comment Varchar2 to Date for sysdate comparison
@tx_query - testdata is just to provide some example data like yours, and show how it's converted successfully to a date with this format model. You don't need testdata or a join. Just use TO_DATE(read_date, 'MM/DD/YYYY') in your original query.
4h
comment Print pascal triangle in oracle pl/sql
You can do it in plain SQL as well.
6h
answered Pragma Exception init with constant
6h
comment Pragma Exception init with constant
You get PLS-00103 if you try to refer to the package variable directly; and PLS-00702 if you use a local variable (set the package variable), right?
6h
comment Want to play small video files
So the database and Oracle references aren't really relevant - you just want to know how to play a video from your application?
7h
revised How to Select and Group By query which is already grouped with Over() ? - Oracle
Added Fiddle
7h
answered How to Select and Group By query which is already grouped with Over() ? - Oracle
7h
comment How to Select and Group By query which is already grouped with Over() ? - Oracle
Sample and expected output would be useful too. At the moment that gets two rows, both with 05/08/2013, right? And you want to still get one row for TID 1, but three for TID 2 because the three dates are different? But all with the same INSERTDATE? Seems like you're trying to unpivot the three dates for comparison independently.
8h
comment How to Select and Group By query which is already grouped with Over() ? - Oracle
Not sure I'm following why you can't just apply an aggregate and a group by to the outer select. Can you add some sample data and show the expected results for that? (A cut-down but working query would also be helpful, as a self-contained example, rather than having some jojns etc.)
8h
comment Display Number of Rows based on input parameter
@WamglindCarmasaic - I think tvm meant to remove the default clause and make the client always specify a number of rows; i.e. so it has to pass a really high value to make sure all rows are included. That implies your client knows enough about the data to do that safely, or runs a query first to decide what number to use.
8h
revised Display Number of Rows based on input parameter
added 39 characters in body
8h
revised Display Number of Rows based on input parameter
Added pipelined approach
1 2 3 4 5