Created function granted to scott but not working [message #289761] |
Wed, 26 December 2007 01:18  |
sathyguy
Messages: 31 Registered: January 2006
|
Member |
|
|
Friends,
Server: RHEL AS 3.0
DB:9iR2
As a system user....
create or replace function count_rows(table_name varchar2)
return varchar2 is
i number;
begin
execute immediate 'select count(*) c from '||table_name into i;
return i;
end;
/
grant execute on count_rows to scott;
as a scott user....i tried to execute the system created function count_rows
select table_name,count_rows(table_name) from user_tables;
then i received a invalid identifier error.
i tried with the below query also....but no use...
select table_name, system.count_rows(table_name) from user_tables;
but when i run the function as a system user its executing without any problem/error.
thanks
|
|
|
Re: Created function granted to scott but not working [message #289765 is a reply to message #289761] |
Wed, 26 December 2007 01:29   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Privilege granted through role are not active in procedure.
SYSTEM has only DBA role, so it does not have any privilege on SCOTT's table.
Use:
create or replace function count_rows(table_name varchar2)
AUTHID CURRENT_USER
...
Then this will work.
Next time:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Copy and paste the EXECUTION with line numbers and error you get.
Regards
Michel
[Updated on: Wed, 26 December 2007 01:29] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Created function granted to scott but not working [message #289797 is a reply to message #289765] |
Wed, 26 December 2007 02:55   |
sathyguy
Messages: 31 Registered: January 2006
|
Member |
|
|
thanks for your reply...but this is not working....actually im working in another pc which is not having internet connection.
create or replace function count_rows(table_name varchar2)
AUTHID CURRENT_USER
return varchar2 is
i number;
begin
execute immediate 'select count(*) c from '||table_name into i;
return i;
end;
the error which is received is....
PLS-00103: Encountered the symbol "AUTHID" when expecting one of the following:
return
so, where im making mistake?
thanks
|
|
|
Re: Created function granted to scott but not working [message #289799 is a reply to message #289770] |
Wed, 26 December 2007 03:02   |
sathyguy
Messages: 31 Registered: January 2006
|
Member |
|
|
Also, i tried as marcs suggested....
i created the public synonym as system user....
CREATE PUBLIC SYNONYM count_rows FOR system.count_rows ;
GRANT EXECUTE ON COUNT_ROWS TO SCOTT;
After that i logged in as scott....
SELECT table_name, system.count_rows(table_name) from user_tables ;
the error is....
SQL> /
select table_name, system.count_rows(table_name) from user_tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYSTEM.COUNT_ROWS", line 5
what im suppose to do?
|
|
|
|
Re: Created function granted to scott but not working [message #289807 is a reply to message #289803] |
Wed, 26 December 2007 03:24  |
sathyguy
Messages: 31 Registered: January 2006
|
Member |
|
|
Great......its workin ))))))))
create or replace function count_rows(table_name varchar2)
return INTEGER AUTHID CURRENT_USER is
i number;
begin
execute immediate 'select count(*) c from '||table_name into i;
return i;
end;
after that i logged as scott....
SELECT table_name, system.count_rows(table_name) from user_tables ;
now its working....
thanks for your help.
|
|
|