JForum and Oracle 8i Standard Edition

[Migrated From Jforum.net]

Hi,
I�m trying to make JForum 2.1.8 work with Oracle 8i Standard Edition, but when I try to start the application the following error appears.

11:53:57,564 INFO [JForumBaseServlet ] Starting JForum. Debug mode is true
11:53:57,579 INFO [ConfigLoader ] Using cache engine: net.jforum.cache.DefaultCacheEngine
11:53:57,579 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.BBCodeRepository
11:53:57,579 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.RankingRepository
11:53:57,579 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.SmiliesRepository
11:53:57,595 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.ForumRepository
11:53:57,595 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.TopicRepository
11:53:57,595 INFO [ConfigLoader ] Creating an instance of net.jforum.SessionFacade
11:53:57,611 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.PostRepository
11:53:57,611 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.Tpl
11:53:57,611 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.RolesRepository
11:53:57,611 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.SecurityRepository
11:53:57,611 INFO [ConfigLoader ] Creating an instance of net.jforum.repository.BanlistRepository
11:53:57,751 INFO [FileMonitor ] Watching C:\Documents and Settings\nmedina\workspace2\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\JForum/WEB-INF/config/languages/en_US.properties
11:53:57,767 INFO [ConfigLoader ] Loading JDBC driver net.jforum.dao.oracle.OracleDataAccessDriver
11:53:57,908 INFO [FileMonitor ] Watching C:\Documents and Settings\nmedina\workspace2\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\JForum/WEB-INF/config/database/generic/generic_queries.sql
11:53:57,908 INFO [FileMonitor ] Watching C:\Documents and Settings\nmedina\workspace2\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\JForum/WEB-INF/config/database/oracle/oracle.sql
11:53:57,908 INFO [FileMonitor ] Watching C:\Documents and Settings\nmedina\workspace2\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\JForum/WEB-INF/config/SystemGlobals.properties
11:53:58,142 INFO [MLog ] MLog clients using log4j logging.
11:53:58,205 INFO [C3P0Registry ] Initializing c3p0-0.9.1-pre9 [built 23-September-2006 01:47:24; debug? true; trace: 10]
11:53:58,298 INFO [AbstractPoolBackedDataSource] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 120000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> a45536, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> oracle.jdbc.OracleDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> a45536, idleConnectionTestPeriod -> 3600, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:RTD_Forum/forum@172.18.128.191:1521:canada, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 50, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 180, usesTraditionalReflectiveProxies -> false ]
11:54:13,848 ERROR [ForumStartup ] Unable to bootstrap JForum repository.
net.jforum.exceptions.DatabaseException: java.sql.SQLException: ORA-00439: feature not enabled: OLAP Window Functions

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at net.jforum.dao.generic.GenericUserDAO.getLastUserInfo(GenericUserDAO.java:607)
at net.jforum.repository.ForumRepository.loadUsersInfo(ForumRepository.java:788)
at net.jforum.repository.ForumRepository.start(ForumRepository.java:131)
at net.jforum.ForumStartup.startForumRepository(ForumStartup.java:100)
at net.jforum.JForum.init(JForum.java:111)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1161)
at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:806)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:129)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: ORA-00439: feature not enabled: OLAP Window Functions

... 27 more
net.jforum.exceptions.RepositoryStartupException: Error while trying to start ForumRepository: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ORA-00439: feature not enabled: OLAP Window Functions

I know that the Oracle OLAP Functions can be enabled only from versions 8i Enterprise Edition to 11g.
So, is there a way of disabling OLAP functions in JForum, so it can work with Oracle 8i Standard Edition?.
Any suggestion will be gratefully appreciated.

Thanks.
[originally posted on jforum.net by nicomedina]

[Migrated From Jforum.net]

Unfortunately, JForum 2.x doesn't use an DB layer (like Hybernate with is used in version 3). So there is no version specific "dialect" which can be used to quickly change the queries to support version variation. This makes supporting all the variation of Oracle very difficult.

However, if you want to fix your problem, you may be able to do it by simply re-writing the the "Prepared" SQL strings used to get data from the DB to match your version. These are stored in a combination of the generic.sql and oracle.sql files in the config/database subdirectories.

In order to do this, you will need to identify the problem queries and re-write them to match you're version of Oracle...

For example, the error you're reporting is generated by the query below. This is basically extracting the last user created.

UserModel.lastUserRegistered = SELECT * FROM ( \
		SELECT user_id, username, ROW_NUMBER() OVER(ORDER BY user_regdate DESC) - 1 LINENUM FROM jforum_users ORDER BY user_regdate DESC \
	) \
	WHERE LINENUM = 0

I think the big challenge is that Oracle doesn't support a LIMIT / OFFSET type of criteria on SELECT statement.

If you get it working, please post your fix here.


[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

Monroe thanks a lot for your quick reply, I will try fix the problem with that solution and post it here if it worked.
[originally posted on jforum.net by nicomedina]

[Migrated From Jforum.net]

I think there's a limit feature in oracle

select * from (select * from foo) where rownum > 5 and rownum < 10 for instance. I think this syntax should work (it was either oracle syntax or mssql ;))
[originally posted on jforum.net by Sid]