how to lock multiple row in Oracle Db?

Hi friends ,

my code like this

$sql="SELECT mid,substr(ROWID,0,100) as nROWID,MEMBER_ID, NAME, SURNAME,
E_MAIL, SUBJECT, MAIL_BODY, to_char(DATE_CREATED,'DD.MM.YYYY HH24:MI:SS') as
DATE_CREATED, RETRY, PRIORITY, E_MAIL_FROM, RECEIVER_MEMBER_ID,
RECEIVER_NAME, RECEIVER_SURNAME, MAIL_TYPE, ATTACHMENT1, ATTACHMENT2,
EMBEDDING1, EMBEDDING2 FROM MEMBERS_TO_MAIL_SEND_HTML where flag=0 and
rownum<2000";

putenv("NLS_LANG=TURKISH_TURKEY.WE8ISO8859P9");
$baglanti = ocilogon(USERNAME,PASSWORD,DATABASE);

$statement = ociparse ($baglanti, $sql);
ociexecute ($statement);
$i=0;


while (ocifetchinto ($statement,$row, OCI_ASSOC)) {
........
........


I want to lock this 2000 rows, for this I make query like below:

$sql=" .............. FROM MEMBERS_TO_MAIL_SEND_HTML where flag=0 and
rownum<2000 FOR UPDATE";

this time, it is updateting only one row.

I tried to use "LOCK TABLE table IN ROW SHARE MODE;LOCK TABLE table IN ROW
SHARE MODE;"; but I don't know how to use in php.


How can I lock all rows, any advice ?

Thanks a lot
rasim

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: how to lock multiple row in Oracle Db?

Rasim ÞEN wrote:
> Hi friends ,
>
> my code like this
>
> $sql="SELECT mid,substr(ROWID,0,100) as nROWID,MEMBER_ID, NAME, SURNAME,
> E_MAIL, SUBJECT, MAIL_BODY, to_char(DATE_CREATED,'DD.MM.YYYY HH24:MI:SS') as
> DATE_CREATED, RETRY, PRIORITY, E_MAIL_FROM, RECEIVER_MEMBER_ID,
> RECEIVER_NAME, RECEIVER_SURNAME, MAIL_TYPE, ATTACHMENT1, ATTACHMENT2,
> EMBEDDING1, EMBEDDING2 FROM MEMBERS_TO_MAIL_SEND_HTML where flag=0 and
> rownum<2000";
>
> putenv("NLS_LANG=TURKISH_TURKEY.WE8ISO8859P9");
> $baglanti = ocilogon(USERNAME,PASSWORD,DATABASE);
>
> $statement = ociparse ($baglanti, $sql);
> ociexecute ($statement);
> $i=0;
>
>
> while (ocifetchinto ($statement,$row, OCI_ASSOC)) {
> ........
> ........
>
>
> I want to lock this 2000 rows, for this I make query like below:
>
> $sql=" .............. FROM MEMBERS_TO_MAIL_SEND_HTML where flag=0 and
> rownum<2000 FOR UPDATE";
>
> this time, it is updateting only one row.
>
> I tried to use "LOCK TABLE table IN ROW SHARE MODE;LOCK TABLE table IN ROW
> SHARE MODE;"; but I don't know how to use in php.
>
>
> How can I lock all rows, any advice ?
>
> Thanks a lot
> rasim
>

Why do you think they are not being locked?
Where and when is the UPDATE statement being executed?

Tom Kyte may offer help on locking. For example, see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DIS PLAYID:927629362932

Finally, I suggest using the refactored OCI8 driver. This
is currently available from http://pecl.php.net/package/oci8 and
http://pecl4win.php.net/ext.php/php_oci8.dll

Chris

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php