In most cases, simply creating an Oracle SEQUENCE with all defaults is good enough: CREATE SEQUENCE my_sequence; This sequence can then be used immediately in triggers when inserting new records in a table: CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT ON my_table FOR EACH ROW -- Optionally restrict this trigger to -- fire only when … Continue reading Don’t Forget to Set the SEQUENCE CACHE Size
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
QueryDSL vs. jOOQ. Feature Completeness vs. Now More Than Ever
This week, Timo Westkämper from QueryDSL has announced feature completeness on the QueryDSL user group, along with his call for contributions and increased focus on bugfixes and documentation. Timo and us, we have always been in close contact, observing each other's products. In the beginning of jOOQ in 2009, QueryDSL was ahead of us. But … Continue reading QueryDSL vs. jOOQ. Feature Completeness vs. Now More Than Ever
What if Developing a Simple Data Management Application Just Took 1-2 Days?
What if developing an application just took 1-2 days? What if I can create it myself with only 10 clicks? What if I don't need you developers anymore? Said every manager since the beginning of history. This is what all managers dream of. Click click click, next next next, and you're done! Easy, right? Time-to-market: … Continue reading What if Developing a Simple Data Management Application Just Took 1-2 Days?
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong shouldn't be about that You're-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
Oracle Tip: v$sql Table or View does not Exist
If we want to analyse execution plans on the SQL console, we probably need to find a SQL_ID first, which we can the pass to the DBMS_XPLAN.DISPLAY_CURSOR function. One way to find this SQL_ID is by querying the v$sql table first, e.g.: SELECT last_active_time, sql_id, child_number, sql_text FROM v$sql WHERE upper(sql_fulltext) LIKE '%SOME_SQL_TEXT%' ORDER BY … Continue reading Oracle Tip: v$sql Table or View does not Exist
SQL Server Trick: Circumvent Missing ORDER BY Clause
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: -- Get arbitrarily numbered row_numbers SELECT ROW_NUMBER() OVER () -- Skip arbitrary rows SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) OFFSET 3 ROWS Strictly speaking, that … Continue reading SQL Server Trick: Circumvent Missing ORDER BY Clause
The Index You’ve Added is Useless. Why?
Recently, at the office: Bob: I've looked into that slow query you've told me about yesterday, Alice. I've added the indexes you wanted. Everything should be fine now Alice: Thanks Bob. I'll quickly check ... Nope Bob, still slow, it didn't seem to work Bob: You're right Alice! It looks like Oracle isn't picking up … Continue reading The Index You’ve Added is Useless. Why?
How to Implement Sort Indirection in SQL
I've recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order. They wrote SELECT name FROM product WHERE name IN ('CE367FAACDHCANPH-151556', 'CE367FAACEX9ANPH-153877', 'NI564FAACJSFANPH-162605', 'GE526OTACCD3ANPH-149839') They got CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 GE526OTACCD3ANPH-149839 NI564FAACJSFANPH-162605 They wanted CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 NI564FAACJSFANPH-162605 GE526OTACCD3ANPH-149839 Very often, according to your … Continue reading How to Implement Sort Indirection in SQL
NoSQL? No, SQL! – How to Calculate Running Totals
If anything at all, our jOOQ talks at various JUGs and conferences have revealed mostly one thing: Java developers don't know SQL. And it isn't even necessarily our fault. We're just not exposed to SQL nowadays. But consider this: We developers (or our customers) are paying millions of dollars every year to Oracle, Microsoft, IBM, … Continue reading NoSQL? No, SQL! – How to Calculate Running Totals
How can I do This? – With SQL of Course!
Haven't we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine ... While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can I do This? – With SQL of Course!