web stats
Mirth 3.0.0.6931 and Oracle 11gR2 Error when viewing dashboard - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 11-08-2013, 09:18 AM
beepboy beepboy is offline
What's HL7?
 
Join Date: Aug 2013
Posts: 5
beepboy is on a distinguished road
Default Mirth 3.0.0.6931 and Oracle 11gR2 Error when viewing dashboard

Hi all, we recently upgraded from Mirth 1.8 on PSQL (which ran great) to Mirth 3.0.0 on Oracle 11gR2 but having an issue with accessing the Dashboard. The messages are routing properly as far as we can tell.

Error is...
Code:
com.mirth.connect.client.core.ClientException: method failed: HTTP/1.1 500 ### Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected ### The error may exist in oracle/oracle-message.xml ### The error may involve Message.searchMessages-Inline ### The error occurred while setting parameters ### SQL: SELECT A.* FROM (SELECT B.*, ROWNUM B_ROWNUM FROM ( SELECT MESSAGE.ID, MESSAGE.SERVER_ID, MESSAGE.RECEIVED_DATE, MESSAGE.PROCESSED, MESSAGE.ORIGINAL_ID, MESSAGE.IMPORT_ID, MESSAGE.IMPORT_CHANNEL_ID, METADATA_IDS FROM ( SELECT M.ID, LISTAGG(MM.ID, ',') WITHIN GROUP (ORDER BY M.ID) METADATA_IDS FROM D_M1 M JOIN D_MM1 MM ON MM.MESSAGE_ID = M.ID WHERE M.ID <= ? GROUP BY M.ID ORDER BY M.ID DESC ) SUBQUERY INNER JOIN D_M1 MESSAGE ON SUBQUERY.ID = MESSAGE.ID ORDER BY MESSAGE.ID DESC ) B WHERE ROWNUM <= (0 101)) A WHERE B_ROWNUM > 0 ### Cause: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expecte
com.mirth.connect.client.core.ServerConnection.executePostMethod(ServerConnection.java:130)
com.mirth.connect.client.core.Client.getMessages(Client.java:933)
com.mirth.connect.client.core.PaginatedMessageList.getItems(PaginatedMessageList.java:68)
com.mirth.connect.util.PaginatedList.loadPageNumber(PaginatedList.java:73)
com.mirth.connect.client.ui.browsers.message.MessageBrowser$8.doInBackground(MessageBrowser.java:783)
com.mirth.connect.client.ui.browsers.message.MessageBrowser$8.doInBackground(MessageBrowser.java:776)
javax.swing.SwingWorker$1.call(Unknown Source)
java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
java.util.concurrent.FutureTask.run(Unknown Source)
javax.swing.SwingWorker.run(Unknown Source)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)
Is it a known issue, or is there a workaround? I couldn't find it in JIRA when I looked.

mirth.properties
Code:
...
database = oracle
database.url = jdbc:oracle:thin:@//oracleserver:1521/mirthdb

# database credentials
database.username = hdpmirth03
database.password = hdpmirth03
Any help/tips would be appreciated. Let me know if you need more information.
Reply With Quote
  #2  
Old 11-08-2013, 02:33 PM
brentm brentm is offline
Mirth Employee
 
Join Date: Jan 2012
Posts: 85
brentm is on a distinguished road
Default

I've created an issue in JIRA to track this:

http://www.mirthcorp.com/community/i...wse/MIRTH-3054

What operating system is Oracle running on? And is it 11gR2 express or enterprise?

Thanks,
Reply With Quote
  #3  
Old 11-11-2013, 12:31 PM
beepboy beepboy is offline
What's HL7?
 
Join Date: Aug 2013
Posts: 5
beepboy is on a distinguished road
Default

Thank you for your response. The database is running on another node on a Red Hat Enterprise Linux Server release 5.5 (Tikanga). It is an enterprise version of Oracle. My DBA thinks the issue lies with this statement, "SELECT M.ID, LISTAGG(MM.ID, ',') WITHIN GROUP (ORDER BY M.ID) METADATA_IDS."
Reply With Quote
  #4  
Old 11-12-2013, 10:38 AM
brentm brentm is offline
Mirth Employee
 
Join Date: Jan 2012
Posts: 85
brentm is on a distinguished road
Default

Thanks. I was unable to reproduce the error using Oracle 11gR2, however it appears this is a common error message if you are using 11gR1, can you confirm that you are using R2 and not R1?

Thanks
Reply With Quote
  #5  
Old 11-12-2013, 01:25 PM
beepboy beepboy is offline
What's HL7?
 
Join Date: Aug 2013
Posts: 5
beepboy is on a distinguished road
Default

Thanks Brent, I will check again and let you know.
Reply With Quote
  #6  
Old 11-12-2013, 01:47 PM
beepboy beepboy is offline
What's HL7?
 
Join Date: Aug 2013
Posts: 5
beepboy is on a distinguished road
Default

You are right. I am 99% sure we're running 11gR1. We can close this issue.

Quote:
The Oracle base for ORACLE_HOME=/u01/oracle/product/11.1.0.7/db is /u01/oracle/product
Reply With Quote
Reply

Tags
mirth 3.0, oracle database

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 07:51 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation