Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

January 2008

Detect numbers with TRANSLATE() - Take two

Submitted by jkstill on Thu, 2008-01-31 11:51

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

Submitted by jkstill on Tue, 2008-01-29 08:14

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

Submitted by Adrian Billington on Tue, 2008-01-15 19:45

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

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

Submitted by tanelpoder on Mon, 2008-01-14 05:42

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?

Submitted by tanelpoder on Mon, 2008-01-14 05:42

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?

Submitted by tanelpoder on Mon, 2008-01-14 05:42

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?

Submitted by tanelpoder on Mon, 2008-01-14 05:42

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…

Submitted by tanelpoder on Wed, 2008-01-09 10:53

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…

Submitted by tanelpoder on Wed, 2008-01-09 10:53

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…

Submitted by tanelpoder on Wed, 2008-01-09 10:53

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.