how to pass multiple sql query with oracle

I am porting one of birt report DB which has been using MSQL server to oracle ,, but looks like oracl DB engine is not accepting multiple sql query ... 
 
 
this works ok in MSQL but not in oracle 
 
beforeopen dataset script 
 
this.queryText = "INSERT into DBSNMP.EMAILADDRESSES (NAME,EMAIL) VALUES ('xyz','abcd')"; 
 
this.queryText += " select * from DBSNMP.EMAILADDRESSES " 
 
//Packages.java.lang.System.out.println("Insert Statement is: " + this.queryText); 
 
 
and if i comment the first line query does work in oracle 
 
//this.queryText = "INSERT into DBSNMP.EMAILADDRESSES (NAME,EMAIL) VALUES ('xyz','abcd')" 
 
this.queryText = " select * from DBSNMP.EMAILADDRESSES " 
 
//Packages.java.lang.System.out.println("Insert Statement is: " + this.queryText); 
 
 
 
looks like unlike MSQL server oracle is not accepting the two this.queryText .... 
 
 
not sure what's wrong here ,,, 
 
is there any other optional way wher we can use multiple sql query to this.queryText......

Find more posts tagged with

Comments

mwilliams

From the above, I can't really tell what you're trying to do with your query. What does the entire query look like? Are you doing a union? Or what? Let me know.

ossvisor

this is how the dataset beforeopen script looks like .. it works perfect with MSQL server but does not work with oracle

//Get the report parameters
ParamName = reportContext.getParameterValue("Name");
ParamEmail = reportContext.getParameterValue("Email");

this.queryText = "INSERT into DBSNMP.EMAILADDRESSES (NAME,EMAIL) VALUES ('" + ParamName +"','" + ParamEmail + "')";

this.queryText += " select * from DBSNMP.EMAILADDRESSES where EMAIL = '" + ParamEmail +"'"

//Packages.java.lang.System.out.println("Insert Statement is: " + this.queryText);

alex520

maybe you can try to add a semicolon after your first statement, like :

xxxx + ParamEmail + "');";
hope it works.

ossvisor

@alex520 Icon

that was the first thought that striked me when i stuck in to this one ... i tried that but no luck ....

mwilliams

Nevermind my above post, I was looking at the first line wrong. I was looking at it as if both had += rather than the first being just =. Based on what I see now, it seems that you have the correct syntax for this. What is the error you're getting? Or is the result set just blank?

ossvisor

here is error output ... what i think is that for some reason oracle doesn't accept multiple query like msql server 
 
 
16-Jun-2011 21:11:03 org.eclipse.birt.report.debug.internal.core.vm.ReportVMServer start 
INFO: [Server] client accepted 
16-Jun-2011 21:11:03 org.eclipse.birt.report.debug.internal.core.vm.ReportVMServer$1 run 
INFO: [Server] enter request dispatching 
16-Jun-2011 21:11:04 org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher configEngine 
INFO: User class path received: 
>>>> Compiled Source: /report/data-sets/oda-data-set[@id="198"]/method[@name="beforeOpen"] 
function __bm_beforeOpen() { 
//Get the report parameters 
ParamName = reportContext.getParameterValue("Name"); 
ParamEmail = reportContext.getParameterValue("Email"); 
 
 
this.queryText = "INSERT into DBSNMP.EMAILADDRESSES (NAME,EMAIL) VALUES ('" + ParamName +"','" + ParamEmail + "')"; 
 
this.queryText += " select * from DBSNMP.EMAILADDRESSES where EMAIL = '" + ParamEmail +"'" 
 
Packages.java.lang.System.out.println("Insert Statement is: " + this.queryText); 
 
 
} 
>>>> end compilation. 
>>>> Compiled Source: /report/data-sets/oda-data-set[@id="198"]/method[@name="beforeOpen"] 
__bm_beforeOpen() 
>>>> end compilation. 
>>>> Frame Source Name: /report/data-sets/oda-data-set[@id="198"]/method[@name="beforeOpen"] 
>>>> Frame Function Name: __bm_beforeOpen 
>>>> Enter script. 2 
>>>> Line changed to: 2 
>>>> Line changed to: 3 
>>>> Line changed to: 6 
>>>> Line changed to: 8 
>>>> Line changed to: 10 
Insert Statement is: INSERT into DBSNMP.EMAILADDRESSES (NAME,EMAIL) VALUES ('newname','newemail') select * from DBSNMP.EMAILADDRESSES where EMAIL = 'newemail' 
>>>> Exit script. 
16-Jun-2011 21:11:19 org.eclipse.birt.data.engine.odaconsumer.PreparedStatement getRuntimeMetaData 
SEVERE: Cannot get the result set metadata. 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. 
SQL error #1:ORA-00933: SQL command not properly ended 
 
; 
java.sql.SQLException: ORA-00933: SQL command not properly ended 
 
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:471) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:410) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:312) 
at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:412) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:379) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getMetaData(PreparedStatement.java:334) 
at org.eclipse.birt.data.engine.executor.DataSourceQuery.getMetaData(DataSourceQuery.java:416) 
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:336) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:455) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:340) 
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:455) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:145) 
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:616) 
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:152) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265) 
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1890) 
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80) 
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62) 
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46) 
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90) 
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:180) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.createReport(ReportLauncher.java:594) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.renderReport(ReportLauncher.java:541) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.run(ReportLauncher.java:455) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.main(ReportLauncher.java:104) 
at org.eclipse.birt.report.debug.internal.core.ReportDebugger.start(ReportDebugger.java:39) 
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) 
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110) 
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79) 
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:369) 
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179) 
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.equinox.launcher.Main.invokeFramework(Main.java:620) 
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:575) 
at org.eclipse.equinox.launcher.Main.run(Main.java:1408) 
at org.eclipse.equinox.launcher.Main.main(Main.java:1384) 
Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended 
 
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:590) 
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973) 
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119) 
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191) 
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064) 
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989) 
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658) 
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:467) 
... 49 more 
16-Jun-2011 21:11:19 org.eclipse.birt.data.engine.odaconsumer.PreparedStatement getRuntimeMetaData 
SEVERE: Cannot get the result set metadata. 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. 
SQL error #1:ORA-00933: SQL command not properly ended 
 
; 
java.sql.SQLException: ORA-00933: SQL command not properly ended 
 
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:471) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:410) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:312) 
at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:412) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:379) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347) 
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563) 
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:923) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:441) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1103) 
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232) 
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:173) 
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:98) 
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265) 
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1890) 
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80) 
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62) 
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46) 
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90) 
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:180) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.createReport(ReportLauncher.java:594) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.renderReport(ReportLauncher.java:541) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.run(ReportLauncher.java:455) 
at org.eclipse.birt.report.debug.internal.core.launcher.ReportLauncher.main(ReportLauncher.java:104) 
at org.eclipse.birt.report.debug.internal.core.ReportDebugger.start(ReportDebugger.java:39) 
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) 
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110) 
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79) 
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:369) 
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179) 
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.equinox.launcher.Main.invokeFramework(Main.java:620) 
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:575) 
at org.eclipse.equinox.launcher.Main.run(Main.java:1408) 
at org.eclipse.equinox.launcher.Main.main(Main.java:1384) 
Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended 
 
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:590) 
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973) 
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119) 
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191) 
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064) 
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989) 
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658) 
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:467) 
... 46 more 
16-Jun-2011 21:11:20 org.eclipse.birt.report.debug.internal.core.vm.ReportVMServer$1 run 
WARNING: [Server] client disconnected

mwilliams

The only thing I can find looking online is the same thing suggested earlier, having the statements separated by a semi-colon. Maybe have a semi-colon at the end as well.

Or maybe Name and Email need to be 'Name' and 'Email'.

ossvisor

i tried almost all possible combination of putting ";" in the query bit no luck ,,,

is there any other way apart from "this.queryText" we can send two sql query

and surprisingly same report work fine with MSQL

alex520

maybe you can try to use procedure as data source ,but base on your sql , you can use two dataset and using a union dataset to join them

ossvisor

tried to apply the same report on classic car model sample database ,, but no luck ....attaching the report

RobR

Unlike MS SQL Server, Oracle does not support multiple SQL Statments in a prepared statement.

I think you would need to run the insert seperately. Not sure exactly how you would do that...but you would need to create your own Statement and run it.

ossvisor

@Rob
thanks Rob ,,,
that explains why this report works in MSQL and not in oracle

but that still left in the same stuck situation ,,

@forum
any clue how to workaround with this issue

RobR

Since no one has jumped in...

Can you create an Oracle Stored Procedure that does an Insert and a Select?

Something like this person did:

http://www.birt-exchange.org/org/forum/index.php/topic/21636-executing-a-stored-procedure-using-javascript-or-java-event-handler/

ossvisor

thanks rob .... I would go with the procedure only as a last resort ...

I will wait some time (before i start working on the procedures ) and hope if someone comes up with some sort of workaround to this issue ....