Archiwa tagu: Oracle

MERGE Alias Bug – No Error Thrown When Using Wrong Alias

Alas

Back in the days, when I was just beginning working as an Oracle developer, I used to view Oracle Database Server as such a great piece of software to the extent where I wouldn’t even consider that a problem which I’ve just encountered wasn’t my fault, but Oracle’s bug!

Why am I bringing this up, you might ask? Well, a few weeks ago I’ve stumbled upon a weird bug in Oracle. I was using Oracle 11.2.0.1.0. Take a look at the below example: Czytaj dalej

Answer to Quiz #2: NULL in Aggregate Functions

Mind the NULL!

1 Quiz #2 – NULL in Aggregate Functions

Hello again! It’s due time to provide the answers for my second quiz – the one about NULL in aggregate functions.

In this particular quiz, it wasn’t the point to pick the correct choices, but rather figure out what will be the result of running each of the SELECTs. Czytaj dalej

Quiz #2: NULL in Aggregate Functions

Impact of NULL in Aggreaget Functions

Aww. So dusty in here. Where have I been for the past seven months?!

Either way, it’s that time. Yep, you know what I mean – it’s the QUIZ TIME!

I’ve got a new quiz for you today:

What will be the result of running each of the SELECTs? Czytaj dalej

Quiz #1: Package Dependency

Packages and Dependent Objects

I’ve got the following quiz for you today:

After running which of the following code snippets will the ‘INVALID’ status be returned?

Assumptions:

  • Version of Oracle: 11g R2.
  • Before each snippet is run, both dummy_pkg and print_message objects do not exist.

I’ll provide the answer in my next post.

The choices:
Czytaj dalej

Text Index With a Sync Parameter

Subtitle is on holidays.

Oracle Text Index with SYNC (EVERY …) Parameter

Just a quick tip for today. Oracle allows us to create special kind of indexes on columns containing long texts. One of them is the CONTEXT type. You don’t need any particular privileges to create an index of this type (other than being able to create indexes in you schema). At least, that’s what the documentation says.

There was a question about creating a CONTEXT index on StackOverflow:

Can’t create oracle text index with sync every

Czytaj dalej

Returning BLOB From Embedded Java

- Waiter! There's a BLOB in my Java!

Returning a BLOB from a Java Method Embedded in a Database

You’re about to learn your future

Oracle supports embedding Java classes in its database. Different SQL types are mapped to corresponding Java classes to allow us to make the most of this feature. Author of the following question on StackOverflow:

create-java-sql-blob-instance-in-java-stored-procedure

had an issue with returning a BLOB object from Java method back to PL/SQL context. In the beginning, I didn’t even think you could return a new BLOB object from an embedded class. Fortunately, there was a BLOB in my Java, too. Czytaj dalej

How to Pass an Array of Object Type
To Static Function of That Type

Solution shorter than the title

Array of an Object Type Argument in a Function of That Object Type

Interesting question regarding Object Types was asked not a long ago on StackOverflow:

How to Pass a Nested Table of Object Type to a Function of That Object Type?

What author was trying to accomplish was to declare a static function in an object type which would take as a parameter an array of objects of the object type in which it was declared.

Is that even possible? (dramatic pause) Czytaj dalej

Top-N Queries & The New Row Limiting Clause
11g & 12c

Sponsored by 'c'. Letter 'c'.

Getting the Top-N Records From an Ordered Set &
The New Row Limiting Clause – 11g & 12c

and a cup of tea if you’re lucky

I bet my cup of raspberry-juiced black tea that, somewhere along your journey with Oracle, you had to write a query which was supposed to return only the top-n rows from an ordered set. Unlike some of the other databases, MySQL, for instance, Oracle does not provide a dedicated solution to this problem.

At least, not before the 12c hit the stage.

Before I introduce you to the nice Row Limiting Clause, let me show you why the first solution that comes to mind to solve the problem at hand, in Oracle’s versions prior to 12c, is not the right one, and what voodoo tricks one has to perform to achieve the expected result. Czytaj dalej