Re: How to LOCK TABLE/READ ONLY table in own schema after data insertion

On 6 Jun 2006 03:03:31 -0700, sanjeev_atvankar_at_hotmail.com wrote:

>Hello Guys
>
>I am developing one application in which there are two tables as
>follows
>1)call_by_call_details(this table stores each call by call details) and
>tables columns are as follows
>(call_id,caller_name,call_type,call_Date)
>
>2)call_by_call_hist (this table stores monthwise history from
>call_by_call_details) and columns are as follows
>(Rep_Month,call_type)
>
>I have written the query that create history and put into
>call_by_call_hist on Every month end with the help of procedure and Job
>scheduling.
>
>But after inserting the data into call_by_call_hist i want to lock this
>table till next insert,that means no one can change the data from hist
>table i.e.In this hist table only insert and fire a query is possible.
>
>Could anyone give me the solution(possibly with explaination).
>
>Thanks in advance
>
>Sanjeev

You would need to two things
1 set up a table with 'closed out' periods 2 create a view on top on the hist table, which will join the hist table and the table you just created, allowing to view only data for 'open' periods. The view has to be created 'With check option' 3 The inserting program (or no one) doesn't get insert privilege on the table, it only gets insert privilege on the view 4 As soon as the insert program gets out of boundaries, the insert will fail.

--
Sybrand Bakker, Senior Oracle DBA