amahanit

 Profile Answers by amahanit Questions by amahanit

• Oct 13th, 2007

 

to use a user defined function in SQL statement purity level is established using pragma restrict_refrences. puriy levels are WNDS,WNPS,RNDS,RNPS.

malik.aman

 Profile Answers by malik.aman 

• Dec 4th, 2007

 

To be callable from SQL statements, a stored function (and any subprograms called by that function) must obey certain "purity" rules, which are meant to control side effects:

• When called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.

• When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.

• When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit.

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed).

To check for violations of the rules, you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read or write database tables or package variables. For example, the following pragma asserts that packaged function credit_ok writes no database state (WNDS) and reads no package state (RNPS):

CREATE PACKAGE loans AS    FUNCTION credit_ok RETURN BOOLEAN;  
  PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS); END loans; 
            

            

joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 10th, 2007

 

The  purity level defines  what structure the  function reads  or  modifies.
the  types  of  purity level:

1)wnds--write  no database  stage  I.e  the  function  does not modify any database  or tables.
2)rnds--reads  no database state.  i.e function does not  read  any database  tables.
3)wnps--write  no package  state  i.efunction does not modify any packaged variables.
4)rnps-read  no package  state i.e function does not  read  any  package variables.