Pages Navigation Menu

Coding is much easier than you think

How to create a AUTO_INCREMENT column in Oracle?

Posted by on Sep 13, 2013 in Oracle | 0 comments

 
A lot of databases have a column autoincrement attribute or an autoincrement data type. They are used to create unique identifiers for a column. However in Oracle, there is no such thing such as “auto_increment” columns. But we can implement this feature with a Sequence and a Trigger:
 
** UPDATE: Complete JDBC tutorial now available here.
 
Table definition :
 

CREATE TABLE EMPLOYEE(
USER_ID NUMBER(5) NOT NULL , 
USERNAME VARCHAR(20) NOT NULL, 
CREATED_BY VARCHAR(20) NOT NULL);

 

ALTER TABLE EMPLOYEE ADD (
  CONSTRAINT userid_pk PRIMARY KEY (USER_ID ));

 
Sequence definition :
 

CREATE SEQUENCE userid_seq;

 
Trigger definition :
 

CREATE OR REPLACE TRIGGER userid_bir 
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW

BEGIN
  SELECT userid_seq.NEXTVAL
  INTO   :new.USER_ID 
  FROM   dual;
END;
/

 

Read More

Connect to Oracle DB via JDBC driver

Posted by on Aug 17, 2013 in JDBC Tutorials | 0 comments

 
Here is an example to show you how to connect to Oracle database via JDBC driver.
 
 1. Download Oracle JDBC Driver
 
Get Oracle JDBC driver here – ojdbcxxx.jar
 
2. Java JDBC connection example
 
Code snippets to use JDBC to connect a Oracle database.
 
** UPDATE: Complete JDBC tutorial now available here.
 

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = null;
connection = DriverManager.getConnection(
	"jdbc:oracle:thin:@hostname:port:dbname","username","password");
connection.close();

 
See a complete example below:
File: OracleJDBC.java
 

package com.simplecode.jdbc;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class OracleJDBC {
	
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION="jdbc:oracle:thin:@localhost:1521:SIMPLECODEDB";
private static final String DB_USER = "username";
private static final String DB_PASSWORD = "password";

public static void main(String[] argc) {

	System.out.println("**** Oracle JDBC Driver Connection Establishing ****");

	try 
	{
	Class.forName(DB_DRIVER);
	} 
        catch (ClassNotFoundException e) 
	{
		System.out.println("Exception : Add Oracle JDBC Driver in your classpath ");
		System.err.println(e.getMessage());
		return;
	}

	System.out.println("*Oracle JDBC Driver Registered!");
	Connection connection = null;

	try 
	{
	connection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
	}
	catch (SQLException e) {

		System.out.println("Connection Failed!);
		System.err.println(e.getMessage());
		return;
	}

	if (connection == null) {
		System.out.println("Connection Failed!");
	} 
	else 
	{
		System.out.println("Connection established!");

	}
}
}

 

Read More

How to Manage NULLs when Sorting Data

Posted by on Jan 17, 2013 in Oracle | 0 comments

 
How to Manage NULLs when Sorting Data
 
Consider a scenario such that you want to sort data in descending order, for example Given a table student (gateId, name, score), now to arrange the students by their GATE scores the following query can be used.

Select * from Student 'ORDER BY score DESC'

 
Now consider a scenario such that some students who did not take the GATE at all, now their scores in the table are not zero, but they are null. In this case Oracle’s ORDER BY..DESC will give a surprise result.

Here’s how the result will look with the ORDER BY…DESC clause:
 

SQL> select * from student order by score desc;
 
    GATEID NAME                 SCORE
---------- --------------- ----------
         9 Blellik
         3 Michal
         7 Sara                   910
         4 Ibrahim                840
         1 Caledon                730
         6 Fabrizio               710
         2 Jaya Prakash           640
         5 Nilafar                580
         8 Lourde                 550

 
In this scenario actually Sara need to be ranked 1st, but the nulls push that student rank to 3rd as ORDER BY..DESC in Oracle places null values right at the top of the query results.
 


Likewise, ORDER BY (ascending order) places null values at the end of the query results.

SQL> select * from student order by score;
 
    GATEID NAME                 SCORE
---------- --------------- ----------
         8 Lourde                 550
         5 Nilafar                580
         2 Jaya Prakash           640
         6 Fabrizio               710
         1 Caledon                730
         4 Ibrahim                840
         7 Sara                   910
         3 Michal
         9 Blellik

 

Solution
 
Starting with Oracle 8i, there is a little known syntax available in the ORDER BY clause that resolve this issue. All you have to do is change the last line above to the following:

 

SQL> select * from student order by score desc nulls last;
 
    GATEID NAME                 SCORE
---------- --------------- ----------
         7 Sara                   910
         4 Ibrahim                840
         1 Caledon                730
         6 Fabrizio               710
         2 Jaya Prakash           640
         5 Nilafar                580
         8 Lourde                 550
         9 Blellik
         3 Michal

 
You can also use NULLS FIRST when you’re sorting in ascending order, and you want the NULL rows to appear at the top of the report.

 


SQL> select * from student order by score nulls first;
 
   GATEID NAME                 SCORE
---------- --------------- ----------
         3 Michal
         9 Blellik
         8 Lourde                 550
         5 Nilafar                580
         2 Jaya Prakash           640
         6 Fabrizio               710
         1 Caledon                730
         4 Ibrahim                840
         7 Sara                   910

 
If you’re still supporting Oracle 8.0 or 7.3 databases, you can achieve the same effect using the Null Values function (NVL).
 

ORDER BY NVL(score, -1);

 
This forces the NULL rows to be sorted as if they had the value (-1) in them, and they will appear at the bottom of the output.
 
Conclusion
 
In Oracle database, the default sort order in an ascending sort places null values at the bottom of the result list, and in a descending sort at the start of the result list. This is because, NULL is treated as a very large value by Oracle. To override this default behavior of ORDER BY, use the NULLS FIRST/LAST clause.
 

Read More