---
title: 存储过程跨用户调用报错-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于 存储过程跨用户调用报错相关的常见问题和使用技巧，帮助您快速解决 存储过程跨用户调用报错的难题。
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# 存储过程跨用户调用报错

更新时间：2023-12-07 03:16

适用版本： V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x 内容类型：Troubleshoot  

## 适用版本

OceanBase 数据库所有版本

## 问题详情

存储过程跨用户调用（用户 A 向用户 B 某张表插入、查询数据报错）。

```shell
obclient [TEST2]> INSERT INTO TEST1.STD VALUES (1,90);  ##用户test2向用户test1 std表中插入、查询数据是正常的
Query OK, 1 row affected (0.020 sec)

```

```shell
obclient [TEST2]> SELECT * FROM TEST1.STD;
+------+------+
| S_ID | S_GD |
+------+------+
|    1 |   90 |
+------+------+
1 row in set (0.015 sec)

```

现在创建存储过程（用户 test2 向用户 test1 std 表中插入、查询数据）。

```shell
obclient [TEST2]> DELIMITER //
obclient [TEST2]> create or replace PROCEDURE PRO1 (S1 IN INT ,C1 IN INT)
    -> AS
    -> BEGIN
    -> INSERT INTO TEST1.STD(s_id,s_gd) VALUES (S1,C1);
    -> END;
    -> //
Query OK, 0 rows affected (0.094 sec)

```

```shell
obclient [TEST2]> CREATE OR REPLACE PROCEDURE PRO2 AS
END;
//
    -> BEGIN
    ->   FOR rec IN (SELECT * FROM TEST1.STD) LOOP
    ->     DBMS_OUTPUT.PUT_LINE('s_id: ' || rec.s_id);
    ->     DBMS_OUTPUT.PUT_LINE('s_gd: ' || rec.s_gd);
    ->     DBMS_OUTPUT.PUT_LINE('---------------------------');
    ->   END LOOP;
    -> END;
    -> //
Query OK, 0 rows affected (0.032 sec)

```

可以看到调用存储过程报错，报错表不存在。

```shell
obclient [TEST2]> call pro1(2,91);
ORA-00942: table or view does not exist
at TEST2.PRO1 , line : 4, col : 1
obclient [TEST2]> call pro2();
ORA-00942: table or view does not exist
at TEST2.PRO2 , line : 3, col : 3
obclient [TEST2]>

```

## 解决方案

通过存储过程跨用户调用（用户 A select，insert 用户 B 的某张表）需要单独对用户 B 的某张表赋权，需要什么权限给哪个表加权限。

现在切换到 SYS 用户下给 TEST2 赋予 test1.std 的权限。

```shell
[root@ob120 ~]# obclient -h127.0.0.1 -usys@oracle -P2881 -p xxxx
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3222359508
Server version: OceanBase 3.2.4.5 (r105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb) (Built Aug 15 2023 13:49:25)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

```

```shell
obclient [SYS]> grant select,insert on test1.std to test2;
Query OK, 0 rows affected (0.044 sec)

```

```shell
obclient [SYS]> exit
Bye
[root@ob120 ~]# obclient -h127.0.0.1 -uTEST2@oracle -P2881 -p xxx
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3222359759
Server version: OceanBase 3.2.4.5 (r105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb) (Built Aug 15 2023 13:49:25)

```

```shell
obclient [TEST2]> call pro1(2,91);    ##已经不报错了且数据已经插入进去了
Query OK, 0 rows affected (0.010 sec)

obclient [TEST2]> call pro2();
Query OK, 0 rows affected (0.440 sec)

```

```shell
obclient [TEST2]> select * from test1.std;
+------+------+
| S_ID | S_GD |
+------+------+
|    1 |   90 |
|    2 |   91 |
+------+------+
2 rows in set (0.007 sec)

```

上一篇

[OceanBase 数据库中创建存储过程时出现不支持 AS LANGUAGE JAVA NAME 声明的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002398332)

下一篇

[OceanBase 数据库 PL 包含 PIVOT、静态 SQL 时，执行报 -4016, Internal error 的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002397313) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线