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: http://www.tanelpoder.com/files/scripts/calc.sql.

It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.exe on my Windows box (I immediately stopped using it after I wrote the script).

But the main benefit is that your calculation outputs remain in the sqlplus output history, so you don’t have to redo them again and you’ll have somewhat better documentation of the output of research steps you’ve done.

Here’s a quick demo:

Add a decimal value to decimal value:

SQL> @calc 10 + 10

                 DEC                  HEX
-------------------- --------------------
                  20                   14

Add a hex value to a decimal:

SQL> @calc 10 + 0x10

                 DEC                  HEX
-------------------- --------------------
                  26                   1A

Divide a hex value with a decimal one:

SQL> @calc 0xFFFF / 64

                 DEC                  HEX
-------------------- --------------------
                1024                  400

Show a value in both hex and dec:

SQL> @calc 65536 + 0

                 DEC                  HEX
-------------------- --------------------
               65536                10000

Note that you do not need to use “0x” for indicating a hex value, instead just an “x” will do:

SQL> @calc xB0B * x142A2D

                 DEC                  HEX
-------------------- --------------------
          3735928559             DEADBEEF

So, you can use Oracle as a very expensive calculator – but on the other hand it can save some of your time…

NB! If you want to move to the "New World" - and benefit from the awesomeness of Hadoop, without having to re-engineer your existing applications - check out Gluent, my new startup that will make history! ;-)

This entry was posted in Cool stuff and tagged . Bookmark the permalink.

5 Responses to Expensive calculator…

  1. LOL

    Cool to see that I am not the only one who uses Oracle this way. Until now I though that I was the only lazy guy around doing stuff like this via his SQL*Plus terminal.


  2. tanelp says:

    I used to type in “select num1 + num2 …” statements, but eventually this got too time-consuming when hex values came into play etc.

    Note that my script doesn’t handle fractional decimal numbers, by DEC I’ve meant decimal in base 10 notation context…

  3. Brian Tkatch says:

    IMO, calc is faster when shortcutted. I have it set to open on which is less typing and fast to come up them using SQL*Plus. Though, the history, and scriptability is quite nice.

    If i were on a Un*x box, i would think the script would be a simpler $bc…

  4. The PerlDBA says:

    Here’s the perl script I use for calculations:

    while () {
    print (eval $_);
    print “\n”;
    die $@ if $@;

    Type in any legal formula ( or any perl statement for that matter) and get the results.

    eg. How many years is 1,000,000,000 seconds?

    1*10**9 / 60 / 60 / 24 / 365

    31.7 years.

  5. tanelp says:

    Very nice, perlDBA :)

    I think I’ll start using it!

Leave a Reply

Your email address will not be published. Required fields are marked *