well, it is only according to you that an update of zero rows is a "failure".

updating zero rows to me is total success, just means there wasn't anything to update!

Seems strange that you would update three rows and care that you updated three rows having never read them out??? if you read them out (with select for update), you would have no such worries and would not suffer from lost updates (as you stand a chance of now -- you are basically "shooting at deer in the dark and hoping you hit some" apparently).


begin
update t1
t1_updated := sql%rowcount > 0;
update t2
t2_updated := sql%rowcount > 0;
update t3
t3_updated := sql%rowcount > 0;
if ( not t1_updated or not t2_updated or not t3_updated )
then
raise failure;
end if;
end;

is all you need. as long as the invoker of this does not catch the error and "hide it", it'll rollback all by itself (if you have my book expert one on one oracle -- i go over transactions and how they work in some detail)

we use optimistic concurrency controls.

search this site for those words

or if you have my book expert one on one oracle -- i go over that in some detail in there in the chapter on transactions

this does not make sense:

..
Now because I check in my code not to insert if values are null, I do not want
to rollback too since it is not a fail on an insert too.
.....


I create API's, these API's take a set of inputs, do whatever they need to perform a consistent change of the database. The caller of the API then decides "commit or rollback" (never commit in the API, it'll prevent reuse of your API in a larger transaction). A page is a transaction to me, each and ever submit is a single transaction.

1) huh? just functions like:


function save_question(
p_email in varchar2,
p_fname in varchar2,
p_lname in varchar2,
p_location in varchar2,
p_job_title in varchar2,
p_org in varchar2,
p_category in varchar2,
p_version in varchar2,
p_years in varchar2,
p_subject in varchar2,
p_notify in varchar2,
p_question in varchar2 )
return number;


procedure admin_update_question(
p_id in varchar2,
p_subject in varchar2,
p_answer in varchar2,
p_status in varchar2 );

and so on. a simple stored procedure that takes as input everything it needs to perform a multi-statement transaction.... a typical "API"


2) it is my 100% commited belief that transaction control belongs at the top level, at the caller. they and only they should control commit/rollback. I do not believe at the low level you would ever consider catching a when others and rolling back. You would let the exception propagate up to the top layer. If NO ONE catches it -- the statement (all work performed by that statement, which is your plsql call in fact) is automagically rolled back. If someone at the top does catch it and "hides" it, they would have to rollback themselves if that is the right thing to do.

but again, i don't program that way myself. If you have expert one on one Oracle, i wrote about this.

3) that makes no sense to me. sql%rowcount is the count of rows inserted. if there were an error, you'd never get to that point, an exception would be raised.

if you have an insert ... values - sql%rowcount will be 1. if you have an insert as select, sql%rowcount will be 0..anything -- 0 is not an error, 1 is not an error, 1000 is not an error. sql%rowcount isn't really for error checking.

i didn't think otherwise. I had no thoughts on your approach. You asked simply:

<quote>
How do you usually implement transactional inserts/updates using web pages
</quote>

and I responded "API"




PROCEDURE SAVE_REQUEST (
button IN VARCHAR2 DEFAULT NULL,
i_applicant_id IN VARCHAR2 DEFAULT NULL,
.....
i_user_id IN VARCHAR2 DEFAULT NULL )
IS
my_variables
BEGIN
SELECT request_seq.nextval INTO v_request_id FROM dual;

INSERT into REQUEST (Request_ID, applicant_id user_id, creation_date )
VALUES (v_request_id, i_applicant_id, i_user_id, sysdate );
Never select a sequence from dual only to turn around and send it right back! simply:

insert into t ... values( s.nextval)

and if you need the sequence value, use the RETURNING clause of the insert

IF (SQL%rowcount = 0) THEN
raise record_not_saved;
END IF;

as stated, that condition will *never* happen. if the insert failed, that code would never execute

FOR i IN 1..i_material_id.count
LOOP
IF (i_material_id(i) is not null)
THEN
INSERT into requested_item (request_id, item_number, material_id,
quantity_requested, user_id, creation_date)
VALUES (v_request_id, v_item_number, i_material_id(i),
i_quantity_requested(i), i_user_id, sysdate );
v_item_number := v_item_number+1;
END IF;
END LOOP;

as stated, this code is not worthwhile...
IF (SQL%rowcount = 0) THEN
raise record_not_saved;
END IF;

I firmly believe transactional control belongs elsewhere. what if someone needs to call "save_record" and
"something_else" as a transaction -- you have made it impossible. the caller should do that
COMMIT;

you don't have a clean API if you are doing gui stuff in it. I would not have that there
--Print the save confirmation.

HTML confirmation page

I would let the caller handle all exceptions. they can rollback, they can do whatever. Again, gui in the api, what
if tomorrow someone wants to call this as a web service, not as a mod_plsql thingy.

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN

1) because it is much less efficient to do two sql's rather than one.

ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> create sequence s;
 
Sequence created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_id  number;
  3  begin
  4          select s.nextval into l_id from dual;
  5          insert into t values ( l_id );
  6          dbms_output.put_line( l_id );
  7  end;
  8  /
1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_id  number;
  3  begin
  4          insert into t values ( s.nextval ) returning x into l_id;
  5          dbms_output.put_line( l_id );
  6  end;
  7  /
2
 
PL/SQL procedure successfully completed.


the second case there is really nice, less code, tastes great -- uses less resources and definitely helps with 90/10 block splits (if you pause in between "get sequence" and "insert sequence", large chance someone else inserts a sequence out of order -- meaning we don't see it as a monotomically increasing value and we'd do a 60/40 split - -meaning the index would grow faster then it needed to).


2) the calling, controlling procedure should:

   a) call your transactional API (which contains NO GUI logic)
   b) commit (or rollback) as it sees fit
   c) do gui stuff.

what does "refresh" has to do with anything?  not following you there.  why 'refresh'.  

3) google it.

"web service"

on google will return you the answer...... 

1) no, means less whitespace in the index structure.

2) i already told you how to solve problems like that -- i don't care how you write you code

a) modular as I suggest with separation of pieces
b) as a stream of stuff

you have the same exact issues, i fail to see how one technique solves it where the other cannot.

b) doesn't fit in my scheme of things. wrong place

c) save_data_entry should not be calling print_data_entry, the controlling process should be doing what you say your api is doing.

depends - in general, it sounds like you are doing a "blind update" - updating data without having ever looked at it.


If you read the data out at some point, looked at it, made a decision in your code to modify it - you should just update it - but in a fashion that ensures that a lost update does not occur, eg:


update t
set x = :x, y = :y
where primary_key = :pk
and decode( x, :old_x, 1 ) = 1
and decode( y, :old_y, 1 ) = 1


if you are just doing a blind update (you never read the data in the first place), I would question your logic - and in the rare case that such an event should happen - you would just update it.


So, just update it - but do so in a manner that does not suffer from the lost update problem (make sure the record did not change between the time you read it out, made a decision to modify it, and wrote it back).


Don't query it to see if it exists to update - by the time you go to update it, it might not exist anymore.

Actually, you need to do much better than that.

You need to perform lost update detection.

We must presume (in order to update), you read the data out at some point...

basically, you will:

Update table
   Set column1 = :new_column1, column2 = :new_column2, ....
 Where primary_key = :primary_key
   And column1 = :old_column1 
   And column2 = :old_column2

and check sql%rowcount (in plsql - or use whatever your API provides to tell you how many rows were updated) to see if you actually succeeded.


to read about this update in context:


<quote src=Expert Oracle Database Architecture>

Lost Updates
A lost update is a classic database problem. Actually, it is a problem in all multiuser computer environments. Simply put, a lost update occurs when the following events occur, in the order presented here:
1. A transaction in Session1 retrieves (queries) a row of data into local memory and displays it to an end user, User1.
2. Another transaction in Session2 retrieves that same row, but displays the data to a different end user, User2.
3. User1, using the application, modifies that row and has the application update the database and commit. Session1¿s transaction is now complete.
4. User2 modifies that row also, and has the application update the database and commit. Session2¿s transaction is now complete.
This process is referred to as a ¿lost update¿ because all of the changes made in step 3 will be lost. Consider, for example, an employee update screen that allows a user to change an address, work number, and so on. The application itself is very simple: a small search screen to generate a list of employees and then the ability to drill down into the details of each employee. This should be a piece of cake. So, we write the application with no locking on our part, just simple SELECT and UPDATE commands.
Then an end user (User1) navigates to the details screen, changes an address on the screen, clicks Save, and receives confirmation that the update was successful. Fine, except that when User1 checks the record the next day to send out a tax form, the old address is still listed. How could that have happened? Unfortunately, it can happen all too easily. In this case, another end user (User2) queried the same record just after User1 did¿after User1 read the data, but before User1 modified it. Then after User2 queried the data, User1 performed her update, received confirmation, and even requeried to see the change for herself. However, User2 then updated the work telephone number field and clicked Save, blissfully unaware of the fact that he just overwrote User1¿s changes to the address field with the old data! The reason this can happen in this case is that the application developer wrote the program such that when one particular field is updated, all fields for that record are ¿refreshed¿ (simply because it¿s easier to update all the columns instead of figuring out exactly which columns changed and only updating those).
Notice that for this to happen, User1 and User2 didn¿t even need to be working on the record at the exact same time. They simply needed to be working on the record at about the same time.
I¿ve seen this database issue crop up time and again when GUI programmers with little or no database training are given the task of writing a database application. They get a working knowledge of SELECT, INSERT, UPDATE, and DELETE and then set about writing the application. When the resulting application behaves in the manner just described, it completely destroys a user¿s confidence in it, especially since it seems so random, so sporadic, and it is totally irreproducible in a controlled environment (leading the developer to believe it must be user error).
Many tools, such as Oracle Forms and HTML DB, transparently protect you from this behavior by ensuring the record is unchanged from the time you query it and locked before you make any changes to it, but many others (such as a handwritten Visual Basic or Java program) do not. What the tools that protect you do behind the scenes, or what the developers must do themselves, is use one of two types of locking strategies: pessimistic or optimistic.

.................

Optimistic Locking
The second method, referred to as optimistic locking, defers all locking up to the point right before the update is performed. In other words, we will modify the information on the screen without a lock being acquired. We are optimistic that the data will not be changed by some other user; hence we wait until the very last moment to find out if we are right.
This locking method works in all environments, but it does increase the probability that a user performing an update will ¿lose.¿ That is, when that user go to update her row, she finds that the data has been modified, and she has to start over,
One popular implementation of optimistic locking is to keep the old and new values in the application, and upon updating the data use an update like this:

Update table
   Set column1 = :new_column1, column2 = :new_column2, ....
 Where primary_key = :primary_key
   And column1 = :old_column1 
   And column2 = :old_column2

...
Here, we are optimistic that the data doesn¿t get changed. In this case, if our update updates one row, we got lucky; the data didn¿t change between the time we read it and the time we got around to submitting the update. If we update zero rows, we lose; someone else changed the data and now we must figure out what we want to do to continue in the application. Should we make the end user rekey the transaction after querying the new values for the row (potentially causing the user frustration, as there is a chance the row will have changed yet again)? Should we try to merge the values of the two updates by performing update conflict-resolution based on business rules (lots of code)?
The preceding UPDATE will, in fact, avoid a lost update, but it does stand a chance of being blocked¿hanging while it waits for an UPDATE of that row by another session to complete. If all of your applications use optimistic locking, then using a straight UPDATE is generally OK since rows are locked for a very short duration as updates are applied and committed. However, if some of your applications use pessimistic locking, which will hold locks on rows for relatively long periods of time, then you will want to consider using a SELECT FOR UPDATE NOWAIT instead, to verify the row was not changed and lock it immediately prior to the UPDATE to avoid getting blocked by another session.
There are many methods of implementing optimistic concurrency control. We¿ve discussed one whereby the application will store all of the before images of the row in the application itself. In the following sections, we¿ll explore three others, namely
* Using a special column that is maintained by a database trigger or application code to tell us the ¿version¿ of the record
* Using a checksum or hash that was computed using the original data
* Using the new Oracle 10g feature ORA_ROWSCN
</quote>

1) we write software to do things correctly. If your attitude is "wow, that'll be hard for ME (the human programmer) so I won't do it" - please don't write any code I have to use.

My bank, I hope they do not take that approach "wow, protecting the integrity of data is so hard for the coder, we should just skip it - the customers will probably never notice"


(can you tell that statement by you just FLOORED ME, among other emotions)

2) umm, look at your logic. So many things wrong with it.

You just updated address, city, state and zip to some values and you GET THOSE VALUES RETURNED TO YOU (what you input, you get as output).

then you compare the inputs to the outputs and see if they match. Hmmm, but if the outputs are simply your inputs.....

Well, they would always match UNLESS one of the inputs was null then it would be unknown if they match

but in any case the TRUE/FALSE or UNKNOWN-ness of you "if" statement would do nothing to protect data integrity.


you will either:

a) select * into l_rec from t where pk = :pk and decode( c1, :old_c1, 1 )=1 and decode( c2,:old_c2,1) = 1 .... FOR UPDATE NOWAIT;

if (record returned)
then update with new values
else FAIL, record is locked (ora-54) OR you didn't geta record (someone else modified it, start OVER)

b) update t set c1 = :new_c1, c2 = :new_c2, .... where pk = :pk and decode(c1,:old_c1,1) = 1 and decode(c2,:old_c2,1) = 1 and .....;




and I don't care how much "pain" this causes, you are paid to do things correctly, no matter how tedious you believe it to be personally!!!!

1) Well, I did not misunderstand, you were very clear - you just did not type in what you meant.

You very very very clearly wrote:


...
If the table has 30 columns would you still do optimistic locking?
....

the question was "to do or not to do optimistic locking". The question was not "would you use the old/new values or a flag or a checksum...."

Sure, I would tend to use the flag column personally myself - if designing the application from scratch.

But what if you couldn't - and ora_rowscn isn't available (wrong release or the scn is tracked at the block level and you cannot rebuild the table) and whatever - then the column approach is just fine.


2) I do not follow you here at all.

in your example, if you update a row, you return "success" - the same as simply checking sql%rowcount = 1 would

if you do not update a row, the v_ values will be null and you will return failure, just like sql%rowcount = 0 would.

so, your way is a very obtuse, obscure way - that is made more clear and safer by using sql%rowcount

I have no clue what you mean by this:

...
We are not sure that values were updated correctly
....


IF sql%rowcount = 0, then you updated NOTHING
IF sql%rowcount = 1, then you updated a single row.


they were updated to whatever you asked them to be updated to, if rowcount = 1.


Now, try your example where

a) city WAS NULL in the table, and p_city = 'X'
b) city WAS NOT NULL in the table, and p_city is NULL
c) city WAS NULL in the table and p_city IS NULL



I don't get what you are trying to do here at all.


If you say "city=p_city", AND rowcount = 1, then you KNOW that in one row - city was set to the value in P_CITY.



Oh, and I don't like you using rollback and commit in stored procedures - ONLY THE CLIENT knows when to commit and when to rollback.

1) umm, it tells you your update actually updated the row they were interested in updating. think about that please.

If you issue an update and say "set x = 5", x will be 5 (sans triggers of course). that is sort of the way it works.


2) right, your logic was not correct...

3) only your most top level procedure should do this, not the individual procedures that do the modifications.

B is a transaction, it should contain no user interface, just database stuff, so B must be called by something - in fact, B might be a handful of routines ( because we code in a very modular fashion, with very small subroutines)

So, you have a procedure A that draws a screen...

Procedure A will submit to a routine "Handle_A_input"

Handle_A_input will invoke B and B' and B'' and so on - whatever it takes to process the transaction and Handle_A_Input will *likely* call A or bits of A to redraw the screen and then Handle_A_Input may commit.

we have the concept of modular code, that of separating bits of logic.

Yours will not be the last application to want to use this data, put the transactional logic SEPARATE so that when your user interface becomes "the old junky interface" - you can reuse what you have.

makes maintaining the code easier
makes enhancing the code easier
makes understanding the code easier
makes everything better.

use small modular, reusable routines.

all i saw in your example was a proc a and proc b.


suppose tomorrow the code needs to be:

proc A
proc B
proc D --> more transaction code, you need to CREATE an emp AND assign them a room
proc C --> now we are done, now we can commit.


your existing proc B cannot presume to be the end all, be all routine. Things change over time - by committing yourself, you presume to know what the client transaction will forever be.

I'm saying "you do not know that". Not long term anyway.

this is not really relevant to the original question, but trivial to answer

update t 
   set col1 = decode( p_col1, null, col1, p_col1 ),
       col2 = decode( p_col2, null, col2, p_col2 ),
       ...
 where (p_col1 is not null or p_col2 is not null 
        or p_col3 is not null or p_col4 is not null)
   and (whatever other conditions you have)

no idea what you mean by "4 different message formats", the http client is either

a) sending a single message
b) sending 4 messages, one by one

and that'll dictate how you process...

1) ummm, no. not at all

I took YOUR example and used it.

update t 
   set col1 = decode( p_col1, null, col1, p_col1 ),
       col2 = decode( p_col2, null, col2, p_col2 ),
       ...
 where (p_col1 is not null or p_col2 is not null 
        or p_col3 is not null or p_col4 is not null)
   and (whatever other conditions you have)

table T is a table with columns named COL1, COL2, .....

p_col1 is a plsql program variable, your input, just like in your example.

2) it is mostly up to you, I prefer specific code over wildly generic code, a single routine would be "generic", whether it is extremely generic (too generic to do it correctly) or not, I cannot tell sitting here.

... DO I need to add the same code (update statement) in every exception and
main program. ...

no idea what you mean by that - but do allow me to comment on your code.


I do not like it one bit. Classic reason WHY I wish plsql could neither commit nor rollback.

So, say you have a less trivial example like:

begin
update this
delete that
insert here
exception
when something
then
update error_table;
commit;
end;


what does your exception block commit? It commits maybe the update this, maybe the delete (but never the insert in this example). the only thing you can say here is "if the update error table works, that will be committed - other bits MIGHT get committed, might not"



This screams for a "procedure" (whenever you see yourself typing the same thing over and over...)

similar to this (I don't like this logic, that a failure has to rollback, I believe an exception should just be propagated all of the way up - but whatever):

create or replace procedure 
update_the_log( p_msg_sent in varchar2, p_msg_id in number, p_err_code in number default null, p_err_msg in varchar2 default null)
is
    pragma autonomous_transaction; 
begin
   if (p_err_code is not null) then htp.p('<ERR_CODE>'|| to_char(p_err_code) ||'</ERR_CODE>' ); end if;
   if (p_err_msg is not null) then htp.p('<ERR_MSG>'|| p_err_msg ||'</ERR_MSG>' );
   update my_log set msg_sent = p_msg_sent where msg_id = p_msg_id;
   commit;
end update_the_log;
/


BEGIN

OWA_UTIL.mime_header('text/xml',TRUE);
htp.p('<?xml version="1.0" encoding="ISO-8859-1"?>');

begin
   For x in (select * from table)
   LOOP
      ......
      ....
   END LOOP;

   htp.p('<XML tags>')
   v_msg_sent := '<all the XML tags>'

   update_the_log( v_msg_sent, v_msg_id );
exception
when no_data_found
then
   rollback;
   update_the_log( v_msg_sent, v_msg_id, 200, 'No data was found' );
when invalid_parameter
then 
   rollback;
   update_the_log( v_msg_sent, v_msg_id, 100, 'Your input is missing a parameter' );
end;

I can only follow your example... You updated my_log all three times.

You just need more than one procedure for two tables, might even be "more clear" that way, as you won't need the is null check.

and only use the autonomous transaction for the ERROR LOGGING ONE.


one obvious programming approach to what you are doing involves - yet another procedure.

is
   l_my_var long;
   ...

   procedure print( p_string in varchar2 )
   is 
   begin
        htp.p( p_string );
        l_my_var := l_my_var || p_string;
   end;
begin
   ....
   for x in (select * from table)
   loop
       print( xml line 1 );
       print( xml line 2 );
   end loop;
   ....
   l_my_var is the full string here....

1) how could you read my code and come to that assumption? I actually demonstrated using a procedure in a procedure there....


I am creating a procedure in the procedure.

You can do it however you like.


2) when you update the error log, you do NOT want to commit the larger transaction. You do not want to roll it back - you want this error logging routine to be able to be called by anyone safely.

the autonomous transaction makes this "safe", the insert into the error log is committed - but NOTHING ELSE IS - you do not break anyones transactional integrity, but you are sure that the record is logged.

1) no idea what you mean. Unless you mean "should I turn my invalid_parameter / invalid_user procedures into FUNCTIONS that return a string that is the message"

(use your programming skills here, this is just an exercise in programming at this point, nothing about oracle really - just coding)

2) do you understand how scoping works in plsql? Yes, if you make this standalone, and my_var is a local variable - the standalone procedure cannot "see" it, you would have to pass it as an in out parameter.

3) what is the client, what calls this procedure, what is the TOP LEVEL THING calling this.

1) modular programming, what result gives you a fine set of reusable subroutines that results in code that fits on a screen from top of procedure to bottom and pleases you aesthetically

2) I mean, if you don't have something in scope, you'll need to pass it in obviously. You need to both read and write this variable, so it'll be an IN OUT parameter.

You will have no local variables at all to speak of, you'll be PASSING some variable as a formal, named parameter and using it.

3) the error code should be the ora-xxxx error code and the client decides what to do based on that.

sure, it is ora-00000

no error - commit is just a sql statement, you execute it, you get either

a) the absence of an error, ora-00000
b) an error

depends, in many cases the application should

a) log error
b) convert error into something that uses the client can understand

for you see, the client could be anything - a human being, another procedure, the end point of a web service, whatever


just consider it ZERO. the sqlcode is ZERO, the sqlerrm is a string.

to make this not be a local procedure is pretty straightforward programming.

pass a PARAMETER, instead of accessing a local global.

procedure print( p_my_var in out long, p_string in varchar2 )
is
begin
  htp.p( p_string );
  p_my_var := p_my_var || p_string;
end;

do that as a top level procedure in a package and then

loop
   print( l_my_var, xml_line_1 );
   ....

Not really "oracle specific", but just sort of straight forward programming there..

the raise itself never causes a rollback.


I would suggest to never use commit or rollback in plsql. let the client - which is after all the only thing that truly knows when a transaction is complete - make that call.


there is an implicit rollback to savepoint issued from the client IF the plsql routine returns an exception t the client. a block of code such as:

begin p; end;


that is submitted by a client is processed like this:

begin
   savepoint X;
   p;
exception
   when others
   then
       rollback to X;
       RAISE;
end;

it makes the execution of P atomic (it either entirely succeeds or entirely fails - but only if the plsql programmer doesn't MUCK IT ALL UP by committing themselves or rolling back)

Let the client decide when and if the transaction is complete. That way - they can take two or three of what you thought were transactions and execute them as a single one.

An 8 column primary key is possible. But is likely a sign that the table has a low level of normalization.

Your example looks like a hierarchy to me. In which case you have a single column PK and another column referencing the parent PK.