Category: Programming

How to compute a timestamp in Oracle (PL SQL)

Posted: 2007-10-09   |   Categories: Oracle, Programming

Many sources use the term timestamp to refer specifically to Unix time, the number of seconds since 00:00:00 UTC on January 1, 1970. In Oracle you can compute this number very easily. For example, the following query computes the number of seconds between 00:00:00 UTC on January 1, 1970 and October 9, 2007.

   
SELECT (to_date('09-10-2007', 'DD-MM-YYYY') -
to_date('01-01-1970', 'DD-MM-YYYY')) * 60 * 60 * 24
FROM dual

The result of the preceding query should be 1191888000.

How to create a copy/backup of a table in Oracle

Posted: 2007-07-04   |   Categories: Oracle, Programming

You can create a backup of a table (structure and data) in Oracle by using the following syntax:

 
CREATE TABLE customers_backup AS (SELECT * FROM customers)

The previous example creates the customers_backup table which mirrors the structure and data of the customers table.

How to exclude subversion hidden directories (.svn) using Ant

Posted: 2007-06-04   |   Categories: Java, Programming   |   Tags: Ant, SVN

Many Java developers already know that Ant is a great build tool. Besides a build tool you also need a good version control system to manage the various versions of your code. For this purpose I often use Subversion. Now, there might be time when you need to exclude the Subversion hidden directories from, for example, a backup Ant target you built. To do that you just need to use the following attribute to your zipfileset or other directory-based task:

How to commit a single DML statement in Oracle (PL/SQL)

Posted: 2007-05-24   |   Categories: Oracle, Programming   |   Tags: DML, Statement

Sometimes you might need to commit a single INSERT, UPDATE or DELETE statement and not the whole main transaction. In such a case you can use an AUTONOMOUS TRANSACTION which is a feature available since Oracle 8i. The trick is to encapsulate the statement in a procedure and use the pragma AUTONOMOUS_TRANSACTION. Here is an example, suppose you have a table, your_table, which has just one column of type NUMBER and you want to insert the value 1, irrespective of the state of the main transaction: