| privs for creating functions using tables from another schema [message #203612] |
Wed, 15 November 2006 16:55  |
swedstar
Messages: 7
Registered: November 2006
|
Junior Member |
|
|
Hi all,
I'm having problems creating a function in the following scenario.
I have a user that has 'select' system privileges on certain tables in another users schema. I've created local synonyms for these tables and I can query them without any problems. However, I've been trying to create a function with a select query using the tables from the other schema but I constantly receive compilation errors where it says that the table/view doesn't exist. There are no problems creating this function in the schema where the tables belong but I can't do it from this user. I believe there is an issue with privileges for this user but I'm not sure what the minimum privs are is to make this work.
Many thanks
Erik
|
|
|
|
| Re: privs for creating functions using tables from another schema [message #203639 is a reply to message #203612] |
Wed, 15 November 2006 22:31  |
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
|
Senior Member |
|
|
hi
I've been trying to create a function with a select query using the tables from the other schema but I constantly receive compilation errors where it says that the table/view doesn't exist.
you have two option.
1. Use "schema" name before table name in ur function.
2. Or use synonym in function.
sql>conn scott/tiger
sql>grant select on emp to hr;
sql>conn hr/hr
sql>select * from [B]scott.emp[/B];
sql>create synonyn emp_scott for scott.emp;
sql>select * from emp_scott;
sql>create or replace function "name"
...
...
here use 1. synonym or table name with schema.
like [b] emp_scott or scott.emp [/b]
end "name"
hope this helps.
Mohammad Taj.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
