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

A little bit about Fusion Tables

Written on 22 May 2011, 10:49pm

Tagged with: , , ,

Google Fusion Tables is a modern data management and publishing web application that makes it easy to host, manage, collaborate on, visualize, and publish data tables online.

So as far as an user is concerned, a Fusion Table is a virtual table stored in the cloud, on which he can perform the regular CRUD operations. Here comes the nice part: the language on which you can perform these operations is SQL-like. The API is designed so that you can use URLs like*+from+1234567
The output of this request is a CSV text, with the contents of the selected table.

Let’s assume that you have a Fusion table where you store some physical addresses for some items. Below is a very simple PHP script which reads the contents of the fusion table, parses it, extracts the address, then performs a geocoding request using the geocoding API, parses the JSON object and outputs the latitude and longitude of the given address.

//run sql query to get data from fusion table
$url = '*+from+822057';
$lines = file($url);

$i = 0;
foreach ($lines as $line_num => $line) 
	//parse csv line
	$csv = str_getcsv($line);

	//extract the address
	$address	= $csv[7];
		//we need to geocode the address to get the latitude and longitude
		$url_address = urlencode ($address);
		$geocode_url = "$url_address&sensor=false";
		$geocode = file_get_contents($geocode_url);
		$output = json_decode($geocode);

		$latitude = $output->results[0]->geometry->location->lat;
		$longitude = $output->results[0]->geometry->location->lng;

		//sleep for a second

		//do something nice with all this data



6 useful Oracle commands

Written on 26 March 2011, 12:43pm

Tagged with: , ,

This is more of a reference post to store some useful Oracle features. Or a cheat sheet if you wish 🙂

1. How to find the maximum column size in a table


2. How to paginate in Oracle using rownum

Method 1:

select * from 
       select rownum r, a.* from 
       ) a 
       where rownum < = 10
where r >= 1;

Method 2: (more…)