Search

Top 60 Oracle Blogs

Recent comments

January 2016

Not using binds .. banging an obsolete drum ?

We’ve all seen the mantra – “you should be using binds”.  And this is a demo that’s been rolled out for decades now, showing the performance overhead of not using them:

MobaXterm 8.6

MobaXterm 8.6 has just been released. The downloads and changelog can be found here.

For SSH and X emulation, this is the best! I keep saying it, but give it a go. You won’t regret it. :)

Cheers

Tim…

Video: SQL Server Databases on Microsoft Azure

I mentioned in a previous post, the whole look and feel of Microsoft Azure has been rejigged. As a result, I had to do a run through of the SQL Server DBaaS stuff to update the screen shots in and old article on the subject.

Azure : SQL Server Databases on Azure

Since I was doing that, I figured I might as well do a video for my YouTube channel.

Cheers

Tim…

VirtualBox 5.0.14

VirtualBox 5.0.14 has been born.

Downloads and changelog are in the usual places.

I’ve not done the installation on Linux yet, but it installed and seems to work fine on Windows 7 and Mac OS X (El Crapitan).

Cheers

Tim…

Converting LONG to CLOB

Some folks still are stuck with LONG columns, and are keen to move to LOB.  Since version 9, we’ve had a nice facility to do that – just with a simple alter command.

You can now simply issue “alter table (longcol CLOB)” to perform the conversion. This is a neat tool, but be aware of the space implications before attempting a conversion.

Here is a simple example to demonstrate:

PL/SQL arrays–the index datatype

You get some interesting (but perhaps not unexpected) results when playing with the speed of array functions in PL/SQL.  This is a series of tests comparing “BY PLS_INTEGER” arrays with “BY VARCHAR2” arrays.  In all the cases, their speed is pretty much blindingly fast, but the comparison between the two seems to be dependent on the type of array function being performed. 

Anyway … on to the tests

Certification exams

This is an update of a blog item I made nearly 15 years ago … but I think it still holds true

Is doing the OCP exams worthwhile ?
Yes, but not for the reasons you may be thinking. The OCP exams are a relatively cheap way of identifying possible weaknesses in your knowledge base on Oracle. For example, when I did the OCP, all of the database sites I had worked on did not use MTS and thus the exams revealed an area that could be “swotted up” on.

What does an OCP mean ?

Jenkins Plugin for Delphix

In my last blog I talked about trying out Jenkins. In this blog post I want to talk about the new Jenkins plugin for Delphix.

Delphix plugin

Delphix plugin is easy to add. Just navigate to “Manage Jenkins” in the top left

Screen Shot 2016-01-15 at 11.34.17 AM

Then click on “Manage Plugins”

Getting Your Transaction SCN – USERENV(COMMITSCN)

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it so I asked Jonathan and he pointed me straight to this post he did about it a while back. What a nice chap. However, the post is from 1999 (last CENTURY!) so I thought I should just check it out first…

A little known ORDER BY extension

Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy.


SQL> select lpad('*', level, '*' ) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order SIBLINGS by ename
  6  /

ENAME
------------------------------
*KING
**BLAKE
***ALLEN      <==| 
***JAMES      <==|
***MARTIN     <==| ordered within the "BLAKE" branch
***TURNER     <==|
***WARD       <==|
**CLARK
***MILLER
**JONES
***FORD
****SMITH
***SCOTT
****ADAMS

14 rows selected.

SQL> select lpad('*', level, '*' ) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order SIBLINGS by ename DESC
  6  /

ENAME
-----------------------