table select from oracle procedure
Posted on 2011-03-14
I am seeing wierd things in oracle 11 trying to select data from a table from another schema in the same database..
First I do this
select orders from order_master
Order master is in another schema and not in the schema where I am running the above select statement but neverthless I get result back with 50 rows and thos are the orders in that table.
First I want to find out how I am granted access to this table - meaning via explicit grants or roles. the reason is I am seeing the problem when I use the same query in a procedure
create or replace test_procedure as
insert into test table (order,exist_flag) --exist flag is indication to say Y or N being exist or not
select order,'Y' from order_master where order_id = '1-A' ;--just to get one row
when others then
insert into test_table (exist_flag) values ('N')
when I compile the above procedure in the same schema where I ran the select statement I get compilation error ORA-00942 - table or view does not exist..
This might be a role vs straight select grants to the table from the schema owner I am thinking I want to prove if that is the case - there should be some way to find out how I got select access to that table - if it is by role or grants depending on the answer - I might need a fix to compile that procedure - as second problem