Posts Tagged “oracle”

While doing some testing of Oracle on a Windows 7 computer I stopped the TNS Listener service also known as OracleOraDb11g_home1TNSListener. The problem is when I attempted to restart the service it would not start. While testing various other things I had made numerous changes so I started looking into what might cause the issue and was pretty confident that the $ORACLE_HOME variable I set within the Windows Environment Variables was probably the culprit. Below I describe more details about the error as well as how I resolved the TNS Listener not being able to start.

Read the rest of this entry »

Tags: , , , , , , , , , , , , ,

Comments 2 Comments »

Again I am fairly new to Oracle so it has been quite a learning process and typically I have been running into errors that are easy to resolve but being able to locate the answers easy has been challenging. While attempting to use sqlplus.exe from the Windows 7 command line I continued to receive a TNS protocol adapter error and it turns out it was simply from using the incorrect syntax. Below I describe the error in more detail as well as the correct syntax to use for sqlplus on Windows 7.

Read the rest of this entry »

Tags: , , , , , , , , , , , , ,

Comments 9 Comments »

I recently installed Oracle on a Windows 7 computer to test some tools that are available in Backtrack. I am very new to Oracle so it has been a bit of a learning process including the fact that I had no idea you can install Oracle without having to pay any money to download it so I also plan on writing an article on how to do this. Anyhow after the initial Oracle install we were testing oscanner from Backtrack and locked all of the Oracle accounts which was a bummer because I wasn’t sure there was a way to correct the issue. Turns out it is really easy to unlock the Oracle accounts if you have access to the Oracle server so below I describe how to unlock Oracle accounts when it appears they have all been locked.

Read the rest of this entry »

Tags: , , , , , , , , , , , , , , , ,

Comments No Comments »

Troel has written a great summary of the SQL standard for the timestamp data type along with its implementation in the big 5 databases (MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and DB2).


It turns out that MySQL does not support sub-second time or date formats. There is a MICROSECOND function in MySQL allowed to run on temporal values, but sub-second precision (including millisecond) is not stored in the database.

A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions. Microseconds are specified as a trailing .uuuuuu part in the value. …. However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

There is also a May 26, 2007 post on Kevin Burton’s blog in which a MySQL developer appears to confirm that MySQL will not store milliseconds (which obviously means microseconds can’t be stored either). It’s odd considering that MySQL’s DATETIME data type is 8 bytes and milliseconds can’t be stored. Interestingly, PostgreSQL’s timestamp datatype is also 8 bytes and can support microsecond resolution.

Here’s the mapping of the Rails data types (the symbols used in Rails migrations) to MySQL data types. This mapping can be found in mysql_adapter.rb in Rail’s ActiveRecord:

def native_database_types #:nodoc: { :primary_key => “int(11) DEFAULT NULL auto_increment PRIMARY KEY”, :string => { :name => “varchar”, :limit => 255 }, :text => { :name => “text” }, :integer => { :name => “int”, :limit => 11 }, :float => { :name => “float” }, :decimal => { :name => “decimal” }, :datetime => { :name => “datetime” }, :timestamp => { :name => “datetime” }, :time => { :name => “time” }, :date => { :name => “date” }, :binary => { :name => “blob” }, :boolean => { :name => “tinyint”, :limit => 1 } } end

A potential solution to the MySQL sub-second limitation is to use force Rails to use the bigint type in MySQL (along with creating your own custom timestamp format to fit in the bigint type). This is an alternative to modifying mysql_adapter.rb in Rail’s ActiveRecord, which could pose problems later on. There is a plug-in MySQL Bigint Rails Plugin that supports multiple integer types. Not only is bigint supported, it also allows tinyint and mediumint. After installing, it magically transforms the :limit parameter so that it specifies the number of bytes for the column stored instead of specifying the maximum value expected for that column. For example, the following statement will be mapped to MySQL’s mediumint instead of a regular int.

t.column “col2″, :integer, :limit => 3, :null=> false

Run 'DESCRIBE table;' in the MySQL command line to verify that it worked.


PostgreSQL, on the other hand, has 1 microsecond resolution on all of its time and timestamp types. Here’s the mapping in of Rails data types to PostgreSQL data types (line 92 of postgresql_adapter.rb in Rail’s ActiveRecord)

def native_database_types { :primary_key => “serial primary key”, :string => { :name => “character varying”, :limit => 255 }, :text => { :name => “text” }, :integer => { :name => “integer” }, :float => { :name => “float” }, :decimal => { :name => “decimal” }, :datetime => { :name => “timestamp” }, :timestamp => { :name => “timestamp” }, :time => { :name => “time” }, :date => { :name => “date” }, :binary => { :name => “bytea” }, :boolean => { :name => “boolean” } } end

In PostgreSQL, the timestamp data type is referred to as the "timestamp without time zone“. It will use this by default, unless you explicitly specify the timestamp with time zone. In either case, the timestamp is accepted and queried in string format: (e.g. '2003-07-29 13:19:30.532').

Also, PostgreSQL’s timestamp data type accepts a parameter p indicating the the number of fractional digits retained in the seconds field. To my understanding, p does not have any influence on the total bytes stored, which is always 8. Internally, it’s an 8-byte int or an 8-byte float depending on whether or not you compiled with enable-integer-datetimes. The default is 8-byte float.

If server was compiled with enable-integer-datetimes, PostgreSQL stores the timestamp as the total number of microseconds from midnight 2000-01-01 (in an int8). If not, it’s stored (in a float8) and the precision degrades as the date moves away from midnight 2000-01-01.

To make it all work, I added a :timestamp_with_time_zone data type by overriding PostgreSQL adapter class found in postgresql_adapter.rb

To override this class, include the preceding in a file such as lib\postgre_extensions.rb and add “require ‘postgre_extensions’” to the top of each migration file. Or you can add require ‘postgre_extensions’ to the bottom of config\environment.rb

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter def native_database_types { :primary_key => “serial primary key”, :string => { :name => “character varying”, :limit => 255 }, :text => { :name => “text” }, :integer => { :name => “integer” }, :float => { :name => “float” }, :decimal => { :name => “decimal” }, :datetime => { :name => “timestamp” }, :timestamp => { :name => “timestamp” }, :timestamp_with_time_zone => { :name => “timestamp with time zone” }, :time => { :name => “time” }, :date => { :name => “date” }, :binary => { :name => “bytea” }, :boolean => { :name => “boolean” } } end end

While I was at it, I tried to create a smallint type in PostgreSQL with the following line of Migrations code and the MySQL Bigint Rails Plugin (described above):

t.column :heartrate, :integer, :limit => 1, :null=> false

The author claims that it should work in other databases outside of MySQL and it did for this example! In this particular case, a smallint (2 bytes) was used instead of an integer (4 bytes). The tinyint data type (1 byte) is not available in PostgreSQL like it is in MySQL unfortunately.


Oracle, too, appears to supports millisecond precision, but the Rails mapping goes to DATE instead of TIMESTAMP as Paul Greer pointed out in ActiveRecord’s oracle.rb:

def native_database_types #:nodoc: { :primary_key => “NUMBER(38) NOT NULL PRIMARY KEY”, :string => { :name => “VARCHAR2″, :limit => 255 }, :text => { :name => “CLOB” }, :integer => { :name => “NUMBER”, :limit => 38 }, :float => { :name => “NUMBER” }, :decimal => { :name => “DECIMAL” }, :datetime => { :name => “DATE” }, :timestamp => { :name => “DATE” }, :time => { :name => “DATE” }, :date => { :name => “DATE” }, :binary => { :name => “BLOB” }, :boolean => { :name => “NUMBER”, :limit => 1 } } end

Timestamp SQL queries

Use single quotes '' for timestamp! He are are some examples

  1. SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';
  2. SELECT AVG(value) FROM numerics WHERE timestamp BETWEEN '2007-08-23 19:48:09-04' AND '2007-08-23 19:48:09-04';
  3. SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04'::timestamp;
  4. EXPLAIN SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';
    Traditional practice for EXPLAIN is to measure the costs in units of disk page fetches;

P. S. It’s a bad idea to title a column ‘timestamp’, because this is a datatype-identifier – but it works.

Useful Rails timestamp-related code

# First and last records from a model ordered by a specified column (e.g. timestamp)
ending = Users.find(:first, :order => 'timestamp DESC')
beginning = Users.find(:first, :order => 'timestamp ASC')

#resolve Ruby variable name (e.g. start_time) in string using the #{...} string interpolation mechanism
average = User.average(:age, :conditions => "timestamp > #{start_time} AND timestamp < #{end_time}")

#converts string to time object

Tags: , , , , , , , , , , ,

Comments No Comments »