Oracle VM Consulting
view counter

PL/SQL Feed

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

COMPARE_SYNC: Introducing the package

This helper tool generates SQL for

  • Comparing tables, views and queries, both local and remote.
  • Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.

Participants and Dates set for (four!) 2014 Annual Championships

2014 is a thing of the past, but there are at least four more 2014-related activities to do: the annual championships.

With the expanding selection of weekly quizzes come an expanded set of annual championships.

This year we have four, and here are the dates and times:

PL/SQL Fall Through?

Somebody wants to know how you can write a PL/SQL solution that mimics the fall through of a switch statement because PL/SQL doesn’t support a switch statement. It’s a question that I found interesting because there wasn’t a need for it when I figured out what he wanted to accomplish. Essentially, he wanted to know how to implement a nested loop where the first loop runs in ascending order and the nested loop runs in descending order based on the value of the outer loop.

SQL Brainteasers: Guess the Chocolate Bar

Some more SQL clues, this time to chocolate bars.

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Convert to SQL Server?

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table name to database_user. That’s what I’ve done in this example.

Sync tables: generate MERGE using Unique constraint

How to generate SQL to compare and sync tables, when the target table has a UNIQUE constraint instead of a primary key. Warning: doesn't work if some rows have NULL values in all the columns used by the constraint.

MERGE using Change Data Capture (CDC)

Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.

Happy New Year, and....New and Revamped Quizzes for the New Year

Happy new year to the thousands of Oracle technologists who played a quiz or otherwise visited and benefited from the PL/SQL Challenge in 2014.

The Challenge continues into the very frosty New Year (as I write, it is 0 degrees Fahrenheit here in Chicago), but first, let's do (some of) the numbers from 2014.

Over 4000 Oracle technologist took at least one quiz in 2014. 

Goodbye Flybe, Hello Oracle!

update people
set employer = 'Oracle',
job_title = 'Database Evangelist'
where name = 'Chris Saxon';

That’s right, as of last week I’m now an Oracle employee! I’m joining Steven Feuerstein’s database evangelist team, a hugely exciting opportunity I’m honoured to be a part of.

view counter