Alex Poole

United Kingdom

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.

comment pipelined function with cursor parameter oracle
@arminrock - no problem. I didn't bother showing the pipelined function as you said you already had that part working, but I tested this as a procedure in the same package, which seemed to be what you were doing. Same as Lalit really, other than his being in an anonymous block instead.
answered pipelined function with cursor parameter oracle
comment Is it possible to give aliases to columns with a Select *?
Almost a duplicate of this. Using select * is considered bad practice anyway, in this case because even if your application needs all the columns then it is relying on them always being in the same order in the data dictionary - which may break one day.
comment SQL: Create Trailer Count
Are you always going to be running this from SQL*Plus?
comment Increase number of rows in SQL Developer export
'Max rows to print in a script' controls the spool size for me in 4.1. What version of SQL Developer are you using?
comment SQL Error: ORA-02000: missing ALWAYS keyword when create identity column based table
Are you sure the server is 12c, not just your client? It looks very much like your server is 11g. What does select * from product_component_version or select * from v$version report?
comment Sum(bytes)/1024/1024/1024 shows in hexadecimal in aix
Which client are you running the query in?
comment How many times does the value A from the column 2 appears with the value B from the column 1 (metadata)
It would help if you showed your current query then.
comment Is it safe to run oracle 11g express edition on Oracle VirtualBox in production server?
VirtualBox is a powerful ... virtualization product for enterprise as well as home use, and VirtualBox is ... targeted at server, desktop and embedded use. The enterprise reference suggest so, but whether your organisation considers it safe - which among other things may depend on whether you have a support contract with Oracle - isn't something we can answer.
answered Log function return with out of range message
comment Oracle - Union tables with a virtual column
Is the view of the same table? You'll only get true/false for the same other values if they exist in both sources. Maybe you know you have some of the same data in both, and you want a not-exists clause in the view part, perheps? We don't know what you're trying to do or what the data represents though, so that's just a guess.
comment Log function return with out of range message
Can you look at the execution plan or trace the execution? It looks like Oracle is rewriting the query (pushing the predicate into the CTE/subquery).
comment Return date from function in PL/SQL
So you just want to concatenate the arguments together, with a slash between each? You really have 2-digit years - and if so, you want YY not RR?
comment How to calculate yearly cumulative return
Do you want the average over the whole 19 years to be at least 5%, which Gordon's answer gives; or for every individual year to have at least 5%?
answered SQL Nested Query with distinct count
comment Cannot connect to oracle database
SQL*Plus is connecting locally, not through the listener. What are the settings in SQL Developer? Just to check, does localhost still resolve to (in your hosts file)? BTW, it is better to copy the text from your command window into the question, rather than linking to,images.
comment Error at line 20: PLS-00103: Encountered the symbol "" ORACLE 11G
Is that the entire error message/stack? Are you running several comands as a script or this all of it?
comment Oracle SQL Trigger for View
@Kittyinsocks - the answer you accepted was only looking at the current row's value; but your comment was looking at your aggregate column in the view, no?
comment how to validate integer datatype in oracle procedure
The documentation says: "If the subtype has a numeric base type, then the actual parameter inherits the range of the subtype, but not the precision or scale." It helpfully demonstrates that with an INTEGER example. So this is expected behaviour, though not intuitively obvious.
comment Oracle SQL Trigger for View
You seemed to have this working yesterday and you've now made it much complicated than in your comment? You're assuming that who/whatever does the insert is doing anything with the dbms_output buffer, which often isn't the case. And that whoever inserts does something with the info, and can't insert into the base table. A scheduled (monthly?) job that checks all employees and, say, emails HR might be more appropriate for this sort of thing?
1 2 3 4 5