mahjag
asked on
table select from oracle procedure
Hi
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
begin
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
exception
when others then
insert into test_table (exist_flag) values ('N')
end;
/
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
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
begin
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
exception
when others then
insert into test_table (exist_flag) values ('N')
end;
/
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes typo on test_table -
ASKER
why need explicit grants vs role - can you explain more - I think that will solve the issue
ASKER
I logged into master schema and did select * form user_tab_privs where table_name = 'ORDER_MASTER' I got bunch of roles - how do I go into each role and find out what my user schema was granted to -
Roles don't help you here.
The way procedures execute, the master schema needs to: grant select on order_master to proc_owner;
It's just how Oracle works.
To see the roles and what they have I believe it is role_tab_privs and role_sys_privs.
The way procedures execute, the master schema needs to: grant select on order_master to proc_owner;
It's just how Oracle works.
To see the roles and what they have I believe it is role_tab_privs and role_sys_privs.
ASKER
will it work with roles if I turn above procedure to dynamic sql?
I can create variable sqlstmt := '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';
execute immediate 'sql_stmt';
I can create variable sqlstmt := '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';
execute immediate 'sql_stmt';
ASKER
matter of fact- the procedure compiled with the above changes - let me execute
ASKER
create or replace procedure test_insert as
sql_stmt varchar2(100);
begin
sql_stmt := 'insert into test_table (order_id,insert_flag)
(
select order_id,''Y'' from main_schema.order_master where date = ''01-JAN-2011''
and order_id = ''1-ABC''
)';
execute immediate sql_stmt;
exception
when others then
insert into test_table(insert_flag) values ('N');
end;
above procedure came back with values N in test_table - but there is a row in order master?
sql_stmt varchar2(100);
begin
sql_stmt := 'insert into test_table (order_id,insert_flag)
(
select order_id,''Y'' from main_schema.order_master where date = ''01-JAN-2011''
and order_id = ''1-ABC''
)';
execute immediate sql_stmt;
exception
when others then
insert into test_table(insert_flag) values ('N');
end;
above procedure came back with values N in test_table - but there is a row in order master?
>>will it work with roles if I turn above procedure to dynamic sql?
NO. Procedures need explicit grants on objects to work properly.
>>above procedure came back with values N in test_table - but there is a row in order master?
If you got an 'N' in test_table then there was an exception in the statement. The error is likely a permission issue because you still haven;t granted insert privs.
Do not use dynamic SQL unless you absolutely have to.
NO. Procedures need explicit grants on objects to work properly.
>>above procedure came back with values N in test_table - but there is a row in order master?
If you got an 'N' in test_table then there was an exception in the statement. The error is likely a permission issue because you still haven;t granted insert privs.
Do not use dynamic SQL unless you absolutely have to.
ASKER
Hi slightwv
Can you please explain why not dynamic sql? Also I am trying to see if we can stick to roles as there are lots of tables that needs grants.
Can you please explain why not dynamic sql? Also I am trying to see if we can stick to roles as there are lots of tables that needs grants.
>> Also I am trying to see if we can stick to roles
You can try all you want but it's NOT going to work.
This is talked about buried in the docs somewhere. I can't find it right now but heres a decent article on it:
http://www.adp-gmbh.ch/ora/err/ora_00942.html
Creating a procedure
Pay attention to the last note for a possible work-around. I've never tried this but it sounds like it might be what you need:
However, if the procedure is created with authid current_user, the granted roles will be enabled when the procedure is called.
>>Can you please explain why not dynamic sql?
There are a few reasons but here are the main ones:
You lose object dependency (nothing knows this procedure is dependent on the tables used in dynamic sql).
You have to hard parse the statement on every execution.
You can try all you want but it's NOT going to work.
This is talked about buried in the docs somewhere. I can't find it right now but heres a decent article on it:
http://www.adp-gmbh.ch/ora/err/ora_00942.html
Creating a procedure
Pay attention to the last note for a possible work-around. I've never tried this but it sounds like it might be what you need:
However, if the procedure is created with authid current_user, the granted roles will be enabled when the procedure is called.
>>Can you please explain why not dynamic sql?
There are a few reasons but here are the main ones:
You lose object dependency (nothing knows this procedure is dependent on the tables used in dynamic sql).
You have to hard parse the statement on every execution.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may want to check out this link -
http://www.oracleplsqlprogramming.com/oppnews_200509.html
http://www.oracleplsqlprogramming.com/oppnews_200509.html
I still suggest you get rid of EXECUTE IMMEDIATE here.
slightwv,
I agree with you that the execute immediate statement is not needed. I was just copying and pasting and adding the authid current_user clause to show how a procedure can be modified to allow role privileges.
I agree with you that the execute immediate statement is not needed. I was just copying and pasting and adding the authid current_user clause to show how a procedure can be modified to allow role privileges.
ASKER
Hi awking00
i tried your attached code which I beleive the only change is to add authid current user and the results were same - I did not get a Y with order id that I wanted..
i tried your attached code which I beleive the only change is to add authid current user and the results were same - I did not get a Y with order id that I wanted..
delete the exception handler in your code and let it error out to see what is causing the insert to not happen.
Does the table order_master actually have a field called date?
I would also use the to_date function to make the comparison.
Try the attached.
procedure.txt
I would also use the to_date function to make the comparison.
Try the attached.
procedure.txt
ASKER
Thanks slightwv and awking00 - after removing the exception I got buffer too small error for sql_stmt declaration which I changed and then it worked - I did not read the links that you guys posted - looks like by doing dynamic statement the roles worked - can we assume this is one way getting around the issue?
>> looks like by doing dynamic statement the roles worked
No. It was the authid declaration.
No. It was the authid declaration.
ASKER
I am still confused as to why oracle works this way - does this mean convert ever sql to dynamic sql?
I would like clarification on "Roles don't help you here.
The way procedures execute, the master schema needs to: grant select on order_master to proc_owner;
It's just how Oracle works.
"
to understand more and comment on this..
I would like clarification on "Roles don't help you here.
The way procedures execute, the master schema needs to: grant select on order_master to proc_owner;
It's just how Oracle works.
"
to understand more and comment on this..
ASKER
typo in my first line - does this mean convert every sql
Did you review 'authid' in the online docs? You should always refer to the docs when you need to know about a specific command.
It sets the current user for everything in the procedure.
It sets the current user for everything in the procedure.
>> does this mean convert ever sql to dynamic sql?
Forget about dynamic sql. The execute immediate does nothing to help you here.
Do not use it. I can't say it any other way.
Forget about dynamic sql. The execute immediate does nothing to help you here.
Do not use it. I can't say it any other way.
>>I did not read the links that you guys posted - looks like by doing dynamic statement the roles worked<<
As slightwv stated, it was the authid command that made it work, not dynamic sql. Read the links and they will explain that better.
As slightwv stated, it was the authid command that made it work, not dynamic sql. Read the links and they will explain that better.
Not even an assist :-(
ASKER
pardon me awking00 it was my oversight - If I have to rectify would love do that..
ASKER
I mean can I split points now?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER