Obsessed with Oracle PL/SQL

I ran into a problem yesterday on the  Oracle Dev Gym  (offering quizzes, workouts and classes on Oracle technologies). A number of rows of data were incorrectly deleted. I was able to use  Flashback Query  to restore them (thank you, thank you, Flashback Query!). Crisis averted.  But how did this come about? I recruited Chris Saxon to help me figure out how this could have happened. In relatively short order, we narrowed down the culprit to a process in the Dev Gym Application Express definition that was unconditionally "removing previews" - but was in fact removing all rows, "previews" or not. Ugh. So we fixed that. But it got me wondering and worrying: what other processes in my app are unconditional? And should they be? While some processes fire unconditionally on a page (for example, to get the data from tables and display them on the screen), many are (or should be!) restricted to a button press, the result of a conditional expression, or an ...

Have you ever run into the following error when trying to create a materialized view? ORA-22818: subquery expressions not allowed here Yes, it is true: you cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense): CREATE MATERIALIZED VIEW hr_demo_mv AS SELECT employee_id, (SELECT MAX (hire_date) FROM employees ce) maxhd FROM employees t / ORA-22818: subquery expressions not allowed here 22818. 00000 - "subquery expressions not allowed here" *Cause: An attempt was made to use a subquery expression where these are not supported. *Action: Rewrite the statement without the subquery expression. Rewrite my query without the subquery expression? But I just spent an hour putting it all together. Works great. Gives me exactly the results I want and need. Rewrite it? ARGH. Calm yourself. While ...