Locking database for Update

Hello

I am wondering if there is a builtin method by which the selected record can be locked for update?

In my case the user can search for a record thru search page, this takes him to listing page where listing of records matching the search are showed. User can then click any of the records in the listing page to update the record. However the problem is that in the database there are is no column that is unique (something like ID column). So once the user clicks the record on the listing page, this takes him to update page. At update page to perform the query i dont have anything to put in the WHERE clause of the UPDATE query. since there is no unique column.

so I was wondering if there is any way to Lock the record in oracle or hold the record that the user selected and store it? using sql statements or builtin class method?

Thanks

Use with caution please...

Oracle is VERY big on read consistency.

Once you execute a select, you are guaranteed to get all of the resultset rows from the table, EXACTLY as they were at the START of the select. You will not see data updated by another operation that completed while you are in the select.
(ie, you are reading a cursor).

Oracle is not Access.

Look at select ... for update

Thanks a lot!
but im not too sure what that statement actually does? could you please explain it when u get time?

Will that work for my purpose? of kind of holding the record that the user clicked on? From the listings page the user clicks one of the records and this takes the user to the update page where all the fields are populated with information thats stored in the database for the particular field that the user clicked on. so when the user clicks update....how do i know what to put in the WHERE clause of the UPDATE query since there is no unique ID column in the database. Sorry if im being naive to somethint thats obvious from your answer

In form-based PL/SQL select .. for update is what you want to use, pabloj shows you just what you need.

The LOCK statement is meant largely for a SQL script

I know my chickens (as we use to say) but maybe I'm getting too minimalistic (or RTFM prone )
Regards

Sorry i didnt reply to ur post specifically but i did RTFM Maybe you can explain me better how select for update will work.

What I am saying is that my database in which i am doing an update does not have any UNIQUE columns. So when i write the UPDATE QUERY i dont have nethin to write in the where clause.

So according to the manual, for example:
-------BEGIN TRANSACTION-------
select color from cust where
id=500 for update;
----------------------------------
From what i understand after reading those examples is that when we perform select...for update, all it does is makes another session wait to run select query on the same record. right? so how will that help me? lets say on the listing page there are 3 records, AA BB CC. User clicks the first record, AA, this takes him to the update page where all the fields are populated from the info that was in DB. now when the user clicks update after editing the fields on the page....how would i perform the update query when there is no unique column?

The issue is that the select ...for update query also must have a where clause.

i need some way of knowing which record did the user click on from the listings page...? im so lost!

Fine, you'll start a transaction, then use a "select ... where col = 'AA' for update" (thus locking the record the users choose) to populate the formHow could you decide which record present to the user? This tells me that there is something that makes you choose that record (otherwise use rowid ...)Not really, but a table without PK or Unique constraint is badly designed and flawed anyway
In desperate cases you can use the rowid but I'd clean up my data and correct the table structure adding a PK or an unique constraint before going to that extreme.