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