Danny jumped to M$

Now M$ got three of best borland's chief architect .

A interest query

we have a table items which miss some price for some weeks

Week	Item	Price
1	1	8
2	1	null
3	1	null
4	1	7
1	2	6
2	2	3
3	2	null
1	3	2
2	3	null
3	3	4
4	3	2
5	3	null

we want to have a query to replace the missed price with last available price

SELECT i_item,
i_week,
i_price,
SUM(i_price) over(PARTITION BY new_price) new_price2
FROM (

SELECT i.item AS i_item,
i.week AS i_week,
i.price AS i_price,
SUM(i.price) over(ORDER BY i.item, i.week) new_price
FROM items i)
ORDER BY 1,
2

adopts dbms_rls to restrict the table access

oracle provides a powerful feature that allows developer to audit the table access with user_customized function, here is the sample

create or replace function GEORGEY_SECURITY_FUNCTION return boolean is
Result boolean;
begin
raise_application_error(-20101, 'illegal access');

return(Result);
end GEORGEY_SECURITY_FUNCTION;

BEGIN

dbms_rls.add_policy(object_schema => 'GEORGEY',
object_name => 'PEOPLE',
policy_name => 'GEORGEY_POLICY',
function_schema => 'GEORGEY',
policy_function => 'GEORGEY_SECURITY_FUNCTION',
statement_types => 'SELECT,UPDATE,INSERT,DELETE',
update_check => TRUE,
enable => TRUE,
static_policy => FALSE);

END;