view counter

PL/SQL Feed

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

Oracle 12c Multitenant workshop – Bangalore chapter

Thanks to all folks who attended Oracle 12c Multitenant workshop in Bangalore. It was really a great session and well received by all the participants. I appreciate all your interests to know about Multitenant, play with the new architecture and explore the changes. Few points to make note from this event – 1.

SQL Brainteasers: Guess the Film Part 10

Back on the films today, here’s another six to get:

Hidden DBMS_JAVA Nuance

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

When v$sesstat statistics are updated

Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.

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.

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:

view counter