Sunday, July 21, 2013

SQLJUTL Package Error - Database Adapter Creation - Oracle XE

When we try to create a database adapter to execute procedure or function exists in XE database, following error message will be displayed during adapter configuration.

java.sql.SQLSyntaxErrorException: ORA-00904: "SYS"."SQLJUTL"."HAS_DEFAULT": invalid identifier


Given error happens due to missing of required SQLJUTL package from XE.

To resolve this error, you have to compile SQLJUTL package script in XE database. The SQLJUTL package script can be found in Enterprise Database installation SQLJ directory.

For Ex: /home/oracle/healthcare/product/11.2.0/dbhome_1/oc4j/sqlj/lib


Tuesday, July 9, 2013

Pagination with ROWNUM

Following SQL query will help you to get number of records in between certain range using ROWNUM.

SELECT *
FROM
  (SELECT a.*, ROWNUM rnum
   FROM
     (SELECT *
      FROM employees
      ORDER BY employee_id, rowid) a
   WHERE ROWNUM <= 10
 )
 WHERE rnum >= 8;


You can find more details in following LINK