A quick note about what the Oracle TRUNC function can do:
If you have a table ORDERS with a date field DATE_CREATION and you want to filter the records with a given date, you can use the TRUNC function.

SELECT ID 
FROM ORDERS
WHERE TRUNC(ORDERS.DATE_CREATION) = TO_DATE('27/01/2014', 'DD/MM/YYYY')

This will truncate the time from the DATE_CREATION and leave out only the date.
The full syntax for TRUNC is:

TRUNC ( date, [ format ] )
date is the date to truncate.
format is the unit of measure to apply for truncating. If the format parameter is omitted, the TRUNC function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.
techonthenet.com

Update, 28/feb/2014: Another Oracle goodie – how to find out details about sequences:

select sequence_owner, sequence_name, min_value, max_value, last_number 
from all_sequences 
order by sequence_owner, sequence_name;

https://community.oracle.com/thread/2340939

Continuing the Oracle Cheat Sheet 🙂

1. Create an Oracle user

create user xxx identified by yyy;
grant connect, resource, unlimited tablespace to xxx [identified by yyy];
grant create session to xxx;

2. Extend tablespace

ALTER DATABASE
DATAFILE  'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'
RESIZE 1000M;

link

3. Invoking UNIX commands from SQL*Plus

You can invoke a UNIX command from SQL*Plus by using “HOST COMMAND”:

host ls -la *.sql 

lists all SQL files in the current directory
(more…)

Recently I had to implement a ColdFusion + Oracle application using Google Maps. One of the requirements was search by distance (or radius search) for some items, already geocoded.
The items are stored in T_ITEM, with the columns:
ITEM_ID
COORDX --latitude
COORDY --longitude
The latitude and longitude are already calculated, and stored in database as VARCHAR.
The task was to create some sort of function to accept:
-latitude
-longitude
-radius
and to return the items from T_ITEM located within radius kilometers from the given coordinates.

In implementing this task, I started with (more…)