TweetShareSharePin0 SharesIn this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this. At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post […]
SQL
Suppressing repeating values in SQL
TweetShareSharePin0 SharesSometimes you my find a need to suppress repeating values in SQL. One case is when your reporting tool does not have such a feature or you just cannot find it fast enough. That happened to me with a report that was to be converted to APEX from Oracle Reports the other week. I could not find an option in APEX to suppress repeating values, and I did not want to make them all control breaks as that would […]
SQL Access to the alert-log in 11g and an oddity
TweetShareSharePin0 SharesQuite a while ago I wrote about how to setup the alert log as an external table. Since then 11g has been introduced and is now widely used. It of course changes the location and makes the alert log an xml file. While it is possible to select from it using xml functions like Laurent Schneider does here, it is still a bit cumbersome. Tanel Poder (@TanelPoder) found a nicer way by using X$DBGALERTEXT which does a really nice job […]
OOW 2010 Develop Keynote
TweetShareSharePin0 SharesYes, this post is a little out of order as it clearly didn’t take place after the Thursday afternoon sessions. I missed it during the conference so I had to catch up on it later on the on demand site. I thought it was interesting enough to write up a few notes. It was held by Tom Kyte and the subject was “What’s new in database development”. It’d be more correct to say Oracle development than database development as […]
OOW 2010 Thursday Morning
TweetShareSharePin0 SharesThe day starts with a presentation b6 Tom Kyte about “What else can you do with system and session data”. Tom starts with reviewing the history of tuning an Oracle database. The prehistoric era (v5) required writeing debug code as that was the only way to get any information about what the code did. The dark ages followed (v6) and now Oracle introduces: Counters/ratios bstat/estat SQL Trace The first few (7?) v$-views are introduced In the renaissance era (v7) […]
The case for CASE
TweetShareSharePin0 SharesI’ve just uploaded my latest article. This time it is a look at the SQL keyword CASE. It’s power is often not fully appreciated. It is much more flexible and powerful than you may expect from just taking a quick glance in the official documentation. The article is located here. Please post comments and questions here. TweetShareSharePin0 Shares
Supported version of skip locked?
TweetShareSharePin0 SharesAs Pawel showed in his post some details about how the row scn technology works in specific situations isn’t as well documented as you’d wish. I asked Oracle development to clarify the questions I had and the answer back was both interesting and useful. Every kind of update and lock on a row will make the rowscn for the row/block NULL until the updating/locking transaction completes. That is, any kind of lock and update can be identified when retrieving […]
SQL Challenge I – Solution
TweetShareSharePin0 SharesBoth Gary and Philip postd working versions. Gary’s didn’t work for me when I had the same letter more than one time in the string. Philips did work and the need to deal with each letter as if it is unique can of course be questioned. The intent was for a solution that could handle that and as Philip showed adjusting that is as easy as to just add a distinct. My version is based on not using analytical […]
Dynamic in-line queries
TweetShareSharePin0 SharesI don’t know of a better name for this feature. I think this name describes the function well, even if it may sound overly ambitious. This whole post will be based on a post by Laurent Schneider where he uses XML features to do a select count(*) from different tables and return the result in one result table. It is like a auto generated number of selects that are unioned together. You can find Laurent’s post here. It’s a […]
Creating a feed with ORA-00600 errors
TweetShareSharePin0 SharesInstead of having to look at your different alert logs for ORA-00600 errors frequently, why not have them come to you? This post will, as promised, show the Perl code needed to publish a feed with ORA-00600 errors. This is building on the previous two posts that shows how to create an external table for the alert log and how to pull out all information for an ORA-00600 so each fetch retrieves one the data you’re interested in. The […]