Oracle trunc

Written on 27 January 2014, 04:40pm

Tagged with: , , ,

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.


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.

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;

Another 6 Oracle goodies

Written on 25 July 2011, 12:16pm

Tagged with: ,

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



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

Google Maps: radius search in Oracle

Written on 21 April 2011, 12:11pm

Tagged with: , ,

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:
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:
and to return the items from T_ITEM located within radius kilometers from the given coordinates.

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