Search

Top 60 Oracle Blogs

Recent comments

January 2008

Detect numbers with TRANSLATE() - Take two

Last week I wrote about using TRANSLATE to detect numbers in data Using The TRANSLATE() function...

Andrew Clarke at Radio Free Tooting pointed out the shortcomings of using TRANSLATE() to detect numbers.

As I said earlier, all I needed to do was detect if the characters in a string were all digits or not, and I wanted it to be very fast.

But Andrew's remarks got me thinking - could translate be used to detect more complex numbers?

Here's the short list of requirements:

* Detect integers
* Detect numbers with decimal point ( 4.6, 0.2, .7)
* Detect negative and positive ( leading + or - )
* Reject text with more than 1 '.', such as an IP address ( 127.0.0.1 )
* Reject anything with alpha text

RMAN Usage Survey

As part of a presentation I'm preparing, I would like to get an idea of RMAN usage in the Oracle Community.

If you can spare a couple of minutes, please fill out this 10 question multiple choice survey: RMAN Usage Survey

Update:

It seems I made a poor choice of online Survey site.

To get more than 100 responses to this survey, a "Professional" version must be purchased. At a rate of 1 or 2 surveys a year (for me) that is not exactly practical.

Sharing the Survey results also requires the Professional version.

Here's a summary of the results in MS Excel.

RMAN Usage Survey Results

Exception enhancements in 10g

A few small enhancements to exception handling/error messages. June 2004

Can you write a working SQL statement without using any whitespace?

I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.

The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:

SQL> select 1.x from dual;

         X
----------
         1

The column header gives a good hint what happened above. Oracle has treated the X as the column alias.

Let’s remove the “x” and see:

SQL> select 1. from dual;

        1.
----------
         1

Now all works as expected, “1.” is treated as number ( 1. = 1.0 )

Can you write a working SQL statement without using any whitespace?

I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.

The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:

SQL> select 1.x from dual;

         X
----------
         1

The column header gives a good hint what happened above. Oracle has treated the X as the column alias.

Let’s remove the “x” and see:

SQL> select 1. from dual;

        1.
----------
         1

Now all works as expected, “1.” is treated as number ( 1. = 1.0 )

Expensive calculator…

Oracle has evolved over time to much more than just a plain relational database. One option is to use Oracle as an expensive calculator.
When researching or demoing Oracle, it’s quite convenient to do number calculations directly on sqlplus prompt, especially if dealing with internals where lots of stuff is about addresses and offsets shown in hex.
Here’s the script what I use for such purposes: https://github.com/tanelpoder/tpt-oracle/blob/master/calc.sql.
It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.

Expensive calculator…

Oracle has evolved over time to much more than just a plain relational database. One option is to use Oracle as an expensive calculator.
When researching or demoing Oracle, it’s quite convenient to do number calculations directly on sqlplus prompt, especially if dealing with internals where lots of stuff is about addresses and offsets shown in hex.
Here’s the script what I use for such purposes: https://github.com/tanelpoder/tpt-oracle/blob/master/calc.sql.
It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.

Why does Oracle parameter count change during session lifetime?

I was once asked a question, why does Oracle change its parameter count during session lifetime?

The question arose from the following observation that v$parameter shows more parameters after you adjust some hidden parameter value:

SQL>
SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       288

SQL>
SQL> alter session set "_complex_view_merging"=false;

Session altered.

SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       289

Looks like the parameter count was just increased by one!

“It sure seems like the hidden parameter don’t exist before they are actually modified”:

Why does Oracle parameter count change during session lifetime?

I was once asked a question, why does Oracle change its parameter count during session lifetime?

The question arose from the following observation that v$parameter shows more parameters after you adjust some hidden parameter value:

SQL>
SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       288

SQL>
SQL> alter session set "_complex_view_merging"=false;

Session altered.

SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       289

Looks like the parameter count was just increased by one!

“It sure seems like the hidden parameter don’t exist before they are actually modified”:

Systematic application troubleshooting in Unix

How many times have you seen a following case, where a user or developer complains that their Oracle session is stuck or running very slowly and the person who starts investigating the issue does following:

  1. Checks the database for locks
  2. Checks free disk space
  3. Checks alert log
  4. Goes back to the client saying “we did a healthcheck and everything looks ok” and closes the case or asks the user/developer to contact application support team or tune their SQL

The point here is that what the heck do the database locks, alert log or disk space have to do with first round session troubleshooting, when Oracle provides just about everything you need in one simple view?