Search

Top 60 Oracle Blogs

Recent comments

January 2016

SQL – is there a better way?

Next Tuesday at 11:20 am at Oracle headquarters there will be a panel discussion on SQL. You can participate whether you are there or not as we are collecting questions ahead of time and will video the panel to be posted later. The panel’s participants will be

PL/SQL context switch

Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:

-- A function that uses PL/SQL 
create or replace function add_one( value number ) return number is
        l_value number(10):= value;
begin
        return l_value+1;
end;
/
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id)) from t2;

Of course the functionality of the function is superfluous, it can easily be done in ‘pure’ SQL with ‘select sum(id+1) from t2’. But that is not the point.
Also, I added a sum() function, for the sake of preventing output to screen per row.

[Cloud | On-Premise | Outsourcing | In-Sourcing] and why you will fail!

error-24842_640I was reading this article about UK government in-sourcing all the work they previously outsourced.

This could be a story about any one of a number of failed outsourcing or cloud migration projects I’ve read about over the years. They all follow the same pattern.

Friday Philosophy – Database Dinosaurs

I’m guessing many of you reading this are over 40. I know some of you are actually beyond the half century and a couple of you are….older! If you are younger than 40, just print out this and put it in an envelope marked “read a decade later than {current date}”. It will have become relevant for you by then…

beware the network admin

Beware the network admin – creative commons, Elvinds

Video: Oracle Linux Virtual Machine (VM) on Amazon Web Services (AWS)

Continuing the cloud theme, here is a quick run through of the process of creating an Oracle Linux virtual machine on Amazon Web Services (AWS).

A few months ago I wrote an article about installing an Oracle database on AWS.

I updated the images in that article last night to bring them in line with this video.

Literals and VARCHAR2

We all know that comparing two VARCHAR2 fields of different lengths will be treated by Oracle as being not EQUAL:


SQL> drop table T purge;

Table dropped.

SQL> create table T ( x varchar2(10), y varchar2(10));

Table created.

SQL> insert into T values ('abc','abc    ');

1 row created.

SQL> select * from T where x = y;

no rows selected

SQL> select * from T where y = 'abc';

no rows selected

But interestingly enough, if there are no references to tables and/or columns, that is, you are just using plain literals, VARCHAR2 is not the datatype that is in play. For example:

A old story from the past

My own personal hell story was back in the late 1990’s, when moving database from one server to another.  We were recycling some of the hardware (disks and memory), so it was a unload-to-tape, reload-from-tape job. The servers were in Port Hedland (a rat-infested dusty 110-degrees-plus 98% humidity hell hole… Hi to anyone in Port Hedland … ). I was not physically present – don’t you love it when IT companies think “remote login” is always the best way…

Anyway, because of the dust issues, I unloaded three copies of the database to three tapes, and then did a verification read of all them. An on-site non-techie took care of unloading/reloading tapes for me.  He chucks the disk drives and memory from the old server plus the three tapes into a truck and drive 20 km’s across town (where the new server is). Then I spend an hour on the phone trying to explain blind to him how to plug all this stuff in to the new server.

He turns it on… smoke starts billowing out of the box.

Session level parallelism

If you’ve got a stack of large I/O operations to perform, you might want to take advantage of the all of the cores on your server. However, if the scripts are already in place, you might not want to be editing them individually to carefully add parallelism. A little known command is that you can set parallelism at session level.

Determining What Management Packs Are Needed

In Enterprise Manager 12c, there was a little known functionality where you could check what management packs you needed to be licensed for to use a particular page in Enterprise Manager. I referred to it many times at conferences I was presenting at, and almost always people did not realize the functionality existed. Let’s see how this works.

To start with, I’m going to go to the Performance Home page for a particular database (accessed via the Performance menu from the Database home page). Note I’m not particularly interested in what it shows me, I’m just using this as an example of a page that requires the licensing of a management pack. Once I’m on that page, I can follow the path Setup -> Management Packs -> Packs for this Page:

Problematic SQL ? PL/SQL is your friend.

So then… I’ve written a crappy piece of SQL.