#15434: Oracle 12C Table Properties -> java.sql.SQLSyntaxErrorException: ORA-00904: "T"."INMEMORY": invalid identifier

tariqrahiman reported 2017-08-09T17:22:10Z · last modified 2018-02-15T09:59:47Z

Oracle 12C Table Properties -> java.sql.SQLSyntaxErrorException: ORA-00904: "T"."INMEMORY": invalid identifier

Dev	tomconrad
QA	ivangron

Priority	Major
Complexity	Unknown

Component	DB - Oracle
Version	19.0

Oracle 12C Table Properties -> java.sql.SQLSyntaxErrorException: ORA-00904: "T"."INMEMORY": invalid identifier

Version: 19.0.0-alpha-65

Build #: 54930

Build Date: 2017-Jun-28 05:00:44 PM

1. Navigate to AWS Oracle 12c [Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production ] and select any table and take Table Properties

2. I took FY_12_1 Schema and answers table

3. You get below exception

4. We have a lower version of 12.1.0.1.0 and need to check if the patch was SVN#22692 is only for v12.1.0.2

Broken in 19.0.0-alpha-65 Build #: 54930, Build Date: 2017-Jun-28 05:00:44 PM

This is the patch which created the exception SVN#22692 : INMEMORY keyword is not extracted in ADS for Oracle v12.1.0.2.

java.sql.SQLSyntaxErrorException: ORA-00904: "T"."INMEMORY": invalid identifier

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)

at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:733)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)

at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1276)

at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:366)

at \\.\\.\\हिñçêČάй語简�?한\\.oꊕꇁᠠ̾5.executeQuery(Unknown Source)

at \\.\\.\\हिñçêČάй語简�?한\\.xᠦꃌᚬꑋstaticshort.g(Unknown Source)

at \\.\\.\\हिñçêČάй語简�?한\\.xᠦꃌᚬꑋstaticshort.e(Unknown Source)

at \\.\\.\\हिñçêČάй語简�?한\\.Dꊶꂪꀧꏚthrows.bMB(Unknown Source)

at \\.\\.\\हिñçêČάй語简�?한\\.jꇄꏌᠰꎘ5enum.process(Unknown Source)

at com.common.ui.util.BackgroundThread.run(Unknown Source)

Caused by: Error : 904, Position : 1936, Sql = SELECT decode(t.TABLESPACE_NAME, null, p.DEF_TABLESPACE_NAME, t.TABLESPACE_NAME), decode(t.PCT_FREE, null, decode(p.DEF_PCT_FREE, 'DEFAULT', null, p.DEF_PCT_FREE), t.PCT_FREE), decode(t.INI_TRANS, null, decode(p.DEF_INI_TRANS, 'DEFAULT', null, p.DEF_INI_TRANS), t.INI_TRANS), decode(t.MAX_TRANS, null, decode(p.DEF_MAX_TRANS, 'DEFAULT', null, p.DEF_MAX_TRANS), t.MAX_TRANS), decode(t.PCT_USED, null, decode(p.DEF_PCT_USED, 'DEFAULT', null, p.DEF_PCT_USED), t.PCT_USED), decode(t.INITIAL_EXTENT, null, (decode(p.DEF_INITIAL_EXTENT, 'DEFAULT', null, p.DEF_INITIAL_EXTENT)*DECODE(s.block_size, null, 8192, s.block_size)), t.INITIAL_EXTENT), decode(t.NEXT_EXTENT, null, (decode(p.DEF_NEXT_EXTENT, 'DEFAULT', null, p.DEF_NEXT_EXTENT)*DECODE(s.BLOCK_SIZE, null, 8192, s.BLOCK_SIZE)), t.NEXT_EXTENT), decode(t.MIN_EXTENTS, null, decode(p.DEF_MIN_EXTENTS, 'DEFAULT', null, p.DEF_MIN_EXTENTS), t.MIN_EXTENTS), decode(t.MAX_EXTENTS, null, decode(p.DEF_MAX_EXTENTS, 'DEFAULT', null, p.DEF_MAX_EXTENTS), t.MAX_EXTENTS), decode(t.PCT_INCREASE, null, decode(p.DEF_PCT_INCREASE, 'DEFAULT', null, p.DEF_PCT_INCREASE), t.PCT_INCREASE), decode(t.FREELISTS, null, decode(p.DEF_FREELISTS, 'DEFAULT', null, 0, null, p.DEF_FREELISTS), t.FREELISTS), decode(t.FREELIST_GROUPS, null, decode(p.DEF_FREELIST_GROUPS, 'DEFAULT', null, 0, null, p.DEF_FREELIST_GROUPS), t.FREELIST_GROUPS), t.DEGREE, decode(t.LOGGING, null, p.DEF_LOGGING, t.LOGGING), decode(t.BUFFER_POOL, null, p.DEF_BUFFER_POOL, t.BUFFER_POOL), t.CACHE, t.MONITORING, t.ROW_MOVEMENT, t.DEPENDENCIES, decode(t.COMPRESSION, null, p.DEF_COMPRESSION, t.COMPRESSION), decode(t.COMPRESS_FOR, null, p.DEF_COMPRESS_FOR, t.COMPRESS_FOR), decode(p.def_max_size, 'DEFAULT', null, p.def_max_size*DECODE(s.block_size, null, 8192, s.block_size)), s.max_size, s.max_extents, ts.CONTENTS, t.FLASH_CACHE, p.def_indexing, (CASE cls.COLUMN_NAME when 'ORA_ARCHIVE_STATE' then 'TRUE' ELSE 'FALSE' END) AS ROW_ARCHIVAL, t.INMEMORY FROM DBA_tables t LEFT JOIN DBA_part_tables p ON t.owner = p.owner and t.table_name = p.table_name LEFT JOIN DBA_TABLESPACES s ON p.DEF_TABLESPACE_NAME = s.TABLESPACE_NAME LEFT JOIN DBA_TABLESPACES ts ON t.TABLESPACE_NAME = ts.TABLESPACE_NAME LEFT JOIN DBA_TAB_COLS cls ON t.table_name = cls.TABLE_NAME AND t.owner = cls.OWNER AND cls.COLUMN_NAME = 'ORA_ARCHIVE_STATE' WHERE t.table_name = 'ACCESS$' AND t.owner = 'SYS' , OriginalSql = SELECT decode(t.TABLESPACE_NAME, null, p.DEF_TABLESPACE_NAME, t.TABLESPACE_NAME), decode(t.PCT_FREE, null, decode(p.DEF_PCT_FREE, 'DEFAULT', null, p.DEF_PCT_FREE), t.PCT_FREE), decode(t.INI_TRANS, null, decode(p.DEF_INI_TRANS, 'DEFAULT', null, p.DEF_INI_TRANS), t.INI_TRANS), decode(t.MAX_TRANS, null, decode(p.DEF_MAX_TRANS, 'DEFAULT', null, p.DEF_MAX_TRANS), t.MAX_TRANS), decode(t.PCT_USED, null, decode(p.DEF_PCT_USED, 'DEFAULT', null, p.DEF_PCT_USED), t.PCT_USED), decode(t.INITIAL_EXTENT, null, (decode(p.DEF_INITIAL_EXTENT, 'DEFAULT', null, p.DEF_INITIAL_EXTENT)*DECODE(s.block_size, null, 8192, s.block_size)), t.INITIAL_EXTENT), decode(t.NEXT_EXTENT, null, (decode(p.DEF_NEXT_EXTENT, 'DEFAULT', null, p.DEF_NEXT_EXTENT)*DECODE(s.BLOCK_SIZE, null, 8192, s.BLOCK_SIZE)), t.NEXT_EXTENT), decode(t.MIN_EXTENTS, null, decode(p.DEF_MIN_EXTENTS, 'DEFAULT', null, p.DEF_MIN_EXTENTS), t.MIN_EXTENTS), decode(t.MAX_EXTENTS, null, decode(p.DEF_MAX_EXTENTS, 'DEFAULT', null, p.DEF_MAX_EXTENTS), t.MAX_EXTENTS), decode(t.PCT_INCREASE, null, decode(p.DEF_PCT_INCREASE, 'DEFAULT', null, p.DEF_PCT_INCREASE), t.PCT_INCREASE), decode(t.FREELISTS, null, decode(p.DEF_FREELISTS, 'DEFAULT', null, 0, null, p.DEF_FREELISTS), t.FREELISTS), decode(t.FREELIST_GROUPS, null, decode(p.DEF_FREELIST_GROUPS, 'DEFAULT', null, 0, null, p.DEF_FREELIST_GROUPS), t.FREELIST_GROUPS), t.DEGREE, decode(t.LOGGING, null, p.DEF_LOGGING, t.LOGGING), decode(t.BUFFER_POOL, null, p.DEF_BUFFER_POOL, t.BUFFER_POOL), t.CACHE, t.MONITORING, t.ROW_MOVEMENT, t.DEPENDENCIES, decode(t.COMPRESSION, null, p.DEF_COMPRESSION, t.COMPRESSION), decode(t.COMPRESS_FOR, null, p.DEF_COMPRESS_FOR, t.COMPRESS_FOR), decode(p.def_max_size, 'DEFAULT', null, p.def_max_size*DECODE(s.block_size, null, 8192, s.block_size)), s.max_size, s.max_extents, ts.CONTENTS, t.FLASH_CACHE, p.def_indexing, (CASE cls.COLUMN_NAME when 'ORA_ARCHIVE_STATE' then 'TRUE' ELSE 'FALSE' END) AS ROW_ARCHIVAL, t.INMEMORY FROM DBA_tables t LEFT JOIN DBA_part_tables p ON t.owner = p.owner and t.table_name = p.table_name LEFT JOIN DBA_TABLESPACES s ON p.DEF_TABLESPACE_NAME = s.TABLESPACE_NAME LEFT JOIN DBA_TABLESPACES ts ON t.TABLESPACE_NAME = ts.TABLESPACE_NAME LEFT JOIN DBA_TAB_COLS cls ON t.table_name = cls.TABLE_NAME AND t.owner = cls.OWNER AND cls.COLUMN_NAME = 'ORA_ARCHIVE_STATE' WHERE t.table_name = 'ACCESS$' AND t.owner = 'SYS' , Error Msg = ORA-00904: "T"."INMEMORY": invalid identifier

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

... 18 more

1 attachment

Screen Shot 2017-08-09 at 9.27.55 am.png

2017-08-09T17:22:10Z

265 KB

All Comments (7) Change History

tomconrad 2017-08-09T20:32:51Z · (edited)

svn# 55445 - This was caused because the in memory feature was released in 12.2. The code was checking for 12.0 and greater which caused the in memory clause to be added to the extraction query for release 12.0. That column was not available in 12.0 so the query errored. Supporting Oracle documentation

SachinPrakash 2017-08-09T21:44:39Z

@tom: code review?

ajinkya(*) 2017-08-10T11:18:29Z · (edited)

Hi All,

I think we should not fix this exception. Please find the below details for why I think so:

Oracle In-memory was first introduce in 12.1.0.2.0. Before that one patch was provided by Oracle i.e. 12.1.0.1.0 which does not include this feature. This feature is then included in the subsequent releases i.e. 12.2.0.1.0.
Current ADS does not differentiate between the different patches provided by DB vendors which means 12.1.0.1.0 & 12.1.0.2.0 will be treated same by ADS and we code according to the latest patch considering that customer is using the latest patch.

The support for In-memory was added to ADS in VA#9627. While adding support we added it for all the versions of Oracle released after 12.1.0.1.0 though the feature is introduce in 12.1.0.2.0. For this we modified the current SQL according to 12.1.0.2.0. This is the reason an exception is being thrown for 12.1.0.1.0 since the SQL fails to execute for 12.1.0.1.0.

The fix added in this issue here moves the supports for In-memory feature for the version 12.2.x.x.x from 12.1.x.x.x which I don't see completely correct since 12.1.0.2.0 supports this feature. Please refer the very first para on PDF-page#6(doc page#1) of this documentation.

Can we revisit this issue one more time?

tomconrad 2017-08-10T15:47:12Z

Hi Ajinkya,

You are correct that the inmemory feature was released in 12.1.0.2.0. Unfortunately, our version comparison resolution only lets us compare major and minor version. The metadata revision is going to be zero so the only real way to get the patch level is to translate the metadata product version. This is probably reliable for Oracle but I would not want to count on it. That said, not fixing the issue throws an exception for any release of 12.1 that doesn't have inmemory. Throwing an exception is not acceptable in this case. I think providing inmemory support from 12.2 forward is an acceptable solution.

Hope that helps, Tom

tomconrad 2017-08-10T20:33:27Z

Ivan did a code review.

Grushenets_Yurii 2018-01-30T11:26:47Z

@nhilam:

This issue is verified and tested on Aqua Data Studio 19.0 - 42 build. There are no errors in the 'View Log' window after opening table properties.

Screenshot: https://www.screencast.com/t/JQET6ANvrjHJ

Yevhen 2018-02-15T09:01:28Z

Verified on ADS 19-beta-46
Yurii please Close.

Search Tips

Aqua Data Studio / nhilam

Oracle 12C Table Properties -> java.sql.SQLSyntaxErrorException: ORA-00904: "T"."INMEMORY": invalid identifier

1 attachment

Issue #15434

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Oracle 12C Table Properties -> java.sql.SQLSyntaxErrorException: ORA-00904: "T"."INMEMORY": invalid identifier

1 attachment

Issue #15434

Completion