Search Smith

ColdFusion, SQL queries, and, of course, searching

Posts Tagged ‘Oracle 10g’

SHA-256 Hash in Oracle 10g

Posted by David Faber on August 24, 2012

It’s been a while since I’ve posted, and I’m not certain when I will get back to posting regularly (sorry!), but I came across an issue yesterday and found a blog post that was too good not to bring to others’ attention. I was looking to hash a column in Oracle with the SHA-2 256-bit hash function but that is not available in Oracle 10g.

The key to accomplishing this is found in the ability to load Java classes into Oracle and create Oracle functions based on the methods of those classes.

vnull – SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO

Incidentally, according to this post on the Oracle forums, the DBMS_CRYPTO package is certainly included in all editions of Oracle 11g, not just Enterprise Edition. There is nothing in the Oracle licensing information that would suggest that DBMS_CRYPTO is not available on Standard Edition, Standard Edition One, etc.

Having these encryption algorithms available within the Oracle DBMS is certainly helpful as it allows data to be encrypted or hashed via database triggers and the like.

To test your SHA-256 hash results, try the following link: SHA-256 Cryptographic Hash Algorithm.

If SHA-256 is not strong enough for you, SHA-512 is also included in the GNU Crypto project.

Posted in Oracle, SQL | Tagged: , , , , , , , , , , | 2 Comments »

Oracle 10g Pagination

Posted by David Faber on February 28, 2012

A colleague of mine alerted me to the following example of pagination in Oracle 10g:

select * from (
    select /*+ FIRST_ROWS(n) */
        , ROWNUM rnum
        , count(*) over() as total_rows
    from ( 
        /* your select statement goes here */ 
    ) a
) b 
where b.rnum >= :start_row and b.rnum <= :end_row

This does have the advantage of working with an arbitrary SQL SELECT statement, but there is a somewhat simpler way of going about it if you know in advance what the SELECT statement will be:

    SELECT your_columns, ROW_NUMBER() OVER (ORDER BY your_sort) AS rnum
         , COUNT(*) OVER() AS total_rows
      FROM your_table
     ORDER BY your_sort
) WHERE rnum >= :start_row AND rnum <= :end_row

The key is that the value of your_sort must be the same in both places.

Posted in Oracle, SQL | Tagged: , , , , , | 1 Comment »