Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time
- with me. I sincerely hope you found both the presentations enlightening as well as entertaining.
Please see the details of the sessions below along with the (more...)
When using SQL Developer, there is a feature called Completion Insight which help you write SQL and PL/SQL in the SQL Worksheets. It will auto-pop helpful things, like object name when typing a SELECT statement, column names if it is aware of the object which you are accessing and things (more...)
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
I was trying to call function DECOMPOSE while investigating a strange problem I had with some Unicode strings that could not be properly indexed by Oracle Text and, amazingly, I found out that most (more...)
Those who visit SQL.ru often know what the title means. It’s a very simple yet powerful technique to group data which doesn’t seem appropriate for grouping at first sight.
I learned this very nice tip long time ago but often have to re-think before applying it to SQL or (more...)
After my Kscope13 presentation, one of the attendees approached me and asked if I had used regular expressions to try to validate email addresses. He didn’t know about regular expressions before the presentation and he was writing a PL/SQL function (which was getting very complex…) to accomplish this particular task. (more...)
DBMS_LOCK is a slightly obscure built-in package. It provides components which so we build our own locking schemes. Its obscurity stems from the default access on the package, which is restricted to its owner SYS and the other power user accounts. Because implementing your own locking strategy is a good (more...)
Time travel sucks, especially going back in time. Nobody takes a bath, there are no anaesthetics and you can't get a decent wi-fi signal anywhere. As for killing your own grandfather, forget about it.
The same is true for going back in database versions. In 2009 I had gone straight (more...)
The database on the Developer Days Database App VBox appliance doesn't have the SCOTT schema. This is fair enough, as the sample schemas aren't include by default any more (for security reasons, obviously). I know the official sample schemas
used in the documentation - HR, OE, and so on - (more...)
May 23, 2013 The following question recently came through an ERP mailing list (significantly rephrased): I would like to use the Microsoft Query tool in Microsoft Excel to extract records from the ERP database. I would like the list of parent records to be retrieved into Excel when all (more...)
Matrix : What you must learn is that these rules are no different than rules of a computer system. Some of them can be bent, others can be broken. Understand?
Usually an unique index grants the uniqueness of all rows in a specific table which have non-null values; But (more...)
Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but (more...)
Tom is not not a fan of public synonyms, here is why:
- public synonyms pollute the namespace.
- public synonyms can lead to security issues.
- public synonyms can lead to a maintenance headache.
- public synonyms are public – no one owns them.
So, instead of public synonyms…
create PRIVATE synonyms or (more...)
May 1, 2013 I saw a decent SQL brain teaser this morning in the comp.databases.oracle.server Usenet group. The OP in the message thread is attempting to summarize data in one of his tables, with the summarizations broken down by month and then the week within that month. (more...)
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.
Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2
Here is (more...)
April 25, 2013 Roughly 12 years ago I was attempting to analyze customer order changes that were received through electronic document interchange (EDI), specifically X12 830 documents that show order forecasted demand for specific part numbers. At the time, the EDI data was partially transformed and inserted into an (more...)
Yet another performance tuning story
, similar to one that happened about a month ago
. Just to keep it short:
- once upon a time there was a very time-consuming module.
- eventually we were forced to take much closer look - WHY is it so time-consuming
- we found in our own (more...)
Most of us here follow the good practice of executing our SQL statements directly against the database before using them within an SQLExec in PeopleCode, a view or an SQL actions in App Engines. Even after following this, we might get hit in some situations. Read on to find more.