oracle存储过程中的错误

发布于 2024-12-04 16:48:59 字数 1078 浏览 2 评论 0原文

我在 Oracle 中执行存储过程时遇到问题。

下面是存储过程：

CREATE OR REPLACE Procedure xxxlist
    (yyyid in NUMBER)    
IS
    xid number(6);
    cursor c1 is select distinct xxxID from MXS.Y where YID=yyyid;
BEGIN
IF NOT c1%ISOPEN THEN 
    OPEN c1;
END IF;
LOOP
    FETCH c1 into xid;
    dbms_output.put_line(TO_CHAR(xid));
    EXIT WHEN c1%NOTFOUND; 
END LOOP;
CLOSE c1;   
END;

当我创建存储过程时，出现以下错误：

Warning: Procedure created with compilation errors.

我执行以下命令以提供有关上述抽象错误消息的更多详细信息。

SHO ERR;

针对上述命令，我得到以下详细信息：

Errors for PROCEDURE XXXLIST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/18     PL/SQL: SQL Statement ignored
5/52     PL/SQL: ORA-00942: table or view does not exist

现在，我了解到表名或 sql 查询本身存在问题。因此，我尝试在 PRODECURE 之外单独执行 SQL 查询，看看表或查询出了什么问题。

select distinct xxxID from MXS.Y where YID=yyyid;

但是，我没有发现上述查询有任何问题。查询运行良好，我得到了正确的结果。

所以，我不知道为什么程序在执行时出现问题。有人可以帮忙吗？

原文

I'm having problems while executing a stored procedure in Oracle.

Below is the stored procedure:

CREATE OR REPLACE Procedure xxxlist
    (yyyid in NUMBER)    
IS
    xid number(6);
    cursor c1 is select distinct xxxID from MXS.Y where YID=yyyid;
BEGIN
IF NOT c1%ISOPEN THEN 
    OPEN c1;
END IF;
LOOP
    FETCH c1 into xid;
    dbms_output.put_line(TO_CHAR(xid));
    EXIT WHEN c1%NOTFOUND; 
END LOOP;
CLOSE c1;   
END;

When I create the stored procedure, I get the following error:

Warning: Procedure created with compilation errors.

I execute the below command to provide me more details on the above abstract error message.

SHO ERR;

I get the below details in response to the above command:

Errors for PROCEDURE XXXLIST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/18     PL/SQL: SQL Statement ignored
5/52     PL/SQL: ORA-00942: table or view does not exist

Now, I understand that there is a problem with the table name or sql query itself. So, I try to execute the SQL query alone, seperately, outside the PRODECURE, to see what is wring with the table or query.

select distinct xxxID from MXS.Y where YID=yyyid;

But, I do not find any issues with the above query. The query runs fine and I get proper result.

So, I do not know why the procedure is having problems while executing. Can anyone help?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花开柳相依 2024-12-11 16:48:59

可能的解释是您正在运行的帐户已通过角色被授予访问该表的权限。在 Oracle 中，通过角色授予的访问权限会影响独立的 SQL 语句，但不会影响 PL/SQL 中嵌入的 SQL 语句。

如果是这种情况，表的所有者（或 DBA）必须直接向您的帐户授予对表的选择访问权限：

GRANT SELECT ON mxs.y TO <account>;

A possible explanation is that the account you are running in has been granted access to the table through a role. In Oracle, access granted through roles affects standalone SQL statements but not SQL statements embedded in PL/SQL.

If this is the case, the owner of the table (or a DBA) would have to grant select access on the table directly to your account: