Oracle 19c创建含游标存储过程遇end-of-file错误及执行无效求助

问题重现 #

• 环境：Oracle 19c
• 操作：创建包含游标的存储过程sw
• 编译报错：提示end-of-file语法错误，存储过程状态为无效
• 执行报错：ORA-06550: line 1, column 7: PLS-00905: object SYS.SW is invalid

原代码 #

kIsH@Xhydra<>create or replace procedure sw as
        cursor cevent
        IS
                select sw.sid,sw.seq#,sw.event,sw.p1text,
                         sw.p1,sw.p1raw,sw.p2text,sw.p2,
                         sw.p2raw,sw.p3text,sw.p3,sw.p3raw,
                         sw.wait_time,sw.seconds_in_wait,
                         sw.state,s.serial#,s.username,
                         s.osuser,s.paddr,s.logon_time,
                         s.process,s.sql_hash_value,s.saddr,
                         s.module,s.row_wait_obj#,s.row_wait_file#,
                         s.row_wait_block#,s.row_wait_row#
                from v$session_wait sw, v$session s
                where sw.sid = s.sid
                and s.username is not null
                and s.type <> 'BACKGROUND';
        /

编译错误信息 #

Warning: Procedure created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/29    PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         begin function pragma procedure subtype type <an identifier>
         <a double-quoted delimited-identifier> current cursor delete
         exists prior

执行错误信息 #

kIsH@Xhydra<>exec sw;
BEGIN sw; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYS.SW is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

错误原因 #

PL/SQL存储过程要求完整结构：

CREATE OR REPLACE PROCEDURE 过程名 AS
    -- 声明部分（游标、变量等）
BEGIN
    -- 执行逻辑部分
END 过程名;
/

你仅定义了游标（声明部分），未添加BEGIN和END包裹的执行逻辑块，Oracle解析器读取到文件结尾时仍未找到预期的执行起始标记BEGIN，触发end-of-file语法错误。

修正方案 #

方案1：添加空执行逻辑（仅确保编译通过） #

如果暂时不需要具体业务逻辑，可添加空的BEGIN...END块，用NULL语句避免空块报错：

create or replace procedure sw as
    cursor cevent
    IS
            select sw.sid,sw.seq#,sw.event,sw.p1text,
                     sw.p1,sw.p1raw,sw.p2text,sw.p2,
                     sw.p2raw,sw.p3text,sw.p3,sw.p3raw,
                     sw.wait_time,sw.seconds_in_wait,
                     sw.state,s.serial#,s.username,
                     s.osuser,s.paddr,s.logon_time,
                     s.process,s.sql_hash_value,s.saddr,
                     s.module,s.row_wait_obj#,s.row_wait_file#,
                     s.row_wait_block#,s.row_wait_row#
            from v$session_wait sw, v$session s
            where sw.sid = s.sid
            and s.username is not null
            and s.type <> 'BACKGROUND';
BEGIN
    NULL; -- 空语句，避免空执行块报错
END sw;
/

方案2：添加游标遍历逻辑（实现实际功能） #

如果需要处理游标查询结果，可添加遍历逻辑，示例如下：

create or replace procedure sw as
    cursor cevent
    IS
            select sw.sid,sw.seq#,sw.event,sw.p1text,
                     sw.p1,sw.p1raw,sw.p2text,sw.p2,
                     sw.p2raw,sw.p3text,sw.p3,sw.p3raw,
                     sw.wait_time,sw.seconds_in_wait,
                     sw.state,s.serial#,s.username,
                     s.osuser,s.paddr,s.logon_time,
                     s.process,s.sql_hash_value,s.saddr,
                     s.module,s.row_wait_obj#,s.row_wait_file#,
                     s.row_wait_block#,s.row_wait_row#
            from v$session_wait sw, v$session s
            where sw.sid = s.sid
            and s.username is not null
            and s.type <> 'BACKGROUND';
    v_event cevent%rowtype; -- 声明变量存储游标单行数据
BEGIN
    -- 打开游标
    OPEN cevent;
    -- 循环遍历游标
    LOOP
        FETCH cevent INTO v_event;
        EXIT WHEN cevent%NOTFOUND; -- 无数据时退出循环
        -- 示例：打印会话用户名和SID，可替换为实际业务逻辑
        DBMS_OUTPUT.PUT_LINE('会话用户名: ' || v_event.username || ', SID: ' || v_event.sid);
    END LOOP;
    -- 关闭游标
    CLOSE cevent;
END sw;
/

验证步骤 #

1. 执行修正后的代码编译存储过程
2. 检查编译状态：执行show errors procedure sw;，若无报错则编译成功
3. 执行存储过程：
   • 如果使用了DBMS_OUTPUT，先执行set serveroutput on;开启输出
   • 执行exec sw;

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