Home » SQL & PL/SQL » SQL & PL/SQL » purity level of finction
purity level of finction [message #223007] Wed, 07 March 2007 03:12 Go to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
can anyone explain me cleraly purity level of functions and why it is needed and auth_id clause
Re: purity level of finction [message #223030 is a reply to message #223007] Wed, 07 March 2007 04:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Link (it may require a free OTN subscription).

The purity level of a stored procedure tells whether the function will read/write records on database records and package variables.

MHE

Re: purity level of finction [message #223044 is a reply to message #223007] Wed, 07 March 2007 05:51 Go to previous messageGo to next message
rtjk
Messages: 33
Registered: February 2005
Member
Hi

For the Auth_id clause let me try to explain ,

we have two terms
definer -- who is owner of function and procedure
invoker -- not a owner but he have privilege to execute.

Each and every procedure or function will execute based on definer rights. Say for example we have 3 users(u1,u2,u3) and they are having the same table name(Table emp) in their own schemas.
you have written a procedure p1 in U1 user which will insert 1 record in to the table emp.User U1 has given the execute privilege to the users u2, u3.Now if users u2 and u3 execute the procedure the records will get added in u1 schema's table not in u2 and u3 schema's table.

So if we want that even the owner of the procedure is u1, but when executed it should insert record in to u2 and u3 , then while writing procedure or function , we have to specify the key word as auth_id current_user .by default auth_id is definer.

CREATE PROCEDURE query_employee
(p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE,
p_comm OUT
employees.commission_pct%TYPE)
AUTHID CURRENT_USER
IS
BEGIN
SELECT last_name, salary,
commission_pct
INTO p_name, p_salary, p_comm
FROM employees
WHERE employee_id=p_id;
END query_employee;





Re: purity level of finction [message #223095 is a reply to message #223044] Wed, 07 March 2007 09:31 Go to previous message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Thanx for your explanation
Previous Topic: ORA-00904 Error in Table Function
Next Topic: E-mail through Pl/SQL
Goto Forum:
  


Current Time: Wed Dec 07 16:34:13 CST 2016

Total time taken to generate the page: 0.09801 seconds