我正在使用在 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>
uj5u.com熱心網友回復：
解決了。代理用戶 HR 缺少員工表的 SYNONYM。同義詞有助于撰寫不需要使用模式名稱限定表名的查詢。因此，它要么為表創建同義詞，要么使用其模式限定其名稱。這對于在參考物件時避免歧義是必要的。睡在上面后，我在限定名稱或使用同義詞之間糾結：后者模棱兩可但靈活，前者僵硬但明確。但是，無論如何，問題解決了。
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
非常感謝亞歷克斯普爾。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/522716.html