invalid datatype error when connecting with oracle database

[lujingyicst]

When I try to connect hasura to my oracle database, I keep getting following error " java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype\n\n\tat oracle.jdbc.driver.T4CTTIoer11.processError" (I only paste the part of error message).

Has anyone else experienced the same issue as me and found a solution?

[GavinRay97]

Hiya @lujingyicst

Would it be possible to share the full stack trace message?
If it's not possible to discern the error from the message, we can take a look at the logs for your instance as a next-step.

[lujingyicst]

Hi Gavin, Thanks for your reply. Here is the full stack trace message. "stacktrace": "java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype\n\n\tat oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702)\n\tat oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608)\n\tat oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1335)\n\tat oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1041)\n\tat oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:443)\n\tat oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:533)\n\tat oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:262)\n\tat oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1137)\n\tat oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1318)\n\tat oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1197)\n\tat oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1721)\n\tat oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1518)\n\tat oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3751)\n\tat oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3922)\n\tat oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1103)\n\tat io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78)\n\tat io.opentelemetry.instrumentation.jdbc.internal.OpenTelemetryStatement.wrapCall(OpenTelemetryStatement.java:294)\n\tat io.opentelemetry.instrumentation.jdbc.internal.OpenTelemetryPreparedStatement.executeQuery(OpenTelemetryPreparedStatement.java:60)\n\tat io.hasura.services.schemaGenerators.OracleSchemaGenerator.queryDatabase(OracleSchemaGenerator.kt:193)\n\tat io.hasura.services.schemaGenerators.BaseSchemaGenerator.getSchema(BaseSchemaGenerator.kt:36)\n\tat io.hasura.services.dataConnectors.OracleDataConnectorService.executeGetSchema(OracleDataConnectorService.kt:87)\n\tat io.hasura.services.dataConnectors.BaseDataConnectorService.getSchema(BaseDataConnectorService.kt:122)\n\tat io.hasura.services.dataConnectors.OracleDataConnectorService_Subclass.getSchema$$superforward(Unknown Source)\n\tat io.hasura.services.dataConnectors.OracleDataConnectorService_Subclass$$function$$7.apply(Unknown Source)\n\tat io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:77)\n\tat io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:66)\n\tat io.quarkus.opentelemetry.runtime.tracing.cdi.WithSpanInterceptor.span(WithSpanInterceptor.java:66)\n\tat io.quarkus.opentelemetry.runtime.tracing.cdi.WithSpanInterceptor_Bean.intercept(Unknown Source)\n\tat io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)\n\tat io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:34)\n\tat io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27)\n\tat io.hasura.services.dataConnectors.OracleDataConnectorService_Subclass.getSchema(Unknown Source)\n\tat io.hasura.controllers.DataConnectorResource.getSchemaWithRequest(DataConnectorResource.kt:106)\n\tat io.hasura.controllers.DataConnectorResource_Subclass.getSchemaWithRequest$$superforward(Unknown Source)\n\tat io.hasura.controllers.DataConnectorResource_Subclass$$function$$4.apply(Unknown Source)\n\tat io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:77)\n\tat io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:66)\n\tat io.quarkus.opentelemetry.runtime.tracing.cdi.WithSpanInterceptor.span(WithSpanInterceptor.java:66)\n\tat io.quarkus.opentelemetry.runtime.tracing.cdi.WithSpanInterceptor_Bean.intercept(Unknown Source)\n\tat io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)\n\tat io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:34)\n\tat io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27)\n\tat io.hasura.controllers.DataConnectorResource_Subclass.getSchemaWithRequest(Unknown Source)\n\tat io.hasura.controllers.DataConnectorResource$quarkusrestinvoker$getSchemaWithRequest_346619f777a11d1f6019d5b502ea626944986626.invoke(Unknown Source)\n\tat org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)\n\tat io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)\n\tat org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:145)\n\tat io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576)\n\tat org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)\n\tat org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)\n\tat org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)\n\tat org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)\n\tat io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)\n\tat java.base/java.lang.Thread.run(Thread.java:833)\nCaused by: Error : 902, Position : 421, SQL = SELECT\n json_array(tables.OWNER, tables.TABLE_NAME) AS TableName,\n tables.TABLE_TYPE AS TableType,\n table_comments.COMMENTS AS Description,\n columns.Columns,\n pks.PrimaryKeys,\n fks.ForeignKeys\nFROM (\n \nSELECT *\nFROM (\n SELECT tables.OWNER, tables.TABLE_NAME, 'TABLE' AS TABLE_TYPE\n FROM ALL_TABLES tables\n \n INNER JOIN (\n SELECT jt.*\n FROM json_table(CAST(:1 AS JSON), '$[*]' COLUMNS (schema VARCHAR2 PATH '$.s', tablename VARCHAR2 PATH '$.t')) AS jt\n ) tables_filter ON tables.OWNER = tables_filter.schema AND tables.TABLE_NAME = tables_filter.tablename\n \n WHERE tables.TEMPORARY = 'N'\n UNION ALL\n SELECT views.OWNER, views.VIEW_NAME, 'VIEW' AS TABLE_TYPE\n FROM ALL_VIEWS views\n) tables\nWHERE tables.OWNER IN (:2 )\n\n) tables\nINNER JOIN (\n SELECT OWNER, TABLE_NAME, COMMENTS FROM ALL_TAB_COMMENTS\n UNION ALL\n SELECT OWNER, MVIEW_NAME AS TABLE_NAME, COMMENTS FROM ALL_MVIEW_COMMENTS\n ) table_comments\n ON tables.OWNER = table_comments.OWNER AND tables.TABLE_NAME = table_comments.TABLE_NAME\nINNER JOIN ALL_USERS users\n ON users.USERNAME = tables.OWNER\nLEFT JOIN ( -- Must be a LEFT JOIN because INNER performs very poorly for an unknown reason\n SELECT\n columns.OWNER,\n columns.TABLE_NAME,\n (\n json_arrayagg(\n json_object(\n 'name' VALUE columns.COLUMN_NAME,\n 'description' VALUE column_comments.COMMENTS,\n 'type' VALUE columns.DATA_TYPE,\n 'numeric_scale' VALUE columns.DATA_SCALE,\n 'nullable' VALUE case when columns.NULLABLE = 'Y' then 'true' else 'false' end,\n 'auto_increment' VALUE case when columns.IDENTITY_COLUMN = 'YES' then 'true' else 'false' end\n )\n ORDER BY columns.COLUMN_ID\n RETURNING CLOB\n )\n ) AS Columns\n FROM ALL_TAB_COLUMNS columns\n LEFT OUTER JOIN ALL_COL_COMMENTS column_comments\n ON columns.OWNER = column_comments.OWNER\n AND columns.TABLE_NAME = column_comments.TABLE_NAME\n AND columns.COLUMN_NAME = column_comments.COLUMN_NAME\n GROUP BY columns.OWNER, columns.TABLE_NAME\n )\n columns\n ON columns.OWNER = tables.OWNER\n AND columns.TABLE_NAME = tables.TABLE_NAME\nLEFT OUTER JOIN (\n SELECT\n pk_constraints.OWNER,\n pk_constraints.TABLE_NAME,\n (\n json_arrayagg(\n pk_columns.COLUMN_NAME\n ORDER BY pk_columns.POSITION\n RETURNING CLOB\n )\n ) AS PrimaryKeys\n FROM ALL_CONSTRAINTS pk_constraints\n LEFT OUTER JOIN ALL_CONS_COLUMNS pk_columns\n ON pk_constraints.CONSTRAINT_NAME = pk_columns.CONSTRAINT_NAME\n AND pk_constraints.OWNER = pk_columns.OWNER\n AND pk_constraints.TABLE_NAME = pk_columns.TABLE_NAME\n WHERE pk_constraints.CONSTRAINT_TYPE = 'P'\n GROUP BY pk_constraints.OWNER, pk_constraints.TABLE_NAME\n )\n pks\n ON pks.OWNER = tables.OWNER\n AND pks.TABLE_NAME = tables.TABLE_NAME\nLEFT OUTER JOIN LATERAL (\n SELECT\n fks.OWNER,\n fks.TABLE_NAME,\n (\n json_objectagg (\n fks.FK_CONSTRAINT_NAME VALUE fks.Constraint\n RETURNING CLOB\n )\n ) AS ForeignKeys\n FROM (\n SELECT\n fk_constraints.OWNER,\n fk_constraints.TABLE_NAME,\n fk_constraints.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME,\n json_object(\n 'foreign_table' VALUE json_array(fk_pk_constraints.OWNER, fk_pk_constraints.TABLE_NAME),\n 'column_mapping' VALUE (\n json_objectagg (\n fk_columns.COLUMN_NAME VALUE fk_pk_columns.COLUMN_NAME\n )\n )\n ) AS Constraint\n FROM ALL_CONSTRAINTS fk_constraints\n INNER JOIN ALL_CONSTRAINTS fk_pk_constraints\n ON fk_pk_constraints.OWNER = fk_constraints.R_OWNER\n AND fk_pk_constraints.CONSTRAINT_NAME = fk_constraints.R_CONSTRAINT_NAME\n INNER JOIN ALL_CONS_COLUMNS fk_columns\n ON fk_columns.OWNER = fk_constraints.OWNER\n AND fk_columns.TABLE_NAME = fk_constraints.TABLE_NAME\n AND fk_columns.CONSTRAINT_NAME = fk_constraints.CONSTRAINT_NAME\n INNER JOIN ALL_CONS_COLUMNS fk_pk_columns\n ON fk_pk_columns.OWNER = fk_pk_constraints.OWNER\n AND fk_pk_columns.TABLE_NAME = fk_pk_constraints.TABLE_NAME\n AND fk_pk_columns.CONSTRAINT_NAME = fk_pk_constraints.CONSTRAINT_NAME\n AND fk_pk_columns.POSITION = fk_columns.POSITION\n AND fk_constraints.CONSTRAINT_TYPE = 'R'\n WHERE fk_constraints.OWNER = tables.OWNER AND fk_constraints.TABLE_NAME = tables.TABLE_NAME\n GROUP BY fk_constraints.OWNER, fk_constraints.TABLE_NAME, fk_constraints.CONSTRAINT_NAME, fk_pk_constraints.OWNER, fk_pk_constraints.TABLE_NAME\n ) fks\n GROUP BY fks.OWNER, fks.TABLE_NAME\n )\n fks\n ON fks.OWNER = tables.OWNER\n AND fks.TABLE_NAME = tables.TABLE_NAME\nWHERE users.ORACLE_MAINTAINED = 'N', Original SQL = SELECT\n json_array(tables.OWNER, tables.TABLE_NAME) AS TableName,\n tables.TABLE_TYPE AS TableType,\n table_comments.COMMENTS AS Description,\n columns.Columns,\n pks.PrimaryKeys,\n fks.ForeignKeys\nFROM (\n \nSELECT *\nFROM (\n SELECT tables.OWNER, tables.TABLE_NAME, 'TABLE' AS TABLE_TYPE\n FROM ALL_TABLES tables\n \n INNER JOIN (\n SELECT jt.*\n FROM json_table(CAST(? AS JSON), '$[*]' COLUMNS (schema VARCHAR2 PATH '$.s', tablename VARCHAR2 PATH '$.t')) AS jt\n ) tables_filter ON tables.OWNER = tables_filter.schema AND tables.TABLE_NAME = tables_filter.tablename\n \n WHERE tables.TEMPORARY = 'N'\n UNION ALL\n SELECT views.OWNER, views.VIEW_NAME, 'VIEW' AS TABLE_TYPE\n FROM ALL_VIEWS views\n) tables\nWHERE tables.OWNER IN (?)\n\n) tables\nINNER JOIN (\n SELECT OWNER, TABLE_NAME, COMMENTS FROM ALL_TAB_COMMENTS\n UNION ALL\n SELECT OWNER, MVIEW_NAME AS TABLE_NAME, COMMENTS FROM ALL_MVIEW_COMMENTS\n ) table_comments\n ON tables.OWNER = table_comments.OWNER AND tables.TABLE_NAME = table_comments.TABLE_NAME\nINNER JOIN ALL_USERS users\n ON users.USERNAME = tables.OWNER\nLEFT JOIN ( -- Must be a LEFT JOIN because INNER performs very poorly for an unknown reason\n SELECT\n columns.OWNER,\n columns.TABLE_NAME,\n (\n json_arrayagg(\n json_object(\n 'name' VALUE columns.COLUMN_NAME,\n 'description' VALUE column_comments.COMMENTS,\n 'type' VALUE columns.DATA_TYPE,\n 'numeric_scale' VALUE columns.DATA_SCALE,\n 'nullable' VALUE case when columns.NULLABLE = 'Y' then 'true' else 'false' end,\n 'auto_increment' VALUE case when columns.IDENTITY_COLUMN = 'YES' then 'true' else 'false' end\n )\n ORDER BY columns.COLUMN_ID\n RETURNING CLOB\n )\n ) AS Columns\n FROM ALL_TAB_COLUMNS columns\n LEFT OUTER JOIN ALL_COL_COMMENTS column_comments\n ON columns.OWNER = column_comments.OWNER\n AND columns.TABLE_NAME = column_comments.TABLE_NAME\n AND columns.COLUMN_NAME = column_comments.COLUMN_NAME\n GROUP BY columns.OWNER, columns.TABLE_NAME\n )\n columns\n ON columns.OWNER = tables.OWNER\n AND columns.TABLE_NAME = tables.TABLE_NAME\nLEFT OUTER JOIN (\n SELECT\n pk_constraints.OWNER,\n pk_constraints.TABLE_NAME,\n (\n json_arrayagg(\n pk_columns.COLUMN_NAME\n ORDER BY pk_columns.POSITION\n RETURNING CLOB\n )\n ) AS PrimaryKeys\n FROM ALL_CONSTRAINTS pk_constraints\n LEFT OUTER JOIN ALL_CONS_COLUMNS pk_columns\n ON pk_constraints.CONSTRAINT_NAME = pk_columns.CONSTRAINT_NAME\n AND pk_constraints.OWNER = pk_columns.OWNER\n AND pk_constraints.TABLE_NAME = pk_columns.TABLE_NAME\n WHERE pk_constraints.CONSTRAINT_TYPE = 'P'\n GROUP BY pk_constraints.OWNER, pk_constraints.TABLE_NAME\n )\n pks\n ON pks.OWNER = tables.OWNER\n AND pks.TABLE_NAME = tables.TABLE_NAME\nLEFT OUTER JOIN LATERAL (\n SELECT\n fks.OWNER,\n fks.TABLE_NAME,\n (\n json_objectagg (\n fks.FK_CONSTRAINT_NAME VALUE fks.Constraint\n RETURNING CLOB\n )\n ) AS ForeignKeys\n FROM (\n SELECT\n fk_constraints.OWNER,\n fk_constraints.TABLE_NAME,\n fk_constraints.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME,\n json_object(\n 'foreign_table' VALUE json_array(fk_pk_constraints.OWNER, fk_pk_constraints.TABLE_NAME),\n 'column_mapping' VALUE (\n json_objectagg (\n fk_columns.COLUMN_NAME VALUE fk_pk_columns.COLUMN_NAME\n )\n )\n ) AS Constraint\n FROM ALL_CONSTRAINTS fk_constraints\n INNER JOIN ALL_CONSTRAINTS fk_pk_constraints\n ON fk_pk_constraints.OWNER = fk_constraints.R_OWNER\n AND fk_pk_constraints.CONSTRAINT_NAME = fk_constraints.R_CONSTRAINT_NAME\n INNER JOIN ALL_CONS_COLUMNS fk_columns\n ON fk_columns.OWNER = fk_constraints.OWNER\n AND fk_columns.TABLE_NAME = fk_constraints.TABLE_NAME\n AND fk_columns.CONSTRAINT_NAME = fk_constraints.CONSTRAINT_NAME\n INNER JOIN ALL_CONS_COLUMNS fk_pk_columns\n ON fk_pk_columns.OWNER = fk_pk_constraints.OWNER\n AND fk_pk_columns.TABLE_NAME = fk_pk_constraints.TABLE_NAME\n AND fk_pk_columns.CONSTRAINT_NAME = fk_pk_constraints.CONSTRAINT_NAME\n AND fk_pk_columns.POSITION = fk_columns.POSITION\n AND fk_constraints.CONSTRAINT_TYPE = 'R'\n WHERE fk_constraints.OWNER = tables.OWNER AND fk_constraints.TABLE_NAME = tables.TABLE_NAME\n GROUP BY fk_constraints.OWNER, fk_constraints.TABLE_NAME, fk_constraints.CONSTRAINT_NAME, fk_pk_constraints.OWNER, fk_pk_constraints.TABLE_NAME\n ) fks\n GROUP BY fks.OWNER, fks.TABLE_NAME\n )\n fks\n ON fks.OWNER = tables.OWNER\n AND fks.TABLE_NAME = tables.TABLE_NAME\nWHERE users.ORACLE_MAINTAINED = 'N', Error Message = ORA-00902: invalid datatype\n\n\tat oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710)\n\t... 53 more\n"

…

On Thu, Oct 19, 2023 at 10:21 AM Gavin Ray ***@***.***> wrote: Hiya @lujingyicst <https://github.com/lujingyicst> Would it be possible to share the full stack trace message? If it's not possible to discern the error from the message, we can take a look at the logs for your instance as a next-step. — Reply to this email directly, view it on GitHub <#9941 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANATXVWMRVL372ZR2XSQDJDYAEZNZAVCNFSM6AAAAAA6DAE55KVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TGMRYHEZDC> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- This message contains information which may be confidential and/or protected by attorney-client privilege.  Unless you are the addressee, you may not use, copy or disclose to anyone this message or any information contained in this message.  If you have received this message in error, please send me an email and delete this message.   Thank you.