Could anyone shead some light on this problem that I'm faced with.

I have a stored procedure that has 2 parameters. The first is the
primary key for the table that is to be updated. The second is an
integer. This integer is actually a bitwise status flag.(1=locked,
2=reviewed, 4=invalid...)

The issue that I am facing is that I need to change the value for a
large number of fields within this particular table, but only modify
the relevant bits.
For example record1 has a value of 4 in it's statusA field which means
it is marked as invalid but not locked and not reviewed.
Record1 has a value of 5 in it's statusB field which means it is
marked as invalid and locked but not reviewed.
The stored procedure then passes the primary key to select record1 and
an integer value of 1, thereby indicating that the "locked" bit of the
statusA and statusB fields should be set.

How can I do this?

update your_table
set StatusA = StatusA + case bitand(StatusA, 1) when 0 then 1 else 0 end
set StatusB = StatusB + case bitand(StatusB, 1) when 0 then 1 else 0 end
where id = your_id


Add 1 to each bit field if it is not currently locked .... this seems like
the solution for what you described?

Kurt


--


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"Adrian Scott-Day" <adrian.scott.day (AT) gmail (DOT) com> wrote

Adrian Scott-Day wrote:

I am completely unclear as to what it is you are doing and why you have
any problem at all.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Ok, sorry if I was unclear but Oracle programming is new to me.

The situation is this. I have a stored procedure which gets called
repeatedly from a cursor. This stored procedure has 2 parameters. One
being an id and the other being an integer. In the stored procedure I
need to update about 50 columns for the record with the id that matches
parameter 1.

Because there are so many columns that have to be updated, I was hoping
that I wouldn't have to do a select statement to retrieve the existing
values of those columns first.

Can you see my problem now?

Adrian Scott-Day wrote:
check out the bitand function
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald (AT) yahoo (DOT) com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------

adrian.scott.day (AT) gmail (DOT) com wrote:
With SQL, you NEVER, EVER have to select the columns
that you want to update: the UPDATE statement does not have
to read the columns before updating them. That is one of the
biggest differences between traditional file processing and
SQL.

If it is a bit-flip you want, you can use the BITAND
function like Connor suggested.

If you really want to do raw binary, you can use the
UTL_RAW package which gives you access to BITOR, BITAND
and BITXOR as well as a host of others. But you may have
to do some manipulations to make sure the type of the
parameters and results is consistent with RAW.