Using all_tab_cols from a procedure
I have a custom made procedure in my schema that should retrieve columns of a table based on the user that calls the procedure.
I'm using "all_tab_cols" view in a procedure. Here is the query from the USER_A.MY_PROCEDURE :
SELECT LOWER(column_name) column_name,
DECODE (data_type, 'VARCHAR2', 'C', 'CHAR', 'C', 'LONG', 'C', 'NUMBER', 'N', 'DATE', 'D') data_type
FROM all_tab_cols
WHERE table_name = UPPER('my_table')
AND owner = 'USER_B'
ORDER BY column_id;
When i run this query from Toad as USER_A, it works. However when i run it as USER_B from a procedure (exec USER_A.MY_PROCEDURE) it doesn't return any row. I guess there is something with permissions, but i cannot figure out what.
What are the permissions that should be set for this to work?