Oracle – How to insert a record if it does not exist in Oracle

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Just do:

String q = 
"insert into TABLE (var1, var2) " +
"SELECT ?, ? FROM dual "+
"WHERE NOT EXISTS(
"   SELECT 1 FROM table
"   WHERE var1 = ? AND var2 = ? )";
pst = con.prepareStatement(q);
pst.setString( 1, str1 );
pst.setString( 2, str2 );
pst.setString( 3, str1 );
pst.setString( 4, str2 );
int status = pst.execute();
if( status > 0 )
    System.out.println("New row inserted");
else
    System.out.println("Row already exists, insert skipped");

--- EDIT ---

INSERT INTO .... SELECT .... syntax is suported on most DBMS, see this link for more details: http://www.w3schools.com/sql/sql_insert_into_select.asp
It simply selects rows from one table and inserts a result into another table.

dual is a "dummy" table on Oracle databases, it has one column and one row, here is an example: http://www.sqlfiddle.com/#!4/d41d8/11
Dual table is helpful in many cases where we need to create a row with some values. In other databases - esspecially in MySql and PostGreSQL - this can be done with simple SELECT x, y, unfortunately Oracle doesn't support this syntax, and we must use SELECT x, y FROM dual.

In our case it is used to create a row of values that we want to insert into the table:

INSERT INTO table
SELECT x, y FROM dual;

However, the row must be inserted only when some condition is meet - this condition is described in the WHERE clause:

INSERT INTO table
SELECT x, y FROM dual
WHERE NOT EXISTS( 
   SELECT 1 FROM table
   WHERE var1 = X and var2 = Y
)

if the subquery SELECT 1 FROM ... returns a row (a row exists) - then the condition NOT EXISTS ... is false, and SELECT x, y returns nothing (empty resultset), and nothing is inserted into the table. On the other hand, when the subquery doesn't find anything, the whole condition is true, and the SELECT x,y statement creates a row with two columns: X+Y, and this row is inserted into the table.

In the PreparedStatement (in java code) we are using ? instead of X,Y variables - ? is a placeholder (a substitution variable). This link: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html describes in details how PreparedStatement works and how to use substitution variables.

Saying it simply - our query has 4 placeholders ?:

 insert into TABLE (var1, var2)  
 SELECT ?, ? FROM dual  
 WHERE NOT EXISTS(
    SELECT 1 FROM table
    WHERE var1 = ? AND var2 = ? ) ;

and setXXX statements binds values to consecutive placeholders:

pst.setString( 1, str1 );
pst.setString( 2, str2 );
pst.setString( 3, str1 );
pst.setString( 4, str2 );

thus after binding values to substitution marks, the query (which is finally executed) looks like:

 INSERT INTO TABLE (var1, var2)  
 SELECT 'str1', 'str2' FROM dual  
 WHERE NOT EXISTS(
    SELECT 1 FROM table
    WHERE var1 = 'str1' AND var2 = 'str2' ) ;