Code to group each 1000 entry in one IN Cluase

Here's an implmenetaion in Java for http://stackoverflow.com/questions/2401066/oracle-sql-how-to-use-more-than-1000-items-inside-an-in-clause#2401090

public static <T> String createSQLINClause(String colName, List<T> arr) {
        final int MAX_IN_COUNT = 1000;

        if (colName != null && arr != null && arr.size() > 0) {

            String ret = "";

            int to = (int) Math.ceil((double) arr.size() / MAX_IN_COUNT);

            for (int i = 0; i < to; i++) {

                int fromIndex = i * MAX_IN_COUNT;
                int toIndex = (i + 1) * MAX_IN_COUNT;

                List<T> subList = arr.subList(fromIndex, toIndex > arr.size() ? arr.size() : toIndex);
                ret += colName + " IN (" + join(subList, ", ") + " ) ";

                if (i < to - 1)
                    ret += " OR ";

            }
            return " ( " + ret + " ) ";
        } else
            return "";
    }

Difference between CHAR and NCHAR in Oracle

I'd like to share you with some piece of info I found while searching about the difference between CHAR and NCHAR in Oracle and why Oracle DB has two Charsets

This info I got from SO here. and Here's it:

The NVARCHAR2 datatype was introduced by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database (which uses VARCHAR2). The NVARCHAR2 is a Unicode-only datatype.
One reason you want to use NVARHCAR2 might be that your DB uses a non-Unicode character and you still want to be able to store Unicode data for some columns. Another reason might be that you want to use two Unicode character set (AL32UTF8 for data that comes mostly from western Europe, AL16UTF16 for data that comes mostly from Asia for example) because different character sets won't store the same data equally efficiently.
Both columns in your example (Unicode VARCHAR2(10 CHAR) and NVARCHAR2(10)) would be able to store the same data, however the byte storage will be different. Some strings may be stored more efficiently in one or the other.
Note also that some features won't work with NVARCHAR2, see this SO question:
Oracle Text will not work with NVARCHAR2. What else might be unavailable?

Paging in Oracle

To do paging in oracle, the best way is to use:

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

Source: http://stackoverflow.com/questions/241622/paging-with-oracle#241643

The names of the primary key fields must correspond

I've faced some JPA Error (deployment-time error) which is:

The names of the primary key fields or properties in the primary key class [class FooBarPK] and those of the entity bean class [class FooBar] must correspond and their types must be the same. Also, ensure that you have specified id elements for the corresponding attributes in XML and/or an @Id on the corresponding fields or properties of the entity class.

The error appears when deploying some JPA project in Oracle App Server.

I've this entity:

@Entity
@IdClass(FooBarPK.class)
public class FooBar implements Serializable {
    
    private static final long serialVersionUID = 1L;
    

    @Id
    private Long id1;
    @Id
    private Long id2;
    
    //...
}

//And this is the PK class 

public class NewParaPK implements Serializable 
{
    private static final long serialVersionUID = 1L;

    private Long id1;
    private Long id2;
}

I wonder, why this happen...

I found this line:

private static final long serialVersionUID = 1L;

In the PK Class who cases the problem!

Oracle Encoding functions

Salam,

Actually I am not interested in Oracle DB programming, but I use it some times so, I'll list here two handy functions that I use.

To get character ASCII code, use:

select ascii('a') from dual

To get the character from a code page (according to DB local encoding), use:

select chr(176) from dual

BUG: Toplink doesn't correctly interpret named queries when you use setMaxResults() and setFirstResult()

Hi folks,

I found some bug in toplink that is shipped with Oracle App Server 10.1.3.4.0 (actually among many bugs!!)

According to JPA1.0 specs, Query.setMaxResults() and Query.setFirstResult() methods doesn't work with only queries that contains FETCH JOIN.

But unfortantely, when I tried these two methods with a normal INNER JOIN query, I got the query being changed and returned invalid results!!

So, beware of this!!!

Workaround:
One workaround is to make the join by equity joining columns.
ex:

select s from Suppliers s, Products p where s.productId = p.productId