Solved

table select from oracle procedure

Posted on 2011-03-14
31
550 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:mahjag
  • 14
  • 9
  • 7
31 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 42 total points
ID: 35132988
>>insert into test table (ord

Typo on test_table?

If so, I agree that it is likely a role explicit grant issue.

You need explicit grants on the master table.  Not through a role.

If you want to see them you can look in the system views.

I'm on Mobile and can't look up the exact ones so I know I'll miss one or typo them.

Check dba_tab_privs, dba_role_privs.
0
 

Author Comment

by:mahjag
ID: 35133012
I just checked dba_tab_privs or role_privs I dont have access to dba* tables - I only have access to user_views - I can login to master schema and can I use user views there? will it work to find out what kind of access was given to order master table?
0
 

Author Comment

by:mahjag
ID: 35133015
yes typo on test_table -
0
 

Author Comment

by:mahjag
ID: 35133025
why need explicit grants vs role - can you explain more - I think that will solve the issue
0
 

Author Comment

by:mahjag
ID: 35133035
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 -
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35133140
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.
0
 

Author Comment

by:mahjag
ID: 35137970
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';
0
 

Author Comment

by:mahjag
ID: 35138033
matter of fact- the procedure compiled with the above changes - let me execute
0
 

Author Comment

by:mahjag
ID: 35138062
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35138238
>>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.
0
 

Author Comment

by:mahjag
ID: 35138272
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35138503
>> 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.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 83 total points
ID: 35139086
Try the attached.
procedure.txt
0
 
LVL 31

Expert Comment

by:awking00
ID: 35139103
You may want to check out this link -
http://www.oracleplsqlprogramming.com/oppnews_200509.html
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35139139
I still suggest you get rid of EXECUTE IMMEDIATE here.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 35139473
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.
0
 

Author Comment

by:mahjag
ID: 35140209
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..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35140300
delete the exception handler in your code and let it error out to see what is causing the insert to not happen.
0
 
LVL 31

Expert Comment

by:awking00
ID: 35140851
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
0
 

Author Comment

by:mahjag
ID: 35141075
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35141233
>> looks like by doing dynamic statement the roles worked

No.  It was the authid declaration.
0
 

Author Comment

by:mahjag
ID: 35141853
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..
0
 

Author Comment

by:mahjag
ID: 35141861
typo in my first line - does this mean convert every sql
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35141878
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.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35141905
>> 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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 35147608
>>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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 35155852
Not even an assist :-(
0
 

Author Comment

by:mahjag
ID: 35155912
pardon me awking00 it was my oversight - If I have to rectify would love do that..
0
 

Author Comment

by:mahjag
ID: 35155920
I mean can I split points now?
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 83 total points
ID: 35156227
That's OK, you can catch me next time :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now