时间戳

首页 > 解决方案 > PLS-00405：此上下文中不允许子查询

oracle - PLS-00405：此上下文中不允许子查询

问题描述

我正在开发一个启动和停止服务的触发器。关键是如果我的触发器首先发现一个错误，它不会停止服务：

ORA-00604: error occurred at recursive SQL level 3
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 316

因此，为了避免收到错误，我需要先检查服务是否在线，只有在线时才停止它，但 PL/SQL 不接受 select into if 语句。我应该怎么做？

CREATE OR REPLACE TRIGGER managed_service_start
    AFTER STARTUP ON DATABASE
DECLARE
    pdb_role VARCHAR2(64);
    host VARCHAR2(64);
    pdb_name VARCHAR2(64);
    rw_service VARCHAR2(64);
    ro_service VARCHAR2(64);
BEGIN
    SELECT database_role INTO pdb_role FROM v$database;
    SELECT host_name INTO host FROM v$instance;
    SELECT Upper(sys_context ('userenv', 'con_name')) INTO pdb_name FROM dual;
    SELECT rw_service into rw_service FROM dual;
    SELECT ro_service into ro_service FROM dual;
    IF pdb_role = 'PRIMARY' THEN
        DBMS_SERVICE.START_SERVICE(rw_service);
        IF (ro_service) IN (SELECT name FROM v$services) THEN
            DBMS_SERVICE.STOP_SERVICE(ro_service);
        END IF;
    ELSE
        -- IF host IN ('host_name1','host_name2') THEN
        IF host LIKE '%de%' THEN
            DBMS_SERVICE.START_SERVICE(ro_service);
            IF (rw_service) IN (SELECT name FROM v$services) THEN
                DBMS_SERVICE.STOP_SERVICE(rw_service);
            END IF;
        ELSE
            IF (ro_service) IN (SELECT name FROM v$services) THEN
                DBMS_SERVICE.STOP_SERVICE(ro_service);
            END IF;
        END IF;
    END IF;
END;
/


    LINE/COL ERROR
-------- -----------------------------------------------------------------
15/3     PL/SQL: Statement ignored
15/22    PLS-00405: subquery not allowed in this context
22/4     PL/SQL: Statement ignored
22/23    PLS-00405: subquery not allowed in this context
26/4     PL/SQL: Statement ignored
26/23    PLS-00405: subquery not allowed in this context

标签： oracleselectplsql

解决方案

目前尚不清楚您从何处获取这些值rw_service，ro_service因此我只是在这里硬编码它们作为示例。

CREATE OR REPLACE TRIGGER managed_service_start
    AFTER STARTUP ON DATABASE
DECLARE
    pdb_role VARCHAR2(64);
    host VARCHAR2(64);
    pdb_name VARCHAR2(64);
    rw_service VARCHAR2(64) := 'rw_service_name';
    ro_service VARCHAR2(64) := 'ro_service_name';
    rw_up varchar2(1);
    ro_up varchar2(1);
BEGIN
    SELECT database_role INTO pdb_role FROM v$database;
    SELECT host_name INTO host FROM v$instance;
    SELECT Upper(sys_context ('userenv', 'con_name')) INTO pdb_name FROM dual;
    -- validate that services are up. if they are down the variable will be null.
    select max('Y') into rw_up
            from v$services
            where name = rw_service;
    select max('Y') into ro_up
            from v$services
            where name = ro_service;
    IF pdb_role = 'PRIMARY' THEN
        DBMS_SERVICE.START_SERVICE(rw_service);
        IF (ro_up is not null) THEN
            DBMS_SERVICE.STOP_SERVICE(ro_service);
        END IF;
    ELSE
        -- IF host IN ('host_name1','host_name2') THEN
        IF host LIKE '%de%' THEN
            DBMS_SERVICE.START_SERVICE(ro_service);
            IF (rw_up is not null) THEN
                DBMS_SERVICE.STOP_SERVICE(rw_service);
            END IF;
        ELSE
            IF (ro_up is not null) THEN
                DBMS_SERVICE.STOP_SERVICE(ro_service);
            END IF;
        END IF;
    END IF;
END;
/

不要将查询放在 IF 语句中，而是将 IF 逻辑放在查询中，然后在 IF 语句中使用结果。

推荐阅读