Search Smith

ColdFusion, SQL queries, and, of course, searching

Archive for the ‘Oracle’ Category

Some statistics

Posted by David Faber on March 8, 2012

At some point yesterday this blog passed 200 unique visitors and 400 page views. And today we had our 300th visit.

I guess that’s what one would call starting small. 🙂

On a side note, I’ve recently posted on:

  1. How to handle Solr query results in ColdFusion;
  2. Returning the number of milliseconds elapsed since midnight in Oracle (useful for seeding a random number generator);
  3. Paginating query results in Oracle 10g; and
  4. Changing the port on which Jetty listens if you want to use a port other than the default (8983).

Oh, and aside from the United States, this blog is most popular in:

  1. India,
  2. Germany,
  3. Canada,
  4. The United Kingdom, and
  5. Italy.

Of course special mention must also be made to Norway! Most unexpected was the single visit from Lagos, Nigeria. Within the U.S., the greatest number of visits are from:

  1. Philadelphia, PA;
  2. Bozeman, MT;
  3. Washington, DC;
  4. Harrisburg, PA; and
  5. Chicago, IL.

Those five actually combine for over 1/3 of my visits from the U.S. and nearly 1/4 of my total visits.

Posted in ColdFusion, Miscellany, Off-Topic, Oracle, Solr | Tagged: , , , | Leave a Comment »

Oracle: Getting milliseconds past midnight

Posted by David Faber on March 8, 2012

Do a search for oracle milliseconds past midnight and chances are you’ll see results for how to retrieve seconds past midnight:

SELECT TO_NUMBER( TO_CHAR( CURRENT_DATE, 'SSSSS' ) )
  FROM dual;

Finding the number of milliseconds that have elapsed since midnight (why would one do this? Well, perhaps to seed a random number generator) is actually fairly simple – use CURRENT_TIMESTAMP in place of CURRENT_DATE/SYSDATE:

SELECT TO_NUMBER( TO_CHAR( CURRENT_TIMESTAMP, 'SSSSSxFF' ) ) * 1000
  FROM dual;

You multiply by 1,000 as the result is returned as SSSSS.sss, where SSSSS is the number of seconds that have elapsed since midnight and sss is the number of milliseconds that have elapsed since the last second.

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

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) */
          a.*
        , 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 * FROM (
    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 »