Alessandro Lacava

on Designing and Developing Software. In love with Functional Programming.

How to Compute a Timestamp In Oracle (PL SQL)

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.

1
2
3
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 it works:

The subtraction between two dates returns the number of days between them. Multiplying this number by 60 * 60 * 24–the number of seconds in one day–you obtain the number of seconds between those dates.

Very Useful Shortcuts for Eclipse

Who uses eclipse already knows how fantastic this great IDE is. Here I want to provide a table with the shortcuts I use almost ever when I develop in Java.

Action Shortcut
Toggle Comment Ctrl+7
Show Tooltip F2
Format Code Ctrl+Shift+F
Indent Line Ctrl+I
Duplicate Lines Down Ctrl+Alt+Down Arrow
Duplicate Lines Up Ctrl+Alt+Up Arrow
Delete Next Line And Move Below Code Up Ctrl+D
Delete Next Word Ctrl+Delete
Delete Previous Word Ctrl+Backspace
Delete Line Ctrl+Shift+Delete
Insert Line Above The Current One Ctrl+Shift+Enter
Insert Line Below The Current One Shift+Enter
Move Lines Down Alt+Down Arrow
Move Lines Up Alt+Up Arrow
Next Word Ctrl+Right Arrow
Previous Word Ctrl+Left Arrow
To Upper Case Ctrl+Shift+X
To Lower Case Ctrl+Shift+Y
Shortcut for System.out.println Type syso and then Ctrl+Space Bar

How to Attach Source or Javadoc to Java Enterprise Edition API In Eclipse

Eclipse is a great IDE. When you hover your mouse over a class name or method of the Java SE API you get a contextual help for that class or method. However if you do the same thing over a Java EE class, such as HttpServletRequest you might not get the same effect. This is because there’s is no source or Javadoc attached to that class. To attach the documentation to your J2EE class as well you can follow these steps:

  1. Right click on servlet-api.jar in your project.

  2. Choose Properties from the menu.

  3. In Javadoc location path put the URL where the Java EE documentation is located (e.g. http://java.sun.com/j2ee/1.4/docs/api/) or, alternatively, indicate the path where the Javadoc is located in your disk, such as file:/c:/path/to/your/documentation.

Hope this helps!

How to Create a Copy/backup of a Table In Oracle

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

1
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

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:

1
<zipfileset dir="${basedir}" excludes="**/.svn/*" prefix="${ant.project.name}" />

In particular, the previous code excludes all the .svn directories, that are under the basedir directory and its subdirectories, recursively.

How to Commit a Single DML Statement In Oracle (PL/SQL)

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:

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE PROCEDURE p_insert
AS
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN -- Main transaction suspends here.

INSERT INTO your_table VALUES (1); -- Autonomous transaction begins here.
COMMIT; -- Autonomous transaction ends here.

END; -- Main transaction resumes here.

After calling this proc from another transaction (main transaction) your_table will have another row with 1 as the value of its only column. If, after calling p_insert, you call ROLLBACK in your main transaction you will have that the changes made within p_insert will be permanent. However the changes made in your main transaction will be rollbacked.

A tipical scenario where you need an autonomous transaction is a table used to log errors and/or other info. For example if something goes wrong you might want to log the reason into a table before rollbacking the main transaction. In such a case you would write a procedure using the pragma AUTONOMOUS_TRANSACTION and call this proc before rollbacking.

Useful Windows Shortcuts

Try these nice shortcuts and you’ll find yourself using them on and on.

  1. Windows Key + d shows the desktop

  2. Windows Key + e opens the explorer on My Computer

  3. Windows Key + f opens the “Find” window

  4. Windows Key + l logs out the current user

  5. Windows Key + m minimizes all windows

  6. Windows Key + r opens the “Run” window

  7. Windows Key + u opens the “Utility Manager” window

How to Format a Decimal Number In JavaScript

In JavaScript you can use a built-in method to format a decimal number so that it is displayed using, at most, x decimal places. Here is an example:

1
2
3
var num = 3.1356;
var x = 3;
alert(num.toFixed(x)); // displays 3.136

As you may have guessed the method is toFixed. It accepts one parameter which is the number of decimal places to take into account.

Note: The number gets rounded as you can notice by the example above.

How to Put a Set of Rows Into a Single Row In Oracle

In Oracle you can execute hierarchical queries using some cool operators–CONNECT BY PRIOR, START WITH and the pseudocolumn SYS_CONNECT_BY_PATH, namely. However, using your fantasy you can exploit these operators to put in a single row–with each item separated by the next through a separator, such as a comma–what you have in multiple rows. For example if you have more than one telephone number in different rows and you want to put them in a single comma-separated row you could use a query similar to the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT ltrim(sys_connect_by_path(telephone, ', '), ', ') tel
FROM (
  SELECT telephone, rownum num_of_rows
  FROM (-- Select the first 9 rows    (1)
    SELECT ds_number telephone
    FROM telephones t
    WHERE cd_customer = 50
    AND rownum <= 9)) x
  WHERE num_of_rows = (-- Count the # of rows selected in (1)
    SELECT COUNT(*)
    FROM telephones t
    WHERE cd_customer = 50
    AND rownum <= 9)
START WITH num_of_rows = 1
CONNECT BY PRIOR x.num_of_rows = (x.num_of_rows - 1);

The previuos query selects all the telephone numbers for the customer with cd_customer = 50 and put them in a single comma-separated row.

How to Tell Oracle to Use More Than One Byte Per Character

When you create a table, by default, the number of bytes used to represent a character in a VARCHAR2 type is 1, irrespective of the characterset of the DB. This may be fine if the characterset is such that it uses just one byte per character. However, if the characterset of the DB is such that there might take more than one byte to represent a character (for example for characterset = AL32UTF8) you might run into troubles, such as the error: ORA-01401: inserted value too large for column. However you can specify, at creation time, that the size of characters is expressed in CHARs instead of bytes. For example:

1
CREATE TABLE test (col_test VARCHAR2(10 CHAR))

The previous code creates a table with just one column. The column will be able to contain 10 characters irrespective of the number of bytes used to represent the characters internally.