在 BIRT 中运行报告时出现意外异常

Question

下面是一个SQL查询，用于从Oracle数据库11g中检索特定数据，以填充BIRT报告。

select
P.LAST_NAME,
P.FIRST_NAME,
A.EMPLID,
J.EMPL_RCD_NBR,
E.HIRE_DT,
E.SERVICE_DT,
'XXX-XX-' || substr(P.SSN,6,4) SSN,
P.STREET1,
P.STREET2,
P.CITY,
P.STATE,
P.ZIP,
P.COUNTRY,
P.EMAIL_ADDRESS,
P.BIRTHDATE,
P.ETHNIC_GROUP,
P.RACE_ETHNICITY,
P.SEX,
P.MAR_STATUS,
J.EFFDT,
J.EFFSEQ,
J.ACTION,
J.EMPL_STATUS,
J.COMPANY,
J.PAYGROUP,
J.DEPTID,
J.LOCATION,
J.REPORTING_LOCATION,
J.JOBCODE,
J.JOB_ENTRY_DT,
--J.PRIMARY_JOB,
CASE WHEN EXISTS (SELECT 'X' FROM PS_PRIMARY_JOB PJ WHERE PJ.EMPLID = J.EMPLID and PJ.EMPL_RCD_NBR =  J.EMPL_RCD_NBR and     PJ.EFFDT =
coalesce((select max(EFFDT)
from PS_PRIMARY_JOB
where EMPLID = PJ.EMPLID
and EFFDT <= to_date(@SingleQuote.Local.THRUDATE~,'MM/DD/YYYY')),
(select min(EFFDT)
from PS_PRIMARY_JOB
where EMPLID = PJ.EMPLID))) then 'Y' else 'N' end as PRIMARY_JOB,
J.EMPL_TYPE,
J.COMP_FREQUENCY,
J.COMPRATE,
J.RATE_TYPE,
J.RATE_1,
J.STD_HOURS,
J.CURRENCY_CD,
J.REPORTS_TO_ID,
J.FLSA_STATUS,
T.FILE_NBR,
T.FED_ALLOWANCES,
T.FEDERAL_MAR_STATUS,
T.STATE_TAX_CD,
T.SUI_TAX_CD
from 
PS_AUDIT_JOB A
left outer join PS_AL_TAX_DATA T on A.PAYGROUP = T.PAYGROUP and A.FILE_NBR = T.FILE_NBR,
PS_AUDIT_TRANS AT,
PS_PERSONAL_DATA P,
PS_JOB J,
PS_EMPLOYMENT E,
TABLE(DBADM.SECURITYPKG.SEC_MJ_EMPL_F(NULL,'AAA_job_keys6')) B
where A.AUDIT_ACTN = 'A'
and A.ACTION = 'HIR'
and AT.TRANS_SOURCE = '1'
and A.AUDIT_STAMP >= to_date(@SingleQuote.Local.FROMDATE~,'MM/DD/YYYY')
and A.AUDIT_STAMP < to_date(@SingleQuote.Local.THRUDATE~,'MM/DD/YYYY') + 1
and A.AUDIT_OPRID = AT.AUDIT_TRANS_ID
and A.EMPLID = P.EMPLID
and A.EMPLID = J.EMPLID
and A.EMPLID = E.EMPLID
and A.EMPLID = B.EMPLID
and A.EMPL_RCD_NBR = J.EMPL_RCD_NBR
and A.EMPL_RCD_NBR = E.EMPL_RCD_NBR
and A.EMPL_RCD_NBR = B.EMPL_RCD_NBR
and A.EFFDT = J.EFFDT
and A.EFFDT = B.EFFDT
and A.EFFSEQ = J.EFFSEQ
and A.EFFSEQ = B.EFFSEQ
and T.EFFDT = (select max(EFFDT)
from PS_AL_TAX_DATA
where PAYGROUP = A.PAYGROUP
and FILE_NBR = A.FILE_NBR
and EFFDT <= to_date(@SingleQuote.Local.THRUDATE~,'MM/DD/YYYY'))
order by A.EMPLID, A.EFFDT desc, A.EFFSEQ desc

执行它时，它显示以下错误：

org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.

SQL 错误 #1：ORA-00911：无效字符

; java.sql.SQLSyntaxErrorException： ORA-00911： 无效字符

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)
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:980)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:607)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1251)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:196)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:155)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:117)
at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.refreshMetaData(DataSetMetaDataHelper.java:385)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.refreshMetaData(DataRequestSessionImpl.java:414)
at org.eclipse.birt.report.designer.data.ui.dataset.ExternalUIUtil.updateColumnCache(ExternalUIUtil.java:109)
at org.eclipse.birt.report.designer.data.ui.providers.DefaultDataServiceProvider.updateColumnCache(DefaultDataServiceProvider.java:139)
at org.eclipse.birt.report.designer.internal.ui.data.DataService.updateColumnCache(DataService.java:173)
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetUIUtil.updateColumnCache(DataSetUIUtil.java:99)
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.okPressed(DataSetEditor.java:687)
at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.buttonPressed(AbstractPropertyDialog.java:650)
at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:618)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:249)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4410)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4228)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3816)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:818)
at org.eclipse.jface.window.Window.open(Window.java:794)
at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:117)
at org.eclipse.birt.report.designer.data.ui.providers.EditableDataSetNodeProvider.performEdit(EditableDataSetNodeProvider.java:102)
at org.eclipse.birt.report.designer.internal.ui.views.DefaultNodeProvider.performRequest(DefaultNodeProvider.java:472)
at org.eclipse.birt.report.designer.internal.ui.views.actions.EditAction.doAction(EditAction.java:85)
at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)
at org.eclipse.birt.report.designer.internal.ui.views.RenameListener.doubleClick(RenameListener.java:214)
at org.eclipse.jface.viewers.StructuredViewer$1.run(StructuredViewer.java:833)
at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)
at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:50)
at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:173)
at org.eclipse.jface.viewers.StructuredViewer.fireDoubleClick(StructuredViewer.java:830)
at org.eclipse.jface.viewers.AbstractTreeViewer.handleDoubleSelect(AbstractTreeViewer.java:1470)
at org.eclipse.jface.viewers.StructuredViewer$4.widgetDefaultSelected(StructuredViewer.java:1264)
at org.eclipse.jface.util.OpenStrategy.fireDefaultSelectionEvent(OpenStrategy.java:252)
at org.eclipse.jface.util.OpenStrategy.access$0(OpenStrategy.java:249)
at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:311)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4410)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4228)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3816)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$4.run(PartRenderingEngine.java:1121)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1022)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:150)
at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:687)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:604)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:148)
at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:138)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:388)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:243)
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:673)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:610)
at org.eclipse.equinox.launcher.Main.run(Main.java:1519)

原因：org.eclipse.birt.report.data.oda.jdbc.JDBCException：SQL 语句不返回 ResultSet 对象。 SQL 错误 #1：ORA-00911：无效字符

; java.sql.SQLSyntaxErrorException： ORA-00911： 无效字符

at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:482)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingDefaultPolicy(Statement.java:389)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:330)
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:423)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:390)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)
... 71 more

请问有人可以帮忙吗？

Answer 1

您的查询多次包含"@SingleQuote.Local.THRUDATE~"。 您可能要做的是使用参数。

但是语法无效(尝试在 SQL*Developer 中运行查询(。

查询参数(绑定变量(的语法为 ？ 或 :p_name。

？是通常的JDBC语法，我认为:p_name是特定于Oracle的。

BIRT支持这两个版本。

但是，如果您使用的是 :p_name 语法，请注意 BIRT 在内部将每个 "：xxxxx" 转换为 "？"。

因此，每个绑定变量必须在查询中恰好出现一次，并且查询中的位置必须与参数定义的顺序匹配。

实现此目的的常用技巧是 WITH 语法：

with params as (
select :p_deptno      as deptno
, :p_ename_like  as ename_like
from DUAL
)
select *
from emp, params
where (params.deptno is null or emp.deptno = params.deptno)
and emp.ename like nvl(:p_ename_like, '%')

在这种情况下，您需要两个数据集参数， 其中第一个是 deptno，第二个是 ename 的模式。