view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Deprecated mysqli Functions

PHPDeprecation5_4

Having noticed the release of PHP 5.5.10 last week while I was speaking at UTOUG, I checked the deprecated

Capture MySQL Foreign Keys

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

Q2 2014 Brings Big Changes to the PL/SQL Challenge

Last year, I (Steven Feuerstein) floated the idea that I would end the daily PL/SQL quiz. Many players protested, and several agreed to help me with the workload by writing quizzes. So I agreed to keep the daily quiz going into 2014 and we would see how it goes.

It went pretty well. Some players did write quizzes (a big thank you most especially to Jeroen Rutte, who wrote a total of 7 quizzes, 5 of which were used in Q1). But I still spent and spend lots of time writing quizzes, which I enjoy greatly, but unfortunately is not sustainable.

Finally, Oracle has its own Identity!

create table from_12c_onwards (
you_can_have_your_pk number
generated always as identity
,
so_you_dont_have_to_type_it varchar2(10)
);

insert into from_12c_onwards (
so_you_dont_have_to_type_it
) values (
'yes'
);

insert into from_12c_onwards (
so_you_dont_have_to_type_it
) values (
'Yes'
);

insert into from_12c_onwards (
so_you_dont_have_to_type_it

Hobbyist at Heart

Fifteen years ago when I dove into JavaScript friends called me a hobbyist and said I was wasting my time. They had some basis in what they said because most of my work is server side with the database or operating system. However, they haven’t said that I wasted my time with the advent of NoSQL solutions, like Cassandra and MongoDB; or frameworks like node.js.

Merging contiguous date ranges

Last time I wrote about finding gaps in date ranges: this post is about merging date ranges that “meet”. This is a frequent question; the answer applies to any ranges, not just dates. As a reminder, I consider ranges to “meet” when the end value of one record is equal to the start value of […]

Gaps in Date Ranges: when are you free?

Calculating “free time” in Calendars is a very common task. Did you know it can be done by a SQL statement less than 100 bytes long? This post pays homage to an incredibly neat and concise use of analytic functions. The Question “Free time” in a Calendar means the unused time slots between events; in […]

SQL Quiz: How did my primary key get duplicate values?

Somehow I ended up with two rows in my table both with the same primary key value:
select the_pk
from dodgy_data;

THE_PK
----------
1
1

The primary key does exist and THE_PK is the only column in it, so we can’t insert another row with THE_PK = 1:

How Humans Lost Their Hands (and saved the world)

My first creation (and destruction) mythstory....

SQL for date ranges, gaps and overlaps

[Update 2014-07-04] Since this is my most popular post, I’m adding an index to all my posts about date ranges: This post right here explains terms like “gap, meet, overlap”, shows how to design date ranges in tables and explains what null values should mean. SQL and date ranges: don’t make NULL mean something argues […]

view counter