Reblogged from So Many Oracle Manuals, So Little Time: See also : No! to SQL and No! to NoSQL The inventor of the relational model, Dr. Edgar “Ted” Codd believed that the suppression of physical database design details was the chief advantage of the relational model. He made the case (more...)
It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.
email@example.com > create table strange(i int not null, j int not null);
firstname.lastname@example.org > alter table strange add constraint unique_i unique(i) not deferrable
Sometimes to get the data displayed exactly how you want it, you need to employ some nested functions.
with t as (
select null n from dual union all
select 0 from dual union all
select 0.5 from dual union all
select 4 from dual union all
select 4. (more...)
Not all regular expressions are scary.
As the documentation
states, REGEXP_COUNT returns the number of times a pattern occurs in a string.
We can do this to simply count how many times the letter S appears in a string
select job, regexp_count(job,'S')
One of the reasons I
renamed this blog grassroots
-oracle was to occasionally illustrate some simple examples of functionality not everyone is aware of - or are maybe too scared to give it a go (or research).
Still today I occasionally work with people who aren't familiar with some (more...)
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...)
See also : No! to SQL and No! to NoSQL The NoSQL camp claims to have the lead in performance, scalability, and availability. Should you use SQL or NoSQL for your next project? A well-known database theoretician suggested that you weigh three factors, one of which is performance. He conceded (more...)
See also : No! to SQL and No! to NoSQL The inventor of the relational model, Dr. Edgar “Ted” Codd believed that the suppression of physical database design details was the chief advantage of the relational model. He made the case in the very first sentence of the very first (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...)
dbms_comparison package isn’t deployed when you provision a pluggable databases (PDBs) in Oracle 12c. It appears to be a simple admission. At least, it let me manually compiled the
dbms_comparison package with this syntax:
Somebody wants help provisioning a pluggable database (PDB). While it’s a new concept in the Oracle 12c database, I can sympathize with their plight. It wasn’t clear to me when first working with it, and I couldn’t seem to find a quick tutorial on the web. While it’s in (more...)
A friend asked me how to get an Oracle view definition out of a
LONG column and into a web application. I thought it was an interesting question because I ran into a similar problem when writing the Oracle Database 12c PL/SQL Programming book.
One of the new Oracle (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...)
SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison (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...)
I haven't installed 12c yet because
a) I haven't had the time
b) my Linux skills aren't the best
What I have done though is read through some of the documentation in the New Features
The APEX features are just regurgitating what's in 4.2, since that's what's shipped (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...)