DBeaver运行Oracle动态PIVOT语句报ORA-00900错误解决方案咨询

你的猜测正确，COLUMN ... NEW_VALUE 是Oracle官方专属客户端（SQL*Plus、SQL Developer）特有的交互类命令，不属于标准SQL语法范畴，DBeaver默认的SQL执行器不识别该类非标准命令，因此会抛出ORA-00900: 无效SQL语句错误。

方案1：PL/SQL动态拼接执行（适配Oracle 12c及以上版本，无需额外配置） #

你可以通过PL/SQL匿名块动态拼接PIVOT所需的月份参数，再通过Oracle 12c新增的DBMS_SQL.RETURN_RESULT直接返回查询结果，DBeaver可正常识别该输出，适配后代码如下：
首先保留你原来的变量定义：

@set deb_period = 202010
@set end_period = 202109

再执行动态查询块：

DECLARE
    v_deb_period VARCHAR2(6) := :deb_period;
    v_end_period VARCHAR2(6) := :end_period;
    v_mois_list VARCHAR2(4000);
    v_sql CLOB;
BEGIN
    -- 生成带引号的月份列表，用于PIVOT IN子句
    SELECT LISTAGG(''''||mois||''' AS "'||mois||'"', ',') 
    WITHIN GROUP (ORDER BY mois) 
    INTO v_mois_list
    FROM (
        SELECT to_char(add_months(to_date(v_deb_period, 'yyyymm'), level -1), 'yyyymm') mois
        FROM dual
        CONNECT BY LEVEL <= months_between(to_date(v_end_period, 'yyyymm'), to_date(v_deb_period, 'yyyymm')) + 1
    );

    -- 拼接完整的PIVOT查询语句
    v_sql := '
    WITH tmp AS (
        SELECT to_char(add_months(to_date('''||v_deb_period||''', ''yyyymm''), level -1), ''yyyymm'') mois
        FROM dual
        CONNECT BY LEVEL <= months_between(to_date('''||v_end_period||''', ''yyyymm''), to_date('''||v_deb_period||''', ''yyyymm'')) + 1
    )
    SELECT *    
    FROM (
        SELECT DISTINCT
            per.inss
            ,tmp.mois
            ,CASE fam.type
                WHEN ''MONO'' THEN ''M''
                ELSE ''D''
            END AS typ_fam
        FROM
            beneficiaries ben
            LEFT JOIN lumpsum_beneficiaries lum ON lum.actor_id = ben.actor_id
            INNER JOIN actors ac ON ac.actor_id = ben.actor_id
            INNER JOIN persons per ON per.person_id = ac.person_id
                AND per.inss IN (00000018043)
            INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
            INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
            INNER JOIN tmp ON 
                TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(tmp.mois, ''yyyymm''), -1)),''yyyymmdd'')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,''yyyymmdd'')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,''yyyymmdd'')),99991231)
        WHERE lum.actor_id IS NULL
    ) 
    PIVOT (
        MAX(TYP_FAM)
        FOR MOIS IN ('||v_mois_list||')
    )
    ORDER BY 1 ASC';

    -- 执行SQL并返回结果集
    DBMS_SQL.RETURN_RESULT(DBMS_SQL.PARSE(v_sql, DBMS_SQL.NATIVE));
END;
/

方案2：开启DBeaver的SQL*Plus兼容模式（改动最小） #

你也可以通过开启DBeaver的兼容模式直接支持原SQL的语法，操作步骤如下：

• 右键对应Oracle连接，选择「编辑连接」
• 找到「SQL编辑器」→「SQL处理」选项卡
• 勾选「启用SQL*Plus命令支持」，保存配置后重启连接，即可直接运行你在SQL Developer中编写的原代码。

方案3：低版本Oracle适配方案（12c以下） #

如果你的Oracle版本低于12c，不支持DBMS_SQL.RETURN_RESULT，可以改用PIVOT XML实现动态行转列，输出结果为XML格式，DBeaver可直接点击查看结构化内容：

@set deb_period = 202010
@set end_period = 202109

WITH tmp AS (
    SELECT to_char(add_months(to_date(:deb_period, 'yyyymm'), level -1), 'yyyymm') mois
    FROM dual
    CONNECT BY LEVEL <= months_between(to_date(:end_period, 'yyyymm'), to_date(:deb_period, 'yyyymm')) + 1
)
SELECT *    
FROM (
    SELECT DISTINCT
        per.inss
        ,tmp.mois
        ,CASE fam.type
            WHEN 'MONO' THEN 'M'
            ELSE 'D'
        END AS typ_fam
    FROM
        beneficiaries ben
        LEFT JOIN lumpsum_beneficiaries lum ON lum.actor_id = ben.actor_id
        INNER JOIN actors ac ON ac.actor_id = ben.actor_id
        INNER JOIN persons per ON per.person_id = ac.person_id
            AND per.inss IN (00000018043)
        INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
        INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
        INNER JOIN tmp ON 
            TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(tmp.mois, 'yyyymm'), -1)),'yyyymmdd')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,'yyyymmdd')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,'yyyymmdd')),99991231)
    WHERE lum.actor_id IS NULL
) 
PIVOT XML (
    MAX(TYP_FAM)
    FOR MOIS IN (ANY)
)
ORDER BY 1 ASC

内容的提问来源于stack exchange，提问作者Questvba