Oracle - error running stored procedure under proxy-CodePudding

I am using the Oracle XE database running in a Docker container. I have two stored procedures which I want to run under a proxy. One works okay, the other doesn't. Both scripts run fine under the username which created them.

Here are the two stored procedures (the former works, the latter doesn't)

-- @"/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;
/

Here's the output from 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;

I can see no different in the privileges accorded each procedure.

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>

CodePudding user response：

Solved. The proxy user HR was missing a SYNONYM for the employees table. Synonyms facilitate writing queries which don't need to qualify table names with a schema name. So, it's either create a synonym for the table or qualify its name with its schema. This is necessary to avoid ambiguity when referencing objects. Having slept on it, I'm torn between qualifying names or using synonyms: the latter is ambiguous but flexible, the former rigid but unambiguous. But, anyway, problem solved.

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

Many thanks to Alex Poole.