Category Archives: Oracle

The UK Oracle User Group writes…

Posted on by
Reply

I was asked to pass on the following, and I have no hesitation in doing so:

I am unsure whether you are aware but we are now under three weeks away from closing Call for Papers for the UKOUG Conference 2012. As a well known Oracle blogger, I would like to ask if you wouldn’t mind mentioning the Call for Papers in your blog?

I would greatly appreciate your assistance to encourage more potential speakers to come forward to present, especially if sharing hints and tips or lessons learnt, which as you know user group conference attendees find so valuable.

If this is possible, I’d be extremely grateful and would love to see the finished article once online.

The link to the Call for Papers page is as follows – http://2012.ukoug.org/default.asp?p=9306

To assist, please see the end of the email for some extracts taken from a fantastic blog written by James Koopmann in support of a previous UKOUG Conference Call for Papers.

Should you have any further questions, please do not hesitate in contacting me.
Additionally, your blog has been very entertaining!

Many thanks,
Marisa Harris
Project Manager
UK Oracle User Group
591-593 Kingston Road | Wimbledon | London | SW20 8SA
www.ukoug.org

“If you have never been to the prestigious UKOUG Conference, you are missing something special.”

“The UKOUG annual conference is regularly rated as ‘top of the list’ for events to attend.  The International representation is very strong and with the proliferation of a global economy, this is one of those conferences where you can actually get a flavour and appreciation for the issues and concerns on a global scale.  

“Organised by an independent User Group, there is a great sense of community bringing Users, Partners and Oracle together for great days of learning, discussion and networking.”

“One of the main reasons for visiting this conference is to gain insight into solutions that are being developed within the database community. Some of these vendors are specific to this region of the world and your only avenue to meet them face to face, is by going to the UKOUG conference”

“Also there is no better way to get to know the issues than to actually talk to the users of Oracle products. Do you want to actually go through the headache of solving the same problem someone else has already solved?  Sparking a conversation with a like-minded attendee that has had the same, or even similar, issue as yours allows you to drastically reduce the time involved in producing viable solutions and getting to ‘implementation’ sooner.”

“The UKOUG actively encourages the Oracle Community to get involved, this is your user group, it’s for you and about you. Share your knowledge and experiences with your peers by submitting an abstract and telling your story.”

I have to say, I’ve never been to UKOUG, though I’d like to, if only I had something to talk about. (And someone to pay the fare!) I have read lots of reports over the years from people who have been, though, and it sounds to be one of those user groups that remain fresh, informative, entertaining and (somewhat unusually these days) worthwhile. So, if you happen to be a reader of mine, with something Oracle-y to share, and are geographically proximate to the UK at the relevant time, please get in touch with Marisa.

Moving swiftly on…

Posted on by
10

An interesting problem came up at work the other day. A DBA had been trying to import data from one database (created in a standard 8-bit characterset) to another (created in the UTF-8 characterset). The import failed because, on one table, the length of the data being imported exceeded the length of the column in the new database.

How could this be? Well, the column was a VARCHAR2(10) in both databases, but the new database was using a variable length characterset: UTF-8 allows between 1 and 4 bytes per character. Something that had been stored as a single byte in the original database was now being stored as a 2-, 3- or 4-byte character in the new database, thus blowing the budget on the original 10-bytes allowed for this particular column. (For the record, Microsoft’s smart quotes will do this!)

As a workaround, and a way to allow the import to finish without incident, the DBA involved made the decision to pre-create this table in the new database, before doing the import, with a declaration of VARCHAR2(10 CHAR) -the use of character semantics in this way ensures that the column is allowed to store 10 characters no matter how many bytes might be needed to do so. And that certainly solved the import problem.

But it presented a new worry, the answer to which I didn’t know for sure, but could only guess: if there was PL/SQL code in the original database which referenced that column and loaded it into a variable declared as a VARCHAR2(10), would that code now break when it tried to run in the new database?

Put it another way: does PL/SQL use the overall database byte/character semantics? Or does it match the table semantics it’s run against?

My guess was that the code would break, because PL/SQL would use the default database semantics for column/variable length, not the actual semantics used in any specific case. But I didn’t know for sure, and so a test was in order.

Well, the first thing is to make sure our client session is working in the right characterset, otherwise all bets are off:

[oracle@watt ~]$ export NLS_LANG=.AL32UTF8

And here’s the initial state of play:

SQL> select property_name, property_value
  2  from database_properties
  3  where property_name like 'NLS_CHAR%'
  4  or property_name like '%SEMANT%';

PROPERTY_NAME                PROPERTY_VALUE
---------------------------- ---------------------
NLS_CHARACTERSET             AL32UTF8
NLS_LENGTH_SEMANTICS             BYTE

So both my client and database use the AL32UTF characterset (variable in length up to 4 bytes per character). My database also uses “BYTE semantics” (meaning that if something is said to be VARCHAR2(10), the ’10′ means 10 bytes, not 10 characters. This is actually the default value for NLS_LENGTH_SEMANTICS, and is how most Oracle databases (probably) would be configured.

Now let me create a table and do what my original DBA did: over-ride the default length semantics and make a column capable of storing a fixed number of characters no matter how many bytes are needed to store them:

SQL> create table hjr_t1 (col1 varchar2(10 CHAR));

Table created.

By specifying ‘CHAR’, I’m insisting that the ’10′ in the varchar(10) bit of syntax means ’10 characters’. No matter how many bytes are needed to store them all. This hopefully explains the following output:

SQL> select owner, data_length from dba_tab_columns
  2  where table_name='HJR_T1';

OWNER                          DATA_LENGTH
------------------------------ -----------
SYS                                     40

Since UTF-8 is a variable width character set that, sometimes, needs four bytes to store certain characters, Oracle has internally made the column 40 bytes long to deal with that possibility.

Now let me store some data in this table:

SQL> insert into hjr_t1 values ('ÁÇÖÜßîĂĆŐş');

1 row created.

SQL> commit;

Commit complete.

So that’s 10 ‘foreign’ characters, and they will each definitely take up more than a single byte of storage. You can see that with this query:

SQL> select col1, length(col1), lengthb(col1) from hjr_t1;

COL1                 LENGTH(COL1) LENGTHB(COL1)
-------------------- ------------ -------------
ÁÇÖÜßîĂĆŐş                     10            20

The LENGTH function always shows you the number of characters in a string (and has done so for all time). The LENGTHB function, though, shows you the number of bytes a string represents. Again, in a single-byte characterset environment (or one that only used plain English), there wouldn’t be a difference between the two… but that doesn’t apply here!

So now the original question: if I write code that assigns table data to a VARCHAR2(10) variable, what will happen? Er, this:

SQL> declare
  2  v1 varchar2(10);
  3  begin
  4  select col1 into v1 from hjr_t1;
  5  dbms_output.put_line(v1);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

So I was right in my initial guess: code which mentions varchar2s without specifying byte or character semantics, when the database itself uses BYTE semantics by default, is assumed itself to have meant byte semantics… and when transposed to a multi-byte characterset database, that code can fail.

Of course, you could change your database semantics:

SQL> alter system set nls_LENGTH_SEMANTICS=CHAR;

System altered.

SQL> set serveroutput on
SQL> declare
  2  v1 varchar2(10);     
  3  begin
  4  select col1 into v1 from hjr_t1;
  5  dbms_output.put_line(v1);
  6  end;
  7  /
ÁÇÖÜßîĂĆŐş

PL/SQL procedure successfully completed.

And you could even re-write your code: if you specifically declare v1 to be varchar2(10 CHAR), the problem goes away, too.

But there’s a further problem:

alter system set nls_LENGTH_SEMANTICS=BYTE;

That just makes the database use byte semantics once more, as our original database would have done. Now let’s write some stored procedural code:

SQL> create or replace procedure hproc
  2  is
  3  v1 varchar2(10);
  4  begin
  5  select col1 into v1 from hjr_t1;
  6  dbms_output.put_line(v1);
  7  end;
  8  /

Procedure created.

Now that’s pretty much exactly the same code as before, only now it’s part of a stored procedure. Let’s try executing it:

SQL> exec hproc
BEGIN hproc; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.HPROC", line 5
ORA-06512: at line 1

And we get the expected problem about the data not fitting the 10-bytes allocated to the v1 variable. No problems: let’s switch to CHAR semantics:

SQL> alter system set nls_LENGTH_SEMANTICS=CHAR;
System altered.

SQL>
SQL> exec hproc
BEGIN hproc; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.HPROC", line 5
ORA-06512: at line 1

Ooops. It seems that stored procedures created when BYTE semantics are in force always use BYTE semantics, even if you alter the system to use CHARACTER semantics.

Simply put, if you’ve got a bazillion triggers, functions, packages or procedures that thought they were dealing with a column of 10 bytes, they will always expect 10 bytes, even if you change the default database semantics to be characters. You could always switch the semantics to CHAR and re-compile all your code, of course. At the point of re-compilation, all mentions of varchar2(x) would be assumed to be referring to ‘x characters’. That would get my code working once more… but it’s an awful lot of re-compilation and therefore isn’t exactly a trivial fix on a big database! The other problem with doing that is the fact that you’d end up, effectively, assigning 4 times the amount of memory to every varchar2 variable ever used, anywhere… even though the DBA only altered the semantics on one column in one table. Goodbye code breakage -but hello memory problems!

The original DBA has, therefore, created a bit of a problem! Certainly, creating the column as VARCHAR2(10 CHAR) allowed the import to proceed. But now we have, potentially, lots of code in the database that will suddenly have to deal with 2, 3 or 4 times more data than it was expecting to, and it will break as a result. No ‘alter system’ or ‘alter session’ command can fix that up, either!

As it happens, the only data that was preventing the initial import were things like Microsoft smart qotes, em-dashes and other wonders produced by Word ‘auto-correcting’ what you type. None of that data was really indispensable, in other words: we could have done a bulk bit of data conversion using the TRANSLATE function, swapping smart quotes for dumb ones, for example. Had we done that, there would have been no need to mess about with character or byte semantics in the first place: UTF-8 remains a single-byte character set when it’s dealing with basic English, so no column data would have threatened to ‘expand’ during the import. And thus none of our code would be liable to break (at least, not for these reasons!)

As it is, the imported database is now in production …and we have a bit of an issue on our hands!

The moral of the story: don’t mess with your semantics. Pick one when you first create your database and leave it at that. Don’t start mixing-and-matching for different tables, still less for different columns within the same table… for that way, chaos lies!

Finding Numbers

Posted on by
10

What is the best way of working out if some column value is truly numeric?

Suppose I create a table like this:

create table hjr (col1 varchar2(30));

begin
for x in 1 .. 17000000 loop
  insert into hjr values (x);
end loop;
commit;
end;
/

So there are now 17 million records in that table, all of which have a VARCHAR2 column filled with data which is actually, if you looked carefully at it, purely numeric data. But now I now do this:

update hjr set col1 = '186298342kjdfvf9' where col1=327642;
commit;

So, now one row has some non-numeric data in the COL1 column: what’s the quickest way of getting Oracle to find which row has non-numeric data? Well, just to make sure I wasn’t re-inventing the wheel, I did a Google (actually a quackquackgo… but that just sounds a bit weird!) and these are the top two searches that came up:

Both sites suggest using Oracle’s TRANSLATE function to swap blank space for any known numeric values found in the input string. If a string were truly numeric, therefore, it would end up containing nothing, and its length would be zero. The second site is a bit tricky to get to these days for some reason -I had to check the Google Cache of it, and the page’s source code to be sure.

But Technonthenet, in particular, is a site I’ve used many times and the quality of whose writings I have no qualms about. So to see it suggesting the ‘translate’ route is interesting and reassuring -because that’s exactly the way of doing things I was going to suggest using anyway!

Here’s a little function, for example:

create or replace function isnumeric (p_string IN varchar2)
 return number
 deterministic
 as
begin
 if nvl(length(trim(translate(p_string,'1234567890.',' '))),0)>0 then
   return 0;
 else
   return 1;
 end if;
end;
/

The translate function swaps found characters for something else. In this case, I’m asking to swap the numbers 1, 2, 3 and so on for a space (‘ ‘). I then use the TRIM function to strip out spaces. I then measure the length of anything that’s left. So, the number ’667′ would have been translated to ‘   ‘ (3 spaces); trimmed to nothing, and thus caused a ’1′ to return (1 means “it’s a number”). But if the input being tested was ’667xf’, then there are no instructions as to what to replace the letters ‘x’ and ‘f’ with, so that would have been translated to ‘   xf’, trimmed to just ‘xf’ and that would have caused a 0 return (0 meaning ‘it’s NOT a number’).

As I say, this was the way I thought I’d go independently, before finding out that respectable sources had been there a long time before. It’s good to know you’re not alone!!

But here’s a second idea I had:

create or replace function isnumber(p_string IN varchar2)
return number
deterministic
as
 v_number number;
begin
 v_number := p_string;
 return 1;
exception
 when others then
 return 0;
end;
/

This one’s much cruder. It creates a numeric variable (v_number) and then attempts to assign the input string to it. It’s therefore relying on Oracle’s ability to implicitly convert between data types -and, in this case specifically, on its ability to cast between number and string data types. Should the implicit cast work, the function returns a ’1′: it can only have worked if the input string really did contain only-numeric numbers. Should the implicit cast fail, the function will error -but we have an error handler to deal with that. The error handler simply returns 0, indicating that something in the input string could not be implicitly converted to a number.

I think there’s no doubt that this is a much riskier proposition than before. You are, after all, relying on Oracle’s implicit (and not extensively documented) behaviour, which might change between database versions. You’re also relying on a ‘when others’ error handler -so you’ll be told something’s non-numeric if the code ever errors out for completely spurious reasons.

That said, I know this code has worked on every Oracle version from 8.0.6 through to 11.2.0.3, so if we’re worrying about Oracle changing its product’s behaviour, we’ve been worrying for a long time! Moreover, this code doesn’t seem especially complex, so the chances of triggering the ‘when others’ exception unreasonably seems pretty remote. On both grounds, therefore, maybe we can ‘get away with it’?

So: we have an ISNUMERIC function, which is well-behaved and accords with what others have written; and there’s an ISNUMBER function, which depends on implicit datatype conversions and some other assumptions about Oracle behaviour.

We obviously need some speed and efficiency comparisons. So, first up, here’s the average runtime for ISNUMBER, and the main statistics associated with it:

SQL> select count(*) from hjr where isnumber(col1)=0;

Elapsed: 00:00:27.62

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
     31234 consistent gets
     31229 physical reads
         0 redo size
       526 bytes sent via SQL*Net to client
       523 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

And here’s the equivalent for ISNUMERIC (as before, the elapsed time shown is actually the average of five runs, with the longest and shortest discarded):

SQL> select count(*) from hjr where isnumeric(col1)=0;

Elapsed: 00:02:00.29

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
     31234 consistent gets
     31229 physical reads
         0 redo size
       526 bytes sent via SQL*Net to client
       523 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

That’s quite a difference! The method involving implicit casts and error handlers copes in about 27 seconds instead of 129… it is, in other words, about five times as fast in returning a result -and the statistics don’t seem to indicate anything particularly different about either method (same consistent gets, same SQL round trips and so on).

So this is a vote for doing things the dirty way?

Well, let’s alter the experiement a little before we agree to that. Let’s make rather more non-numerics than before:

delete from hjr where isnumber(col1)=0;
update hjr set col1=col||'u8#' where mod(col1,2)=0;
commit;

Thanks to a bit of help from the MOD(2) function, half the rows now contain non-numeric data in the COL1 column. Let’s now re-run our tests. First, the ISNUMBER function, relying on implicit data type casting and error handling:

SQL> select count(*) from hjr where isnumber(col1)=0;

Elapsed: 00:00:55.45

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
     32111 consistent gets
     17789 physical reads
         0 redo size
       529 bytes sent via SQL*Net to client
       523 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

Interesting that the number of physical reads has reduced so dramatically now that half the data is non-numeric! Note, too, that this now completes in about 55 seconds, which is about twice as long than the 27 seconds it took to find one bad row out of 17 million good ones.

But anyway: here’s the ISNUMERIC version, relying on translating away any numeric characters and counting the length of what’s left:

SQL> select count(*) from hjr where isnumeric(col1)=0;

Elapsed: 00:02:01.85

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
     32111 consistent gets
     17791 physical reads
         0 redo size
       529 bytes sent via SQL*Net to client
       523 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

These are the same sorts of statistics as with the other function, including a big reduction in physical reads-but the elapsed time is still just over 2 minutes… which is almost exactly the same time as it took to find just one non-numeric row first time around. The ‘translate away’ technique is slow, therefore, but at least it’s consistently slow!

The technique that relies on implicit data conversions and error handling remains much faster -but the more and more exceptions it has to throw, the slower it gets.

If you are looking for the proverbial needle-in-a-haystack, then, I reckon the implicit-cast-and-catch might be the way to go. But if you are unsure of your data -specifically, if you are not sure if a significant part of it might be ‘bad’- then the ‘trim(translate))’ function might be more reliable.

Look Ma! No network…

Posted on by
Reply

I was installing some servers in Seattle recently, when I was informed that it was not company policy to allow their servers to have Internet access, of any sort, ever. This was a bit of a blow for me, because my Gladstone script (which I use to configure production Red Hat boxes as Oracle database servers) relies on being able to do various “yum install …” commands to get the software prerequisites correct.

It was irritating, though quite understandable -and we worked around the issue by giving me temporary access to the Internet, swiftly revoked once the installs were complete. But the incident made me realise that Gladstone’s reliance on Internet connectivity was misguided.

In fact, it’s never been strictly necessary for Gladstone to have Internet access at all: every one of the software prerequisites are available on the DVD installation media for RCSL distros, so it’s always been possible to install entirely from locally-available media. I used the ‘yum install’ method simply because it was easier: for one thing, it ensured all software dependencies were satisfied automatically.

Well, I have now resolved that particular issue.

My new Kickstart Configurator tool will now output a kickstart file which will perform a completely local installation (i.e., no Internet downloads) that nevertheless satisfies all Oracle software prerequisites. Of course, there’s still the Palmerston script which needs to be downloaded and run to finish things off in an interactive fashion, but if you download that ahead of time and store it on your Kickstart server, you can transfer that internally, still without recourse to the wider Internet.

Kickstart + Palmerston… perfect results every time, and not an external network in sight. My man in Seattle would be happier, I think!

Alas, poor Romulus

Posted on by
Reply

As time races towards the point where the last bits of my Sydney server room are moved to Seattle, this was one of the more poignant moments: the point where I shut down my OID (Oracle Internet Directory) server, which has been doing faithful names resolution duties for quite a while:

The load averages are nothing to write home about, but that box has been running uninterrupted for 2 years and 17 days. Until now.

Oh -and look. It is possible to use CentOS (4.x) in a production environment!

Fedora 16 (alpha!)

Posted on by
Reply

The first alpha release of what will eventually become Fedora 16 has been released -I got my copy from here (update: that link is obviously now to the production release DVD of Fedora 16!).

The default artwork for the release (see left) is, to my eyes, frankly alarming -well, if not alarming exactly, at least not very good! It’s only a wallpaper change away, but I do wish Fedora would stop trying to theme their desktops to match their fairly arbitrary choice of version codename (in this case, Fedora 16 is codenamed Jules Verne, as in Twenty Thousand Leagues Under the Sea. Yeah, I think it’s a bad idea for a desktop theme, too!)

And I’m still not convinced by Gnome 3 (initial horror with Fedora 15′s implementation of it gave way several weeks ago to keen enthusiasm, but that has since been replaced by indifferent dislike… there’s nothing much I’ve seen in Fedora 16′s implementation that counts as a major improvement). Otherwise, there’s the usual software-stack updates (Firefox is at version 6, for example; and you get 3.4.2 of LibreOffice thrown in), but most of the changes are, I think, under-the-hood stuff and aren’t likely to revolutionise your Linux life!

I have successfully tested a Gladstone-prepared 11g Release 2 installation on the new release. There is the usual pile of software the OUI claims doesn’t exist (click Ignore All when prompted, because they do). There is also the expected ‘Error in invoking target agent nmhs of makefile ins_emagent.mk‘ problem during the linking phase. The workaround here is to run the fedora-linking-error-fix.sh script in your Desktop directory which Gladstone will have created for you (just navigate there with Nautilus and double-click the shell script when the linking error occurs, then click Retry in the Oracle Universal Installer. It’s plain sailing after that).

Interestingly, one of the reasonably significant changes made in this release (enough to get a mention in the release notes, anyway) is that, by default, a Desktop directory is no longer created for you during installation. However, Gladstone is hard-coded to write its fix-up script there, so a /home/<username>/Desktop directory needs to exist: create one before you start if you need to.

Of course, there can be no guarantees: what works in an alpha release might be broken by any subsequent beta, let alone the final, finished distro. But since it’s working right now, a revised, Fedora 16-aware, version of Gladstone is downloadable from the usual place.

Incidentally, I have not tested a 10gR2 installation on Fedora 16, and won’t be doing so. As was mentioned in various discussions on this blog lately, 10g Release 2 is out of mainstream Oracle support these days, and I don’t therefore propose to spend any more time on it from now on. So Gladstone may or may not work for 10g: feel free to give it a whirl, I guess.

Nice!

Posted on by
5

I feel moderately pleased with myself:

That’s a work system that involves querying a table with about 16 million rows containing 12GB of free-form text (via an Oracle Text index) about five times a second, with response times over the web of less than 0.5 seconds. It probably counts as a bit of tiddler in the great scheme of things, but when I took it on, they had a quarter of the records with average response times of 26 seconds. I feel as if I’ve earned my money!

(Apropos nothing in particular, their uptime was originally less than 90 days… they were running Windows 2003. I’m just sayin’…)

It would have been longer, too, except the SysAdmin caused the rack to short one day about a year and a month ago (by plugging in a USB device somewhere. Go figure!)

Oracle Article

Posted on by
Reply

As a follow-up to the article on building an Apache box is a new article explaining how you can use it to help automate the build of an Oracle database server.

The new article takes you from building a virtual machine, installing the operating system using Kickstart configuration files, running Gladstone to get all the Oracle prerequisites right through to getting Firefox displaying Flash properly and setting a fixed IP address.

Available in the usual place.

Forum…

Posted on by
Reply

I am getting quite a number of technical questions via email and as comments posted to various blog posts here, which is all fine… but is a complete mess, with quite often the same technical reply posted multiple times in different places.

I’ve therefore decided to open a forum where all technical questions should be asked. It’s linked to in the ‘metadata column’ at the right, but you can also visit it by clicking here.

Having been down the road of running forums before, I can tell you I’m very reluctant to do it again -and, as a result, I don’t intend to spend any time moderating it or doing anything much other than see if someone’s posted a question and answering it if I can. If it works, it works and if it doesn’t, I’m not going to worry about it.

LibreOffice & Oracle

Posted on by
4

I don’t often try and connect to Oracle from LibreOffice, but had to today… and got completely stuffed whilst doing so!

There are two essential problems.

The first is that I run Windows 7 64-bit and LibreOffice only comes as a 32-bit Windows download. This means, in turn, that a 64-bit Java Runtime Environment is not recognised by LibreOffice, which therefore complains that no JRE exists whenever you try to connect to a database with its “Base” application. The fix here is to install a 32-bit JRE, which you can do by going here and making sure to select the Windows 7/XP Office (32-bit) download. 32-bit and 64-bit JREs can co-exist on the same PC, so installing both is not an issue.

The second is that Base connects to Oracle via its ‘Oracle JDBC’ option, which is there from the get-go… but which won’t work because it doesn’t know how to load the necessary Java classes. The fix here is to make sure you know where your ojdbc6.jar file is (which contains the necessary classes): on my Windows laptop on which I’d previously installed a complete 11g Release 2 database, that file can be found in %ORACLE_HOME%jdbclib (which, in my case, is c:apphjrproduct11.2.0dbhome_1jdbclib). With that location in mind, you need to open any of the LibreOffice applications, go to Tools > Options > Java and click the Class Path button. Click the Add Archive button and navigate to the …jdbclib directory and point it at the ojdbc6.jar file. Click OK as appropriate to store the new setting.

At which point, you should be good to go.

If, as is quite likely, you don’t have a complete Oracle installation on your client PC, and you’re disinclined to install a big, fat client either, then you can simply download the necessary ojdbc file from Oracle and stick it somewhere convenient. Point your LibreOffice Java Class Path at it, wherever it might be, and you should be good to go. For example, this shows you I have no Oracle client or database installed on this particular PC:

…you’d see something like ‘Oracle – OraDB11g_home1′ in the menu if I’d installed an Oracle database or client. But I did download the ojdbc6 file to my desktop -and here’s the LibreOffice setting for that:

And having set that, it’s then trivial to point LibreOffice’s Base at my Oracle database:

After which, standard ‘Access-like’ stuff becomes possible. Here’s LibreOffice’s Base and the good ol’ EMP table:

In short, it’s all relatively painless -provided you to stick to a 32-bit client/JRE and you know how to point your LibreOffice installation to your ojdbc class library.