【译】Oracle将行变成列

在新发布的Oracle版本中，有些事情会与你擦肩而过。今天我发现了“sys.odcinumberlist”和“sys.odcivarchar2list”。

我想为连接生成一个预定义的列表，以确保每个值都被表示出来，并提出了Oracle 12中的新函数“sys.odcinumberlist”，所以不是新的！有2个简单的函数：“sys.odcinumberlist”和“sys.odcivarchar2list”。

它们如何使用？

SELECT column_value AS number_list
FROM sys.odcinumberlist(1,2,5,7,9,5,2,-34,5,43,2,0)
ORDER BY number_list;

NUMBER_LIST
-----------
        -34
          0
          1
          2
          2
          2
          5
          5
          5
          7
          9
         43

12 rows selected.

Note how I have had to alias the "column value" virtual column to allow mne to sort the list

SELECT column_value AS char_list FROM sys.odcivarchar2list('A','V','DD');

CHAR_LIST
---------
A
V
DD

这种方式不错并且很简单，列表中限制为32768个项目。那么我是如何使用它的呢？我正在检查数据库中存在哪些配置文件（列表比这个列表稍长，SQL也是如此，但这对于演示来说是件好事），所以我生成了已知配置文件的完整列表，以方便加入每个数据库中的现有配置文件。

WITH my_profiles AS (
    SELECT
        column_value AS profile_name
    FROM
        sys.odcivarchar2list(
            'DEFAULT'
          , 'SOME_OTHER_PROFILE' --etc etc etc 
        )
)
SELECT
    mp.profile_name
  , nvl( profile , '<does-not-exist>') AS profile
  , resource_name
  , limit
FROM
    my_profiles  mp
    LEFT OUTER JOIN dba_profiles dp ON ( mp.profile_name = dp.profile )
ORDER BY
    profile
  , resource_name;

PROFILE_NAME         PROFILE              RESOURCE_NAME                  LIMIT
-------------------- -------------------- ------------------------------ -----------
SOME_OTHER_PROFILE   <does-not-exist>
DEFAULT              DEFAULT              COMPOSITE_LIMIT                UNLIMITED
DEFAULT              DEFAULT              CONNECT_TIME                   UNLIMITED
DEFAULT              DEFAULT              CPU_PER_CALL                   UNLIMITED
DEFAULT              DEFAULT              CPU_PER_SESSION                UNLIMITED
DEFAULT              DEFAULT              FAILED_LOGIN_ATTEMPTS          10
DEFAULT              DEFAULT              IDLE_TIME                      UNLIMITED
DEFAULT              DEFAULT              INACTIVE_ACCOUNT_TIME          UNLIMITED
DEFAULT              DEFAULT              LOGICAL_READS_PER_CALL         UNLIMITED
DEFAULT              DEFAULT              LOGICAL_READS_PER_SESSION      UNLIMITED
DEFAULT              DEFAULT              PASSWORD_GRACE_TIME            7
DEFAULT              DEFAULT              PASSWORD_LIFE_TIME             180
DEFAULT              DEFAULT              PASSWORD_LOCK_TIME             1
DEFAULT              DEFAULT              PASSWORD_REUSE_MAX             UNLIMITED
DEFAULT              DEFAULT              PASSWORD_REUSE_TIME            UNLIMITED
DEFAULT              DEFAULT              PASSWORD_ROLLOVER_TIME         -1
DEFAULT              DEFAULT              PASSWORD_VERIFY_FUNCTION       NULL
DEFAULT              DEFAULT              PRIVATE_SGA                    UNLIMITED
DEFAULT              DEFAULT              SESSIONS_PER_USER              UNLIMITED

当然还有许多其他方法也可以做到这一点，包括使用UNPIVOT函数，创建自己的表类型等，但这是我一直在寻找的完美而简单的方式。