Oracle -在代理下运行存储过程时出错

jk9hmnmh  于 2022-11-03  发布在  Oracle
关注(0)|答案(1)|浏览(186)

我正在Docker容器中运行Oracle XE数据库。我有两个存储过程,希望在代理下运行。一个可以正常运行,另一个不行。两个脚本在创建它们的用户名下都可以正常运行。
下面是两个存储过程(前者工作,后者不工作)

-- @"/opt/oracle/oradata/Custom Scripts/orders_by_product_category_by_year.sql"
CREATE OR REPLACE PROCEDURE orders_by_product_category_by_year(p_cur OUT sys_refcursor) 
AUTHID CURRENT_USER 
AS 
BEGIN
    OPEN p_cur FOR
        SELECT 
            ROW_NUMBER() OVER (ORDER BY EXTRACT(YEAR FROM orders.order_date) ASC) AS row_num, 
            product_categories.category_name, 
            EXTRACT(YEAR FROM orders.order_date) AS year,
            SUM(order_items.quantity*order_items.unit_price) AS value, 
            COUNT(1) AS count 
        FROM orders 
        LEFT JOIN order_items ON order_items.order_id = orders.order_id 
        LEFT OUTER JOIN products ON products.product_id = order_items.product_id 
        LEFT OUTER JOIN product_categories ON product_categories.category_id = products.category_id 
        GROUP BY product_categories.category_name, EXTRACT(YEAR FROM orders.order_date) 
        ORDER BY year ASC, product_categories.category_name;
END;
/

-- @"/opt/oracle/oradata/Custom Scripts/orders_for_year.sql"
CREATE OR REPLACE PROCEDURE orders_for_year(i_year IN NUMBER, o_cursor OUT SYS_REFCURSOR) 
AUTHID CURRENT_USER 
AS 
BEGIN 
    OPEN o_cursor FOR 
        SELECT 
            ROW_NUMBER() OVER (ORDER BY EXTRACT(YEAR FROM orders.order_date) ASC) AS row_num, 
            orders.order_id, 
            customers.name AS customer_name, 
            CONCAT(CONCAT(employees.first_name, ' '), employees.last_name) AS salesrep_name, 
            orders.order_date, 
            (SELECT SUM(order_items.quantity*order_items.unit_price) FROM order_items WHERE order_items.order_id = orders.order_id) AS value 
        FROM orders 
        LEFT OUTER JOIN customers ON customers.customer_id = orders.customer_id 
        LEFT OUTER JOIN employees ON employees.employee_id = orders.salesman_id 
        WHERE EXTRACT(YEAR FROM orders.order_date) = i_year;
END;
/

下面是PL/SQL的输出。

SQL> SHOW USER;
USER is "HR"
SQL> VAR cursor REFCURSOR;
SQL> EXEC ot.orders_by_product_category_by_year(:cursor);

PL/SQL procedure successfully completed.

SQL> EXEC ot.orders_for_year(2016, :cursor);
BEGIN ot.orders_for_year(2016, :cursor); END;

* 

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "OT.ORDERS_FOR_YEAR", line 5
ORA-06512: at line 1

SQL> SELECT * FROM user_tab_privs;

我看不出给予每一程序的特权有什么不同。

SQL> SELECT * FROM user_tab_privs;

GRANTEE    OWNER      TABLE_NAME                     GRANTOR    PRIVILEGE            GRA HIE COM TYPE       INH
---------- ---------- ------------------------------ ---------- -------------------- --- --- --- ---------- ---
HR         OT         REGIONS                        OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         COUNTRIES                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         LOCATIONS                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         WAREHOUSES                     OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         EMPLOYEES                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         PRODUCT_CATEGORIES             OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         PRODUCTS                       OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         CUSTOMERS                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         CONTACTS                       OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         ORDERS                         OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         ORDER_ITEMS                    OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         INVENTORIES                    OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         ORDERS_BY_PRODUCT_CATEGORY_BY_ OT         EXECUTE              NO  NO  NO  PROCEDURE  NO
                      YEAR

HR         OT         ORDERS_FOR_YEAR                OT         EXECUTE              NO  NO  NO  PROCEDURE  NO
PUBLIC     SYS        HR                             HR         INHERIT PRIVILEGES   NO  NO  NO  USER       NO

15 rows selected.

SQL>
ljsrvy3e

ljsrvy3e1#

解决了。代理用户HR缺少employees表的SYNONYM。同义词便于编写不需要用模式名限定表名的查询。因此,要么为表创建同义词,要么用其模式限定表名。这是避免引用对象时产生歧义所必需的。在考虑了这个问题之后,我在限定名称还是使用同义词之间左右为难:后者模糊但灵活,前者僵硬但明确。2但不管怎样,问题解决了。

OT> CREATE OR REPLACE SYNONYM hr.employees FOR ot.employees;

HR> SELECT COUNT(1) FROM employees;

  COUNT(1)
----------
       107

OT> DROP SYNONYM hr.employees;

HR> SELECT COUNT(1) FROM employees;
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
  • 非常感谢亚历克斯·普尔 *

相关问题