TweetShareSharePin0 SharesThis is a short update to the presumed bug I wrote about recently in this post. After a few back and forth with Oracle support it turns out this is a well-known (to them) effect of using dbms_random in SQL. The base for this seems to be a bug report in 10g that was declared “not a bug”. You can read the bug report on MOS. It is also covered in this doc that references the same bug. It turns out that a […]
SQL
Bug with prefactoring and nondeterministic SQL
TweetShareSharePin0 SharesSometimes the results of a SQL is not at all what you expected. Most of the time it is a simple error you’ve made. Very rarely is it an issue that after hours of looking at it still looks like SQL does something wrong, But it still happens. This post is about one such situation that occurred last week. If you read the last blog post, you’ll recognize some of this. It turned up when I looked at transforming […]
LUHNs algoritm – Three ways with SQL and PL/SQL
TweetShareSharePin0 SharesHave you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day. It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Greace, and Israel to credit card numbers and IMEI numbers and misc other things. It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the […]
Quicktip: Don’t grant access to a synonym
TweetShareSharePin0 SharesI’ve seen this in a few places lately. When I ask the database guys there about it, they think this DDL would never work or “it does nothing but does not return an error”. Here is an example, All of the following is performed by a DBA user. We create a table, let’s call it TB. It is owned by user A. User B is granted select privilege to A.TB. A synonym is created for user B pointing to A.TB, let’s call […]
Does recreating a view remove the privileges?
TweetShareSharePin0 SharesShort answer: No This blog post is pretty much just to document what seems to have been hard to find online. At work there was a big rework of a liquibase project due to the perception that “create or replace view” on an existing view would make the granted privileges be dropped. That was of course not what happened. The issue was that using liquibase the new version of the view was installed. Then the rollback was triggered and […]
An old dog learns a new trick
TweetShare1SharePin1 SharesReports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff. Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took it out for a […]
The power of using records in APEX III
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 […]
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 […]