|
|
 |
= 7 days or newer |
|
 |
= 14 - 7 days old |
Documentation
|
|
I thought it would be beneficial to dedicate an article on regular expression matching so we, myself included, get acquainted or re-acquainted with the power these expression can give us.
|
|
|
|
Oracle Bitmap indexes are a very powerful Oracle feature, but they can be tricky!
|
|
|
|
Functions for converting to date, numeric, string, and timestamp data types can be found through the related links.
|
|
|
|
Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also
the hour, minute, and second.
|
|
|
|
Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.
If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also, PL/SQL does not roll back database work done by the subprogram.
At the level of the SQL*Plus prompt, every update/insert/delete has one implicit savepoint, and also the invocation of any unnamed block. Below that, the unnamed block itself has 'sub' savepoints - one for each insert/update/delete statement in it, and one for each subprogram unit. And so on down the line.
|
|
|
|
I was having trouble with one of my SPs and I needed to handle it correctly. As a result I needed to "throw" (raise) the exception. In that case we have three oppurtunities. SQLCODE and SQLERRM can be used to output the message.
Throw one of the default exceptions
These exceptions are defined by the database itself. Like ZERO_DIVIDE NO_DATA_FOUND exceptions. OTHERS will catch everything other that you have specified. To have the behaviour of try catch statements, it’s a good practice to write your exception handler in begin and blocks.
|
|
|
|
Nice quick reference of all the oracle query hints. It includes an explaination for each hint.
|
|
|
|
I think secondary indexes on IOTs need some careful testing. It's probably not an area that many people have used in a high-stress environment. There are two main issues:
* The primary key is used in the secondary index instead of a rowid so for large primary keys, the index would be bigger than the equivalent index on a simple table.
* The secondary index holds a 'guess' block address for the row it points to so that a query can go to the right block more cheaply. But if the row has moved (e.g. leaf block split) then the guess is wrong and is a cost, not a benefit. But this won't be a problem if your application is always adding data at the 'right-hand' edge of the index.
Depending of version, there are various features and limitations on what you can do with secondary indexes that you will have to trade, balance and test, if you go down that path.
|
|
|
|
A logoff trigger fires when a session disconnects. Here's a small example to demonstrate it. A record will be inserted into a table (logoff_tbl) when someone disconnects. The record consists of the username and when he disconnected.
|
|
|
|
Suppose we have a master-detail relationship between two tables such that the rows in the detail table are a collection of attributes for each master record. The detail table has only three columns: an ID that is a foreign key back to the master table, the name of the attribute an the value of the attribute.
|
|
|
|
|
|
|
|
|
|
Page Views
|
|
1,522 today
|
|
2,067 yesterday
|
|
74,891 last month
|
|
13,246,853 total
|
|
|
|
