Thursday Jan 19, 2017

Announcing the 2016 PL/SQL Challenge Championship for Database Design

In 2016, 340 people answered 6,185 quizzes in the Database Design quiz on the PL/SQL Challenge. I'm pleased to see so many people taking an interest in how to build database applications! 

And I'm delighted to announce the 50 players below have qualified for the Database Design Annual Championship 2016. They've all shown grit and a solid understanding of Oracle Database.

[Read More]

Thursday Nov 10, 2016

12 Things Developers Will Love About Oracle Database 12c Release 2 INFOGRAPHIC

It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud!

Here's an infographic summarizing my 12 favorite features. If you want to know about these in detail, read 12 Things Developers Will Love About Oracle Database 12c Release 2.

[Read More]

12 Things Developers Will Love About Oracle Database 12c Release 2

It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud!

With it comes a whole host of new features to help you write better, faster applications.

Here's my rundown of the top 12 new features to help you when developing against Oracle Database.

  1. Easier, Better, Faster, Stronger JSON
  2. Looooooooooong Names
  3. Robust Code using Constants for Data Type Lengths
  4. Listagg Improved On Overflow
  5. Lightning Fast SQL with Real Time Materialized Views
  6. Fast Estimates with Approximate Query Enhancements
  7. Verify Data Type Conversions
  8. Handle Casting Conversion Errors
  9. Single Statement Table Partitioning
  10. Automatic List Partitioning
  11. Mark Old Code as "Not for Use"
  12. PL/SQL Code Coverage
[Read More]

Thursday Sep 08, 2016

How to Convert Rows to Columns and Back Again with SQL (Aka PIVOT and UNPIVOT)

The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results!

To do this, I've compiled a table of medal winners from Rio for each sport:

OLYMPIC_YEAR  SPORT    GENDER  EVENT               MEDAL   NOC  ATHLETE                   
2016          Archery  M       Men's Individual    Gold    KOR  KU Bonchan                
2016          Archery  M       Men's Individual    Silver  FRA  VALLADONT Jean-Charles    
2016          Archery  M       Men's Individual    Bronze  USA  ELLISON Brady             
2016          Archery  W       Women's Individual  Gold    KOR  CHANG Hyejin              
2016          Archery  W       Women's Individual  Silver  GER  UNRUH Lisa                
2016          Archery  W       Women's Individual  Bronze  KOR  KI Bobae   

This is great when looking for a specific result. But what everyone really wants to know how their country fared overall. To get this you need to convert the table above to the final medal table:

Country	        Gold	Silver	Bronze
United States	46	37	38
Great Britain	27	23	17
China		26	18	26
Russia		19	18	19
Germany		17	10	15

To do this, you need to count the number of gold, silver and bronze rows for each country. Then create new columns to hold the results.

The question is, how?

[Read More]

Thursday May 05, 2016

How to Recover Data (Without a Backup!)

Ryan McGuire / Gratisography

It's the classic career-limiting maneuver: accidentally deleting data you weren't meant to. It's easy to do this as the result of mistakes such as:

  • Running a test script on production 
  • Getting the where clause wrong for a delete

    Doing this in test or dev is likely to bring the ire of your colleagues in IT. Do this in production and key parts of the business may come to a stop!

    In either case you'll want to get your data back as quickly as possible.  

    Restoring from backup can be a time consuming process. Time you don't have in extreme cases.

    Luckily Oracle can help you recover from many mistakes quickly - without needing a backup! In this post we'll look at how to undo the damage in the following cases:

    Ready? Let's begin! 

    [Read More]

    Tuesday Mar 22, 2016

    How to Create an Execution Plan

    When you’re trying to get SQL to run faster there’s one thing that’s vital: an execution plan. In this post we’ll investigate four ways to create these:

    But before we begin let's answer the question:

    What is an Execution Plan?

    The plan for a SQL statement is a set of instructions. This tells the database how to access the data and join it together.

    Plans come in two varieties:

    • Explain
    • Execution

    These sound similar. But they’re different. To understand why consider this example.

    [Read More]

    Friday Jan 29, 2016

    How to Find and Delete Duplicate Rows with SQL

    There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. 

    Image by Ryan McGuire

    But there are some things you don't want duplicated. Rows in your database tables are a perfect example. 

    So it's unsurprising a common question people working with relational databases have is:

      "How do I find duplicate rows using SQL?"

    This is often closely followed with:

      "How do I delete all but one of the copies?"

    In this post we'll look at how you can use SQL answer these questions. We'll finish by showing how you can improve your database so you never need to ask them again!
    [Read More]

    Tuesday Jan 19, 2016

    Why You Can Get ORA-00942 Errors with Flashback Query

    I've recently been playing with one of my favorite Oracle features: Flashback. More specifically, Flashback Data Archive.

    lightning last year by oompa123 (CC BY 3.0)
    This extends the power of flashback query. Normally you can only use this to query as far back as your undo allows. With Flashback Data Archive (FBA), Oracle keeps a permanent store of a table's history (up to the retention period). 

    So using this you can run "as of" queries over a larger period. This enables you to see what was in a table days, months or even years ago! You can also view all the changes to a table between two times. Enabling FBA is a quick and easy way to audit data changes.

    Even better, unlike standard flashback query, you can go back to a time before you ran DDL against the table. 

    And the best part? As of 12c it's FREE!*

    But I found myself hitting a strange error. After creating the archive and adding a table to it, I was getting "ORA-00942: table or view does not exist" errors:
      SQL> select * from products as of timestamp sysdate - interval '1' minute;
      select * from products as of timestamp sysdate - interval '1' minute
      ERROR at line 1:
      ORA-00942: table or view does not exist

    The table itself existed. So what's going wrong?

    [Read More]

    Wednesday Jan 06, 2016

    Finding the Previous Row in a Different Group Using SQL

    Imagine you have a table with a series of transactions. These are classified into credits or debits:
    ---------- ----------------- ---------- ----------
             6 01-JAN-2015 00:00 CREDIT             40
             7 01-JAN-2015 00:00 CREDIT             90
             8 01-JAN-2015 00:00 DEBIT              80
             5 02-JAN-2015 00:00 CREDIT            200
             4 03-JAN-2015 00:00 DEBIT              99
             3 04-JAN-2015 00:00 CREDIT             50
             2 05-JAN-2015 00:00 DEBIT             200
             1 06-JAN-2015 00:00 DEBIT             100
    Your boss has asked you to do the following:

    "I want a report showing the ID of the previous transaction of the other type, according to the transaction date. Now!"

    So for credits, you need the previous debit trans_id and vice-versa. In the case of ties (transactions at the same time), simply take the highest transaction id. So the output you want looks like:
    ---------- ----------------- ---------- ---------- ------------------
             6 01-JAN-2015 00:00 CREDIT             40                   
             7 01-JAN-2015 00:00 CREDIT             90                   
             8 01-JAN-2015 00:00 DEBIT              80                  7
             5 02-JAN-2015 00:00 CREDIT            200                  8
             4 03-JAN-2015 00:00 DEBIT              99                  5
             3 04-JAN-2015 00:00 CREDIT             50                  4
             2 05-JAN-2015 00:00 DEBIT             200                  3
             1 06-JAN-2015 00:00 DEBIT             100                  3

    You need to write the SQL to do this.


    [Read More]

    Thursday Dec 17, 2015

    Creating Multiple Tables in a Single Transaction

    The following quiz is taken from the PL/SQL Challenge:

    The Question

    Your boss has asked you to create a release script for the tables shown in this schema diagram:

    You should create these tables in the PLCH_APP_OWNER user.

    Which of the following choices will create the tables shown above?

    You can assume that you are connected to the database as the PLCH_APP_OWNER user for this question.

    [Read More]

    Monday Nov 30, 2015

    UKOUG Tech15 Sessions with the Oracle Developer Advocates

    UKOUG Tech15, Britain's largest Oracle technology conference, is just one week away! 

    The SQL Developer Advocates, Connor McDonald and Chris Saxon are both presenting. There are five chances for you to join us and expand your SQL skills. Our sessions are:  

    Super Sunday 6 Dec

    [Read More]

    Thursday Nov 05, 2015

    Calculate Stock Market Returns Using SQL II

    In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500 falls 2% or more during a day. This provided the best return on our money.

    As hinted, there are problems with this method though. Let's find out what these are and see if we can mitigate them. We'll use SQL magic to do this, including a look at the model clause. 

    First, let's review how feasible the 2% buy rule is. Our previous check for this was basic. How many times did this happen between the start of 2005 and the end of 2014? 
    [Read More]

    Wednesday Oct 28, 2015

    Finding the Longest Common Starting Substring Using SQL

    A poster asked an interesting question on Ask Tom today. This amounted to:

    How can I return a distinct list of the longest common sequences of characters at the start of a set of strings?

    For example, given the following strings:


    The longest sequences where there is at least one other row that starts the same are "emcpower" and "sd". So the output should be:


    How can you solve this using SQL?
    [Read More]

    Thursday Oct 22, 2015

    Calculate Stock Market Returns Using SQL

    What's the best strategy for investing in the stock market?

    Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what the highs and lows are! Only by looking back can we know the best times to buy and sell.

    So which approaches can we use? And, more importantly, how much money can we make by following them?

    In this post we'll use SQL to calculate stock market returns for different purchasing strategies. The purpose is to show how to write the queries. We'll see if we can learn anything about investing in the process. 
    [Read More]

    Thursday Oct 08, 2015

    What Is SQL Injection and How to Stop It

    Data breaches are a common occurrence. As described in The Securing Account Details story, SQL injection is a simple way to access data from exposed sites. 

    How easy is SQL injection and how bad can it be?

    In this post we'll take a look at how it's possible. We'll see how easy it is to access information from a database that is vulnerable to SQL injection. We'll finish up by showing how you can prevent it.

    Let's start with a simple HTML form. It accepts an email address. It passes this to a Java servlet. This looks up subscription details for the email.  

    [Read More]

    Welcome to the blog of Oracle's Developer Advocates for SQL! We're here to share the joy of SQL.


    « February 2017