Re: Oracle & HTML

Thanx you very much for details. Thouse ways like handmade. I've thougth Oracle have some special solution as usual, my mistake ;-)

Will it be fixed in WebServer 2.0 ?

Thomas J Kyte wrote:
>
> "Roman L. Podshivalov" <romas_at_online.ru> wrote:
>
> >Jared Hecker wrote:
> >>
> >> The Web Server doesn't do any locking. The package(s) associated with it
> >> probably implement the standard Oracle locking schemes (select for update,
> >> etc.). Should be in the docs...
 

> >Look: in multiuser env you select data into html form wait 10 mins and want to update it...
> >What can happend ???
 

> >PS: separate packages make select & update as i undestand the idea of Web Server...
 

> >Oracle goverment where are you ???????????
>
> Correct, WebServer does not maintain locks across URL accesses. The
> htt-protocol does not support such a concept (yet). There is no such thing as a
> 'state'. HTTP is a completely stateless protocol.
>
> So, how do people handle this? These are the most common ways I've seen it
> dealt with:
>
> - collect information from the web and disseminate it. Don't update existing
> information. Avoid the problem alltogether. (only allow INSERTS to collect and
> SELECTS to disseminate, and perhaps DELETES to get rid of).
>
> - When you select the information out of the database, not only would you put it
> in the text fields for updating but you would put it in hidden fields as well.
> For example:
>
> for x in ( select rowid, a.* from emp a ) loop
> hpt.formHidden( 'the_Rowid', x.rowid );
>
> htp.p( 'Enter New Emp Name: ' );
> htp.formHidden( 'ename_old', x.ename );
> htp.formText( 'ename_new', cvalue => x.ename );
> ....
> end loop;
>
> And then your update routine will get the rowid, old values and new values. The
> update statement would look like:
>
> update emp
> set ename = new_ename,
> .....
> where rowid = THE_ROWID
> and ename = old_ename and .......
>
> if ( sql%rowcount = 0 ) then
> htp.bold( 'Your data has been changed, please requery and try again' );
> else
> htp.p( 'Success....' );
> end if;
>
> - Do a variation on the above. Instead of having to send old and new values
> back and forth and having a complex update statement, create a 'timestamp' field
> on the table. For example:
>
> create sequence timestamp_seq;
>
> alter table scott.emp add timestamp number;
>
> create trigger emp_ts
> after insert or update on emp
> for each row
> begin
> select timestamp_seq.nextval into :new.timestamp from dual;
> end;
> /
>
> Now whenever a row is created or modified, a unique number will be attached to
> it. Your pl/sql code would look like:
>
> for x in ( select rowid, a.* from emp loop )
> htp.formHidden( 'the_Rowid', x.rowid );
> htp.formHidden( 'the_Timestamp', x.timestamp );
>
> htp.p( 'Enter New Emp Name: ' );
> htp.formText( 'ename_new', cvalue => x.ename );
> ....
> end loop;
>
> And then your update routine will get the rowid, the old timestamp and new
> values. The update statement would look like:
>
> update emp
> set ename = new_ename,
> .....
> where rowid = THE_ROWID
> and timestamp = THE_TIMESTAMP
>
> if ( sql%rowcount = 0 ) then
> htp.bold( 'Your data has been changed, please requery and try again' );
> else
> htp.p( 'Success....' );
> end if;
>
> - Or if you cannot, do not, want to modify the table structure as above but like
> the concept of not having to send old and new values around, you could use a
> checksum to determine of the row has changed since you queried it. For example
>
> -- Internet (1's complement) checksum:
> create or replace function checksum( p_buff in varchar2 ) return number
> is
> l_sum number default 0;
> l_n number;
> begin
> for i in 1 .. trunc(length(p_buff)/2) loop
> l_n := ascii(substr(p_buff, 1+(i-1)*2, 1))*256 +
> ascii(substr(p_buff, 2+(i-1)*2, 1));
> l_sum := mod(l_sum+l_n,4294967296);
> end loop;
> while ( l_sum > 65536 ) loop
> l_sum := bitand( l_sum, 65535 ) + trunc(l_sum/65536);
> end loop;
> return l_sum;
> end checksum;
> /
>
> And then you would code:
>
> for x in ( select checksum(ename||dept||loc||....) checksum, rowid, a.*
> from emp a ) loop
> htp.formHidden( 'the_Rowid', x.rowid );
> htp.formHidden( 'the_checksum', x.checksum );
>
> htp.p( 'Enter New Emp Name: ' );
> htp.formText( 'ename_new', cvalue => x.ename );
> ....
> end loop;
>
> And then your update routine will get the rowid, the old timestamp and new
> values. The update statement would look like:
>
> update emp
> set ename = new_ename,
> .....
> where rowid = THE_ROWID
> and checksum(ename||dept||loc||...) = THE_CHECKSUM;
>
> if ( sql%rowcount = 0 ) then
> htp.bold( 'Your data has been changed, please requery and try again' );
> else
> htp.p( 'Success....' );
> end if;
>
> >>
> >> hth -
> >>
> >> jhy
> >>
> >> Roman L. Podshivalov (romas_at_online.ru) wrote:
> >> : Can any1 explain how locks working in Oracle Web server ????
> >> : Or how Web server locks rows...
> >>
> >--
 

> >Roman L. Podshivalov Sovam Teleport
> ><mailto:romas_at_online.ru> (+7-501) 258-4170
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
>
> --------------------------------------------------------
> opinions and statements are mine and do not necessarily
> reflect the opinions of Oracle Corporation.
 

-- 
Roman L. Podshivalov                     Sovam Teleport 
<mailto:romas_at_online.ru>                 (+7-501) 258-4170