Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using all_tab_cols from a procedure

dsmoljanovicOct 2 2007 — edited Oct 2 2007
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 30 2007
Added on Oct 2 2007
9 comments
1,595 views