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.

42s
answered Delete on matching multiple values supplied as static list
3m
comment Delete on matching multiple values supplied as static list
Do you need to delete matching pairs from both lists, or any combination of values from both lists, or any rows that matches a value in either list?
35m
comment do we need to recreate a materialized view if the underlying table structure changes
A manual refresh doesn't give you any errors? There is nothing for that view in user_errors? Does the client that is showing you the red cross give you an option to refresh it or view more information about it and its errors?
2h
comment How to Call Procedure from other Procedure in Oracle AS in TSQL
You should look at links and tutorials to understand the differences between TSQL and Oracle's SQL and PL/SQL rather then asking us for complete solution. Even if someone takes the time to do your work for you, you won't have learned anything and won't know how to do it next time.
2h
comment do we need to recreate a materialized view if the underlying table structure changes
How did you 'change the order' (and why)? By dropping and recreating the table? Did it (and does it) having a related materialised view log table? What error does the MV have - you can see that in the user_errors or all_errors views.
2h
comment Execute Subqueries of a View
No, there isn't anything built-in to do that. You could potentially write your own function to parse the source text and try to extract a CTE, but it would be a lot of effort for not much real gain, in my opinion. The only reason I can think you'd even want to try is to keep the source hidden; but then anyone debugging would presumably have to see the bigger picture anyway, and would need to see the source to know what the CTE is called.
2h
answered "Not a valid month" on an INSERT statement
2h
comment "Not a valid month" on an INSERT statement
It only matters if there is implicit conversion going on; if my_date is a timestamp (despite the confusing name) then there isn't, and if there was the NLS formats match anyway. So that leaves NLS_DATE_LANGUAGE being wrong; or is there a trigger on the table setting a date/timestamp field?
3h
comment "Not a valid month" on an INSERT statement
Is my_date a date or timestamp field? If it's a date, what are your NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT set to? Sounds like an implicit conversion problem. Or maybe less likely an NLS_DATE_LANGUAGE problem.
3h
comment Oracle 9i: Lock table from clearing table data
To stop the table owner, or other database users? Others can only do what you let them, so if they aren't given delete/ truncate privs or drop any table privs they won't be able to do those things anyway. If you're worried about the owner then you could add triggers to block things, but the owner could drop or disable those first. Not really clear what problem you're trying to solve?
3h
comment Execute Subqueries of a View
I'm not sure what your question is. You've stated a problem that doesn't really exist now, and that you have a workaround for if it does (getting the view source and running that); functionality that doesn't exist; and two approaches you've already rejected. What are you asking?
23h
comment ORA-00904 "Invalid identifier” for an identifier in a group by clause
The error is reported on line 75, which is when you refer to qtyliv in the GROUP BY, not in that subquery itself.
23h
revised ORA-00904 "Invalid identifier” for an identifier in a group by clause
Added doc link, and put alias definition back in the edited query
23h
answered ORA-00904 "Invalid identifier” for an identifier in a group by clause
1d
comment Raise Application Error in Trigger and still update table
Not if they're part of the same transaction, you have the same problem. (There is a way to do it but it is usually a bad idea). I don't understand the model really. Why would you reject the new reading? Why is there any connection between the two things?
1d
comment As if like C program do we have any shared memory in oracle
So you want to cache it in Java, not in the database layer, really?
1d
comment Raise Application Error in Trigger and still update table
Why are ypu raising an exception at all? What is supposed to happen, the session that is inserting the reading sees the dbms_output text (if thay are set up to see it, which you don't know), and they see the exception too, and... do what with that information? Why would you only check late-payment when a new reading happens anyway? Surely that sort of check should be a regular scheduled thing, maybe a daily sweep of all accounts or outstanding payments or something?
1d
comment Numeric overflow error while Truncating table in Oracle
That doesn't really make sense. Please provide a reproducible test case that demonstrates the problem.
1d
reviewed Leave Open default value for dbms_sql.number_table?
1d
comment SQL Developer 4.0.2 install on Windows 7 64 bit
Could you add some text to explain this please? It looks like you're saying that the 'Oracle' look-and-feel is (very) broken in 64-bit mode, so the solution is to use the Windows look-and-feel, is that correct? Or is it a Java version issue?
1 2 3 4 5