Search

Top 60 Oracle Blogs

Recent comments

January 2016

PL/SQL context switch, part 2

This is the second blogpost on using PL/SQL inside SQL. If you landed on this page and have not read the first part, click this link and read that first. I gotten some reactions on the first article, of which one was: how does this look like with ‘pragma udf’ in the function?

Pragma udf is a way to speed up using PL/SQL functions in (user defined function), starting from version 12. If you want to know more about the use of pragma udf, and when it does help, and when it doesn’t, please google for it.

create or replace function add_one( value number ) return number is
        pragma udf;
        l_value number(10):= value;
begin
        return l_value+1;
end;
/

select sum(add_one(id)) from t2;

As you can see, really the only thing you have to do is add ‘pragma udf’ in the declaration section of PL/SQL.

ORA_ROWSCN – When Was My Record Commited

I was going to do a follow-up post to my post on USERENV(‘COMMITSCN’) just to describe the slightly better known but still overlooked (and possibly more useful) ORA_ROWSCN – but I don’t need to!

As Neil Chandler has done this excellent post on it

Go and look at that post, it has all the information and detail you were actually looking for.

However, for any of you search-engine-seekers who can’t face the pain of following a link {it’s such a hard life for the modern knowledge-by-mouse-click generation}, here are the bare bones:

Semijoin_driver

Here’s one of those odd little tricks that (a) may help in a couple of very special cases and (b) may show up at some future date – or maybe it already does – in the optimizer if it is recognised as a solution to a more popular problem. It’s about an apparent restriction on how the optimizer uses the BITMAP MERGE operation, and to demonstrate a very simple case I’ll start with a data set with just one bitmap index:

Raspberry Pi Version 2 Wifi Inconsistent Connectivity

I’d had some difficulty with WiFi consistently staying connected on each of my Raspberry Pi units.  I’d had a little time to look into it, but no research had offered an answer and the only fixes I’d come up with was a shell script I wrote to force a restart of the NIC, which helped some, but no improvement when I added an antenna, (I considered al

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: