ORA-00932 when running report on Oracle

hanbing.yin · July 13, 2022, 9:13am

Hi Team,

A customer reported below issue when running charts report or pivot table. They are using Oracle10g:

Error Code: 932
Call: SELECT DISTINCT t1.ID, t1.DELETE_TS, t1.DELETED_BY, t1.LOCALE_NAMES, t1.NAME, t1.VERSION, t1.GROUP_ID, t1.DEFAULT_TEMPLATE_ID FROM REPORT_TEMPLATE t0, REPORT_REPORT t1 WHERE (((t0.OUTPUT_TYPE = ?) AND (t1.DELETE_TS IS NULL)) AND (((t0.REPORT_ID = t1.ID) AND (t0.DELETE_TS IS NULL)) AND (t0.DELETE_TS IS NULL)))
bind => [70]
Query: ReadAllQuery(referenceClass=Report sql="SELECT DISTINCT t1.ID, t1.DELETE_TS, t1.DELETED_BY, t1.LOCALE_NAMES, t1.NAME, t1.VERSION, t1.GROUP_ID, t1.DEFAULT_TEMPLATE_ID FROM REPORT_TEMPLATE t0, REPORT_REPORT t1 WHERE (((t0.OUTPUT_TYPE = ?) AND (t1.DELETE_TS IS NULL)) AND (((t0.REPORT_ID = t1.ID) AND (t0.DELETE_TS IS NULL)) AND (t0.DELETE_TS IS NULL)))")
FetchGroup(){deleteTs, localeNames, templates => {class java.lang.Object=FetchGroup(templates){deleteTs, reportOutputType, code, customDefinition, custom, name, report => {class java.lang.Object=FetchGroup(report){deleteTs, localeNames, name, id, version, deletedBy}}, id, version, deletedBy}}, defaultTemplate, name, id, version, deletedBy, group}
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:722) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2102) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:265) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:277) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.executeCall(ExpressionQueryMechanism.java:2938) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:263) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:334) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:746) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2826) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2779) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:584) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1229) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1188) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1276) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3043) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1895) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1877) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1842) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262) ~[org.eclipse.persistence.jpa-2.7.9-6-jmix.jar:na]
... 113 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:937) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1172) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1101) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1426) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1309) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3855) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1059) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
... 135 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected - got CLOB

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637) ~[ojdbc8-21.5.0.0.jar:21.5.0.0.0]
... 153 common frames omitted

y.konyashkina · July 22, 2022, 8:19am

Hi @hanbing.yin
Thank you for reporting the problem, I’ve reproduced the error and created a GitHub issue.

Regards,
Yulia

hanbing.yin · December 12, 2022, 3:25am

Hi,

Could you please check if the same exception happens when adding widget to Dashboard add-on?

The issue is reported in CN forum:图表小部件添加至画布时会报OracleDatabaseException: ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB - 问题反馈 - jmix.cn

t.rusiaikina · December 14, 2022, 5:31am

Hi @hanbing.yin
I couldn’t replicate the issue.

Addon dashboard was installed
A fragment of the screen was created and diagram were added to it
A screen fragment has been added to the dashboard

Regards,
Tatiana

hanbing.yin · December 14, 2022, 9:11am

Have you tried to add a chart widget to the canvas?

t.rusiaikina · December 15, 2022, 8:31am

A screen fragment containing, Chart has been created

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<fragment xmlns="http://jmix.io/schema/ui/fragment"
          xmlns:chart="http://jmix.io/schema/ui/charts">
    <layout>
        <vbox>
        <textArea caption="msg://textArea.caption" id="idCapt"/>
        <chart:pieChart id="idPIe"/>
        </vbox>
    </layout>
</fragment>

Screen fragment added to dashboard

Can you attach the project in which the error appears?

hanbing.yin · December 16, 2022, 6:55am

are you using oracle database? Below image is the error when trying to add Chart:

t.rusiaikina · December 19, 2022, 6:31am

Yes, I use Oracle 12

Show what values you take from the entity. What type of data are they? What type of chart do you use?

Is it possible to display the chart on the browse or edit screen?

My entity

Screen fragment

<fragment xmlns="http://jmix.io/schema/ui/fragment"
          xmlns:chart="http://jmix.io/schema/ui/charts">
    <data>
        <collection id="newEntitiesDc" class="com.company.forum.entity.NewEntity">
            <fetchPlan extends="_base"/>
            <loader id="newEntitiesDl">
                <query>
                    <![CDATA[select e from NewEntity e]]>
                </query>
            </loader>
        </collection>
    </data>
    <facets>
        <dataLoadCoordinator auto="true"/>
    </facets>
    <layout>
        <vbox>
        <textArea caption="msg://textArea.caption" id="idCapt"/>
        <chart:pieChart id="idPIe" dataContainer="newEntitiesDc"/>
        </vbox>
    </layout>
</fragment>

Controller fragment

 @Autowired
    private PieChart idPIe;
    @Autowired
    private CollectionContainer<NewEntity> newEntitiesDc;

   @Subscribe
    public void onInit(InitEvent event) {
        idPIe.setWidth("700px");
        idPIe.setTitleField("v2").setValueField("v1").setStartAngle(312).setLegend(new Legend().setMarkerType(MarkerType.CIRCLE)
                .setPosition(LegendPosition.RIGHT).setMarginRight(80)).addLabels(new Label().setText("Sample Chart").setSize(26)
                .setBold(true).setAlign(Align.CENTER), new Label().setText("extra information").setAlign(Align.RIGHT)).setLabelTickColor(Color.GOLDENROD)
                .setColors(Arrays.asList(Color.valueOf("#446493"), Color.valueOf("#5E3D2C"), Color.valueOf("#D0A557")))
               .setDataProvider(new ContainerDataProvider(newEntitiesDc));
    }```