PL/SQL 执行过程失败，无法检索编译警告 [英] PL/SQL executing procedure fails and unable to retrieve compile warnings

问题描述

在之前的一篇文章中，我在编译下面的过程时遇到了一些问题(它应该根据作者编写的任务数量更新属性 profile) - 现在，它确实编译(虽然有警告)，当我尝试执行它时，它失败了.我不明白为什么.我正在使用 ExecuteQuery 连接到远程 Oracle 数据库.程序中涉及的表为:

Task(TaskID, ..., AuthorID)作者(作者ID，个人资料，姓名，...)

这是程序(我的具体问题如下):

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';创建或替换程序 profil_stufe是CURSOR c1 IS SELECT AuthorID, COUNT(AuthorID) as Total FROM Task按作者 ID 分组；结果 INTEGER c1%ROWTYPE;开始打开 c1;环形FETCH c1 INTO 结果；当 c1%NOTFOUND 时退出；IF(result.Total = 2 OR result.Total = 3) THENUPDATE Author SET profile = 'Advanced' WHERE AuthorID = result.AuthorID;万一;IF(result.Total >= 4) THENUPDATE Author SET profile = 'proficient' WHERE AuthorID = result.AuthorID;万一;结束循环；关闭 c1;结尾;

我的问题:

1. 首先，我无法检索编译程序时产生的警告.我尝试了诸如 select plsql_warnings from user_plsql_object_settings ps where ps.name = 'profil_stufe'; 或 select * from user_errors ur where ur.name = 'profil_stufe'; 之类的查询，但都没有提供任何结果，即使该过程编译时出现警告.

2. 当我执行 execute profil_stufe 时出现错误

<块引用>

ORA-06550:第 1 行，第 16 列:PLS-00905:对象 CS261_20.PROFIL_STUFE无效 ORA-06550:第 1 行，第 7 列:PL/SQL:忽略语句

有人可以帮我吗?我知道，我可以避免创建过程并以其他方式实现相同的功能.但似乎我遗漏了一些 PL/SQL 的基本概念，我真的很想了解它们......所以，感谢您的帮助！

解决方案

为什么你的变量声明为:result INTEGER c1%ROWTYPE;应该是 result c1%ROWTYPE;

In an earlier post, I had some problems with compiling the procedure below (which is supposed to update the attribute profile based on how many tasks an author has written) - now, it does compile (with warnings though) and when I try to execute it, it fails. I cannot figure out why. I'm using ExecuteQuery to connect to a remote Oracle database. The tables involved in the procedure are:

Task(TaskID, ..., AuthorID)
Author(AuthorID, profile, name, ...)

Here is the procedure (my specific questions follow below):

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE profil_stufe
IS
    CURSOR c1 IS SELECT AuthorID, COUNT(AuthorID) as Total FROM Task
                 GROUP BY AuthorID;
    result INTEGER c1%ROWTYPE;
BEGIN
    OPEN c1;
        LOOP
        FETCH c1 INTO result;
        EXIT WHEN c1%NOTFOUND;

      IF(result.Total = 2 OR result.Total = 3) THEN
        UPDATE Author SET profile = 'Advanced' WHERE AuthorID = result.AuthorID;
      END IF;

      IF(result.Total >= 4) THEN
        UPDATE Author SET profile = 'proficient' WHERE AuthorID = result.AuthorID;
         END IF;
    END LOOP;
  CLOSE c1;
END;

My questions:

1. First of all, I am not able to retrieve the warnings produced when compiling the procedure. I tried queries like select plsql_warnings from user_plsql_object_settings ps where ps.name = 'profil_stufe'; or select * from user_errors ur where ur.name = 'profil_stufe'; but neither deliver any result, even though the procedure does compile with warnings.

2. When I perform execute profil_stufe I get an error

ORA-06550: Row 1, Column 16: PLS-00905: object CS261_20.PROFIL_STUFE is invalid ORA-06550: Row 1, Column 7: PL/SQL: Statement ignored

Can somebody help me out? I know, that I could avoid creating a procedure and implement the same functionality otherwise. But it seems like I am missing some basic concepts of PL/SQL and I would really like to get to understand them ... So, thanks for your help!

解决方案

why do you have the variable declaration as : result INTEGER c1%ROWTYPE; It should be result c1%ROWTYPE;

这篇关于PL/SQL 执行过程失败，无法检索编译警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！