Home
Analytics
Report # 4a : SQL Error ORA - 00933
nuraniuscc
Hi Mike,
I am emailing my script. Can you "See" the cause of the ablove error? I don't
understand what it's complaining about.
Thanks
Nurani Sivakumar
Find more posts tagged with
Comments
mwilliams
Hi Nurani,
I don't see any issues with it right off the bat. You don't need to have the '+ MySQLString' in this:
this.queryText = this.queryText + MySQLString + "order by MEMBER, DATACENTER, CYCLE_CODE";
One thing you could try, just to see what your query text looks like after all the if/else statements happen would be to set a variable with the value of the queryText and call that variable in a data item or dynamic text box at the top of your report to see what it looks like. Then you may see some sql syntax errors that are hard to catch in the script.
nuraniuscc
Hi Mike,
I know how to set a variable. In this case I called my variable SQLString
So I Say in my Script window after all the IF/ELSE like
SQLString = this.queryText.
Now, How do I call this into a Dynamic Text Box. How do I bring the variable in? Please explain.
This is very urgent.
Thanks
Nurani Sivakumar
mwilliams
Nurani,
You should be able to just type SQLString; in your dynamic text box. If it doesn't find your variable for some reason, initialize it to an empty string in the initialize method of your report.
nuraniuscc
Mike,
I am seeing the variable. Atleast I can look at something to figure out what is going on.
Nurani Sivakumar
mwilliams
Nurani,
If you can post a screenshot of that or email a screenshot to me, I can look at it as well. A second pair of eyes may help.
nuraniuscc
Hi Mike,
I fixed the seemingly obvious errors and now the query after it has been built looks like this. Now, I found the casue for the error
"Missing right Paranthesis".
Ok, If we have a character value plugged in it needs to be in ' ' Right?
How do I ensure that happens.
Thanks
Nurani Sivakumar
mwilliams
Nurani,
Table id = 604 refers to the table you have bound to that dataSet. If you select the table and look in the general section of your property editor, you should see the id of 604.
nuraniuscc
I think we crossed each other.
My scenario is I have a column called "Consolidation Level" as a Parm which
can take the Vales of "Cycle Day" or "Monthly Cycle". This is what I have
coded in my Report Parameter window.
Now the corresponding database values are 'B' and 'M'.
In my script, I say, reportContext.getParameterValue("Consolidation Level").
I need the Text value to show up as a Heading on the report but when I query the database I need to use the 'B' OR 'M' values.
The Missing right paranthesis is because the above statement in my script
converts to consolidation in (Cycle Time)
This is not correct and the "missing Right Paranthesis) shows up on Cycle time which is not enclosed in ' ' .
So, to summarize
1) How do I indicate the value is a character in my script
2) How do I "switch" values based on where it is being used?
(I need both the Values)
I was also looking into reportContext class to see what options can I use. I didn't have any luck in Googling. So probably what we need is to to use the appropriate class for reportContext, instead of getParameterValue. Iam sure there must be something for accessing the database value (Which I am already having in my SELECT)
Thanks
Nurani Sivakumar
mwilliams
Nurani,
If you have display texts and values set up for your parameter selections, in your script, you can use the value, which would be 'B' or 'M', and in the text box or data element that you display the parameter, you can use the displayText. I think this answers what you're asking.
nuraniuscc
Hi Mike,
Ok, The SQL is working (atleast no syntax error, Basically retrieves no rows)
or atleast I thought until I saw these 2 messages.
I am not sure what is going on. I checked the Script and made sure I didn't mistype etc. Could you please shed some light on this?
Thanks
Nurani Sivakumar
select CYCLE_CODE || '/' || CYCLE_RUN_MONTH || '/' || CYCLE_RUN_YEAR as CYCLE, DATACENTER, MEMBER, BAN, EXPERTKEY, APPLICATION_ID, BP_MTFORMAT, FIXEDDATE, LATESTDATEREJECTED, BA_CONSMETHOD from TBLRMREJECTLIST where BA_CONSMETHOD in ('B') and DATACENTER in ('M01') and CYCLE_RUN_MONTH in (07) and CYCLE_RUN_YEAR in (2008) group by BA_CONSMETHOD,CYCLE_CODE,CYCLE_RUN_MONTH,CYCLE_RUN_YEAR,DATACENTER,MEMBER,BAN,EXPERTKEY,APPLICATION_ID,BP_MTFORMAT,FIXEDDATE,LATESTDATEREJECTED order by BA_CONSMETHOD,CYCLE,DATACENTER,MEMBER,BAN,EXPERTKEY,APPLICATION_ID,BP_MTFORMAT,FIXEDDATE,LATESTDATEREJECTED
OdaDataSet Reject Report:
+ Class reportContext does not implement the org.eclipse.birt.report.engine.api.script.eventhandler.IAutoTextEventHandler interface.
Error.ScriptClassCastError ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Class reportContext does not implement the org.eclipse.birt.report.engine.api.script.eventhandler.IAutoTextEventHandler interface. at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.addException(ScriptExecutor.java:201) at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.addClassCastException(ScriptExecutor.java:162) at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.initEventHandler(DataSetScriptExecutor.java:69) at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.<init>(DataSetScriptExecutor.java:47) at org.eclipse.birt.report.engine.adapter.ModelDteApiAdapter.newOdaDataSet(ModelDteApiAdapter.java:481) at org.eclipse.birt.report.engine.adapter.ModelDteApiAdapter.createDataSetDesign(ModelDteApiAdapter.java:204) at org.eclipse.birt.report.engine.adapter.ModelDteApiAdapter.doDefineDataSet(ModelDteApiAdapter.java:276) at org.eclipse.birt.report.engine.adapter.ModelDteApiAdapter.defineDataSet(ModelDteApiAdapter.java:235) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.prepare(AbstractDataEngine.java:135) at org.eclipse.birt.report.engine.executor.ReportExecutor.execute(ReportExecutor.java:101) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportExecutor.execute(WrappedReportExecutor.java:59) at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplciateReportExecutor.execute(SuppressDuplciateReportExecutor.java:51) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportExecutor.execute(WrappedReportExecutor.java:59) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:149) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72) at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:928) at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:876) at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:123) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:89) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:159) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:111) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:225) at javax.servlet.http.HttpServlet.service(HttpServlet.java:616) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:112) at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) at org.mortbay.http.HttpServer.service(HttpServer.java:909) at org.mortbay.http.HttpConnection.service(HttpConnection.java:820) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)Caused by: org.eclipse.birt.report.engine.api.EngineException: Class reportContext not found. at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.getInstance(ScriptExecutor.java:133) at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.initEventHandler(DataSetScriptExecutor.java:62) ... 61 moreCaused by: java.lang.ClassNotFoundException: reportContext at org.eclipse.osgi.framework.internal.core.BundleLoader.findClassInternal(BundleLoader.java:481) at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:397) at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:385) at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:87) at java.lang.ClassLoader.loadClass(Unknown Source) at org.eclipse.birt.report.engine.executor.ApplicationClassLoader.loadClass(ApplicationClassLoader.java:77) at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.getInstance(ScriptExecutor.java:128) ... 62 more
Text (id = 996):
+ An exception occurred during processing. Please see the following message for details:
Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended
nuraniuscc
Duplicate Comment !!!!
mwilliams
Nurani,
Where are you using reportContext in your script?
nuraniuscc
Hi Mike,
Iam using reportContext in the Script Window(beforeOpen) of the Dataset.
Thanks
Nurani Sivakumar
mwilliams
Nurani,
Sorry. I meant what line? How are you using it? Are you getting the parameters that way?
