Why I am getting an error when doing SQL query from the database for the sequence nexval or currval records via iReports? (SOLVED)

[aivarasrudys]

I am coming to error report when I try to refer to SEQUENCE.nextval or SEQUENCE.currval when doing SQL querry in iReports. Is it not supported or am I doing it wrong? The below example generates an error.

SQL : 

select WMUSP_PL32JMB1_SEQ.nextval, WMUSP_PL32JMB1_SEQ.currval from DIL_LIVE_ORDERS_VIEW

SEQUENCE:
CREATE SEQUENCE  WMUSP_PL32JMB1_SEQ  MINVALUE 1 MAXVALUE 999999999999999 INCREMENT BY 1 START WITH 901 CACHE 20 NOORDER  CYCLE  NOKEEP  NOSCALE  GLOBAL ;
ERROR:

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Blank_A4_1.
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:357)
    at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:68)
    at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:70)
    at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:487)
    at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:49)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312)
    ... 5 more
Caused by: Error : 942, Position : 7, Sql = select WMUSP_PL32JMB1_SEQ.nextval from dual, OriginalSql = select WMUSP_PL32JMB1_SEQ.nextval from dual, Error Msg = ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 20 more
 

 

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Jivan Phadtare]

Looking at the error message it is showing caused by "Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist"

Kindly make sure in your oracle databae "DIL_LIVE_ORDERS_VIEW" this view is present.
It's possible that the table or view you are referencing does not exist in the database. Verify that the table or view exists by checking the database 

Apart from this also check on below possibilities :

Incorrect Table or View Name
Missing Schema or Owner Name
Insufficient Privileges : Check if the user executing the query has the necessary privileges to access the table or view. Ensure that the user has been granted the appropriate 
 

[aivarasrudys]

Spot on, the access for the certain user to that sequence was not given. Have granted and it all works! Thanks for the detailed answer. Sorted!