Jasper Reports is unable to call Oracle stored procedures directly, because procedures do not return standard result sets. As a solution, in Oracle, you can use a stored function to retrieve the results of a stored procedure. There are a few more steps to do this than if you were able to use a stored procedure, but it currently is the only option, if the query you need to do can’t be done with a standard SQL query.
Read the rest of this entry >>

I have six tables that all use one auto incrementing sequence in Oracle. Problem is, every time the database is redeployed, the next value in the sequence is incorrect because it has to be set statically. So I found this sql on the internet which will update your sequence number according to the next unused ID from your table. I modified the sql because my sequence spanned more than one table. So the modified sql uses a temp table to find the next unused id across multiple tables.

It runs only in sqlplus (due to the use of the variables). I run it with this command:
sqlplus user/pass@/path/scriptname

My modified sql.

Original sql.

UPDATE: Thanks to Dan Wilson, Business Analyst where I am currently under contract, for giving me some sql to handle this much better than the sql I had previously found.

SELECT 'DROP SEQUENCE REPORT_LOG_SEQ ; CREATE SEQUENCE REPORT_LOG_SEQ INCREMENT BY 1 START WITH ' || NVL((SELECT TO_CHAR(MAX(LOG_ID)+1) FROM REPORT_LOG),20000) || ' MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER ;' FROM DUAL

This code drops the sequence and then recreates it using the highest id currrently in use in a table, plus one. The only caution about this method, is that, if like my situation, you are using a sequence for multiple tables, then you have to use the table with the highest id in it. So if you are in that situation and you need to automate this process, and you won’t know which table has the highest id, then you may still have to use the previous method I used above. Otherwise, this is much simpler.