Home » SQL & PL/SQL » SQL & PL/SQL » Created function granted to scott but not working
Created function granted to scott but not working [message #289761] Wed, 26 December 2007 01:18 Go to next message
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 Go to previous messageGo to next message
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 #289770 is a reply to message #289765] Wed, 26 December 2007 01:46 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Executing a function and/or procedure created by another user needs to be qualified:

SELECT table_name, system.count_rows(table_name) from user_tables ;


A (public) synonym could be created to avoid qualifying (actually the qualifying is "hidden" within the synonym)

As system user:
CREATE PUBLIC SYNONYM count_rows FOR system.count_rows ;


HTH

Marc
Re: Created function granted to scott but not working [message #289775 is a reply to message #289770] Wed, 26 December 2007 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
MarcS

Executing a function and/or procedure created by another user needs to be qualified

It seems OP already knows this:
sathyguy

i tried with the below query also....but no use...
select table_name, system.count_rows(table_name) from user_tables;


Regards
Michel
Re: Created function granted to scott but not working [message #289779 is a reply to message #289775] Wed, 26 December 2007 02:20 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You're right Embarassed

But I was misled:

invalid identifier error is ORA-00904

table or view does not exist is ORA-0942
Re: Created function granted to scott but not working [message #289784 is a reply to message #289779] Wed, 26 December 2007 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Anyway, thanks for pointing to this issue and give public synonym workaround. I was a bit fast to reply.

Regards
Michel
Re: Created function granted to scott but not working [message #289786 is a reply to message #289784] Wed, 26 December 2007 02:30 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
No worries.

But this show once again that to help out we really need the exact information Wink

Regards,
Marc
Re: Created function granted to scott but not working [message #289797 is a reply to message #289765] Wed, 26 December 2007 02:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #289803 is a reply to message #289761] Wed, 26 December 2007 03:11 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
The AUTHID CURRENT_USER clause should be between the RETURN-clause and the IS.

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;


Changes also return-type from VARCHAR2 to INTEGER, as it is a number you'll be returning.

Re: Created function granted to scott but not working [message #289807 is a reply to message #289803] Wed, 26 December 2007 03:24 Go to previous message
sathyguy
Messages: 31
Registered: January 2006
Member
Great......its workin Smile))))))))
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.
Previous Topic: Unable to extend temp segment on tablespace
Next Topic: auto number field
Goto Forum:
  


Current Time: Sat Feb 08 16:22:04 CST 2025