Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Uncategorized

Why PLSQL ?

With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data

Technology debates

As always happens from time to time, we had the following request on AskTom today:

Could you list down 2 reasons why sql server is better than oracle?

Then 2 counter reasons as to why why oracle is better than sql server

 

And I thought I’d reproduce my response here

 

I’ll answer it slightly differently…

Q: When is SQL Server better then Oracle ?
A: When you have good, intelligent, knowledgeable SQL Server people on your workforce.

Q: When is Oracle better then SQL Server ?
A: When you have good, intelligent, knowledgeable Oracle people on your workforce.

Understanding scheduler syntax

When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run.

AskTom – the personal touch

AskTom has been answering questions from the user community for nearly 16 years. 

But what if that service could have an even more personal touch ?  We are pleased announce the largest advance in AskTom service since it started way back in 2000.

See all the details in the video below for the new service we are launching on April 1st.

Dealing with URL’s

If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier

Opatch quick tip

I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error:

Median in SQL

Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial Smile query can determine the median salary from the EMP table


SQL> select avg(sal) from
  2  ( select x.sal
  3   from scott.EMP x, scott.EMP y
  4   group by x.sal
  5   having sum(sign(1-sign(y.sal-x.sal))) in
  6          ( trunc((count(*)+1)/2), trunc((count(*)+2)/2) ) );

  AVG(SAL)
----------
      1550

Of course, if that is basically gobbledygook to you (and it certainly is to me!)…well, you should check out Oracle’s cool Analytic SQL features here

Are your SQL Plus scripts going to ‘ell ?

Excuse the hideous pun in the title.  All will become clear shortly.

Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database.

Here’s a simple script I want to run:

image

and here’s my very sophisticated Smile batch file (which could just as easily be a Unix shell script) to run it:

Up to date text indexes

If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT.  For example,

India bound !

As part of the activities organised by the Oracle Ace Program and the All India Oracle User Group, the OTN Yathra tour is coming up in a few weeks.

OTNYathra2016

I’ll be speaking on 12c developer features and advanced SQL techniques at the following locations: