如何检测Oracle数据库是否支持自动增量? [英] How to detect if Oracle database supports auto increment?

问题描述

我正在一个项目中，根据不同环境中可用的资源，我们可能在其中使用Oracle 11g或Oracle 12c数据库-我知道这不是很理想:(

I'm working on a project in which we may be using an Oracle 11g or an Oracle 12c database depending on what's available in various environments -- not ideal I know :(

我的项目是一个Spring Boot应用程序，我正在使用Liquibase来管理数据库对象.

My project is a Spring Boot application and I'm using Liquibase to manage the database objects.

我的问题是，如果我在Oracle 12c数据库上运行，我想在Oracle 11g上利用自动递增ID的优势，那么我将不得不依靠触发器和序列.

My problem is that if I'm running on an Oracle 12c database, I'd like to take advantage of auto incrementing IDs while on Oracle 11g, I'll have to rely on a trigger and a sequence.

是否可以检测当前版本的Oracle是否支持自动递增，还是必须查看版本号?

我目前使用的SQL可以正常工作，只是查看版本号:

The SQL that I currently have, which works, just looks at the version number:

SELECT
    CASE
        WHEN (TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.') - 1)) >= 12) THEN
            1
        ELSE
            0
    END AS SUPPORTS_AUTO_INC
FROM
    V$INSTANCE;

我宁愿有一些SQL来检查功能是否可用(在CSS中使用功能时，相同的主体).

I'd much rather have some SQL which checks if the feature is available (same principal when using features in CSS).

谁知道，也许Oracle将来会删除此功能.

Who knows, maybe Oracle will remove this feature in the future.

推荐答案

我会检查版本. Oracle确实有一个V$SQL_FEATURE表和一个DBA_FEATURE_USAGE_STATISTICS表，但是似乎都没有任何专门用于标识列的条目.

I would check the version. Oracle does have a V$SQL_FEATURE table and a DBA_FEATURE_USAGE_STATISTICS table, but neither seem to have any entries specifically about identity columns.

这是您可以做到的真正骗人的方式...

Here is a really hokey way you could do it...

select decode(count(*),0,'N','Y') supports_identity_flag
from dba_tab_columns
where table_name = 'DBA_TAB_COLUMNS'
and column_name = 'IDENTITY_COLUMN';

这篇关于如何检测Oracle数据库是否支持自动增量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！