Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, vaneeza.

Asked: February 20, 2008 - 7:38 pm UTC

Last updated: March 29, 2011 - 4:22 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

hi Tom,

I have posted this procedure in oracle forum but friends suggested the way i have coded is not a way i do coding in oracle. I should use bind variable and cursor. As far as i know, how can i use bind variable here with where clasue as my variable is table name and fieldname. But they are constant for entire procedure. Here how can i use bind variable with where clause. Your suggestion will be highly appreciated.

Thanks,

Vaneeza.
CREATE OR REPLACE PROCEDURE CLEANING_THE (TABLENAME IN VARCHAR2, FIELDNAME IN VARCHAR2)

AUTHID CURRENT_USER

IS

SQLST VARCHAR2(250);

BEGIN

--.THE ;

SQLST := 'UPDATE ' ||UPPER(TABLENAME) || ' SET '||UPPER(FIELDNAME) || '=LTRIM('||UPPER(FIELDNAME)||',''.'')';

SQLST := SQLST || ' WHERE SUBSTR('||UPPER(FIELDNAME)||',1,5)=''.THE ''';

--DBMS_OUTPUT.PUT_LINE(SQLST);

BEGIN
EXECUTE IMMEDIATE SQLST;

EXCEPTION WHEN OTHERS THEN

NULL;

END;

COMMIT;

end CLEANING_THE;
/



and we said...

you have "when others then null"

for crying out loud.

AND THEN YOU COMMIT IT.


I give up, I just want to cry. You have ruined the entire day for me. This will be in the back of my head ALL DAY LONG.


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4018285967344#671326800346790791


first and foremost - never ever as in NEVER EVER code when others then null again - for as long as you live. Never. Just stop it now.


second - you cannot commit there, you have NO CLUE if the client is done with their transaction.

third, if you are going to concatenate stuff in dynamic sql, you have to submit your code to 5 people that do not like you. They must be permitted to rip your code to shreds and demonstrate all of its flaws. In this case, with authid current_user - you are 'probably' ok (they cannot do anything they could not have done in the first place).


ops$tkyte%ORA11GR1> CREATE OR REPLACE PROCEDURE CLEANING_THE (p_tname IN VARCHAR2, p_cname IN VARCHAR2)
  2  AUTHID CURRENT_USER
  3  IS
  4  BEGIN
  5      execute immediate '
  6      update ' || dbms_assert.simple_sql_name( p_tname ) || '
  7         set ' || dbms_assert.simple_sql_name( p_cname ) || ' = :val
  8       where substr( ' || dbms_assert.simple_sql_name( p_cname ) || ', 1, 5 ) = ''.THE ''
  9      ' using ltrim(upper(p_cname) || '.' );
 10  end CLEANING_THE;
 11  /

Procedure created.

ops$tkyte%ORA11GR1> create table t ( a varchar2(10) );

Table created.

ops$tkyte%ORA11GR1> insert into t values ( '.THE ' );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( 'xxx' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec cleaning_the( 't', 'a' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

A
----------
A.
xxx

Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alexander the ok, February 21, 2008 - 9:02 am UTC

99% of our vendors that use pl/sql use 'when others'. Sometimes they raise it with a generic 'some error happened', a lot of the time they log it.

I've come to the conclusion they do that to give the illusion their code works. "No errors, we're good."

Just trying to answer your question, why does this occur all the time. I don't get it either. That's the best I can come up with.
Tom Kyte
February 21, 2008 - 11:08 am UTC

I agree with your observation:

I've come to the conclusion they do that to give the illusion their code works.
"No errors, we're good."


In the old days, I used to sarcastically say "return code 0, ship it, it must be done"

If something returns without raising any errors, it must be working right...

Code Review

Greg, February 21, 2008 - 11:37 am UTC

Tom said:
In the old days, I used to sarcastically say "return code 0, ship it, it must be done"

If something returns without raising any errors, it must be working right...
--
I've got a better one than that. I worked with someone that actually said to our customer:
"No, I didn't need to unit test it. If it clean compiles, then the code is correct."
I had to leave the room.


karthick, February 21, 2008 - 12:41 pm UTC

"The DBMS_ASSERT package was introduced in Oracle 10g Release 2 and backported to Release 1 in the Oracle October 2005 Critical Patch Update. There are currently no references to this package in the 10g Release 2 documentation or on Metalink. The package contains a number of functions that can be used to sanitize user input and help to guard against SQL injection in applications that don't use bind variables."

This is what i got. The original poster is in 9.2.0 will it work in that version. I don't have 9.2.0 to try :(
Tom Kyte
February 21, 2008 - 4:51 pm UTC

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> desc dbms_alert
PROCEDURE REGISTER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE REMOVE

Vaneeza

Sokrates, February 21, 2008 - 4:44 pm UTC

why don't you just do

CREATE OR REPLACE PROCEDURE CLEANING_THE (TABLENAME IN VARCHAR2, FIELDNAME IN VARCHAR2) is
begin
null;
end;
/

?

at least you know, what it does !
Tom Kyte
February 21, 2008 - 5:08 pm UTC

it would be faster and just as good - absolutely.



https://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html
more fuel... turn up the fire... I've been known to say the same thing :) just erase the code, it is just as good.

dbms_alert???

Karthick Pattabiraman, February 22, 2008 - 12:20 am UTC

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> desc dbms_alert <<<---- Shouldingt this be dbms_assert
PROCEDURE REGISTER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
PROCEDURE REMOVE


Tom Kyte
February 22, 2008 - 7:08 am UTC

sorry, yes

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> desc dbms_assert
FUNCTION ENQUOTE_LITERAL RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            VARCHAR2                IN
FUNCTION ENQUOTE_NAME RETURNS VARCHAR2

revoke commit in procedure

Sokrates, February 22, 2008 - 3:20 am UTC

Tom, thx for the link, I've read this article before and absolutely agree with you.

Probably here, to be exact, the logical equivalent should be

CREATE OR REPLACE PROCEDURE CLEANING_THE (TABLENAME IN VARCHAR2, FIELDNAME IN VARCHAR2) is
begin
commit;
end;

I think, which, of course, is even worse.

By the way, as far as I remember we sometimes (v7 ?) had a
privilege "commit in procedure" (which by default was turned off), correct ?
Why this was erased ?

SQL> revoke commit in procedure from vaneeza;
revoke commit in procedure from vaneeza
       *
ERROR at line 1:
ORA-00990: missing or invalid privilege

Tom Kyte
February 22, 2008 - 7:13 am UTC

Trigger !

Sokrates, February 22, 2008 - 7:33 am UTC

thx, for that, which I also forgot !

So, now, because it's no default anymore, isn't that a great use of triggers ?
SQL> create trigger disable_commit_in_procedure
  2  after logon on database
  3  begin
  4     execute immediate 'alter session disable commit in procedure';
  5  end;
  6  /

Trigger created.

SQL> grant resource, connect to vaneeza identified by iwanttocommit;

Grant succeeded.

SQL> connect vaneeza/iwanttocommit
Connected.
SQL> CREATE OR REPLACE PROCEDURE CLEANING_THE (TABLENAME IN VARCHAR2, FIELDNAME IN VARCHAR2) is
  2  begin
  3  commit;
  4  end;
  5  /

Procedure created.

SQL> exec cleaning_the('error of', 'commit in procedure')
BEGIN cleaning_the('error of', 'commit in procedure'); END;

*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "VANEEZA.CLEANING_THE", line 3
ORA-06512: at line 1


Well...

Khan Kashif, February 22, 2008 - 12:00 pm UTC

Tom, Sokrates and others,

Lets be a little more accomodating and not rule out the possibility that Vaneeza might be a new programmer, and may not be acutely aware of all the transactional minutia that a senior database developer would be. We can't assume the same level of database sophistication off everyone who posts here.

Besides, I really don't think slamming down on someone is a good way to get someone to address the follies of their programming ways.

Kashif

Tom Kyte
February 22, 2008 - 12:23 pm UTC

Khan

there is no excuse ever for this:


EXCEPTION WHEN OTHERS THEN

NULL;

never, in any language. Forget the transactional minutia - that is just *wrong*, in oracle, in java, in anything - database based or not.

forget the database sophistication - just lets call them a programmer. That practice needs to be slapped down, period.

I see it day after day after day - over and over and over. I'm getting really frightened by it, it seems to be getting WORSE over time.

Sorry, I'm going to get more vocal, louder, more upset - every time I see this.

Kashif, February 22, 2008 - 12:57 pm UTC

Tom,

I don't disagree with the message you're trying to get across, far from it, I know it's a bad practice. I just disagree with the method and tone adopted here to address it. It just seems harsh and unnecessary. That's all.

Kashif

karthick, February 22, 2008 - 2:18 pm UTC

The next time the original poster tries to do

WHEN OTHERS THEN
NULL;

I am sure Tom's Scary reaction is going to come in back of her mind and remained her and even every one here not to do that. So its just we are benefited by this in a big way.

I personally believe things that are said in a very soft and simple way are lost in the wind. You need to be hard some time. I am sure the original poster and other visiting this thread is going to be benefited in a big way as they can never do the WHEN OTHER THEN NULL stuff. Because definitely Tom will scare them i there dreams :-)

Don't change this tom we like you this way...

harsh

Sokrates, February 23, 2008 - 2:08 am UTC

Khan Kashif

let me say two words why I am harsh on Vaneeza

When I learnt database programming and PL/SQL, my mentor in our project used
when others then null
all over and over.
It seemed very strange to me and I asked her
"what's the point with that" ?
And she told me
"that's the way you have to deal with exceptions when doing database-programming, it protects you against unconvenient questions from the end-users"

guess what happened - the project collapsed *because of the*
when others then null
bullshit (sorry, but this is the right word to use).

she just was bad in database designing and programming and hided this behind
when others then null.

I accept no excuse

COMMIT

Robert, February 24, 2008 - 12:28 am UTC

...second - you cannot commit there, you have NO CLUE if the client is done with their transaction. ...

Tom,

Are you suggesting that the stored procedure should return back to the client, whilst still open (in terms of a transaction), to let the client decide whether to commit or roll back the transaction?

I would argue that the stored procedure should make this decision, and not AN application. If you leave it to the application to decide, then two separate applications could make handle the result differently. Application A might commit, application B might commit based on the contents of the result set, and application C might roll back the transaction.

Have I read your response incorrectly or is my view that the database should take ownership of a database transaction different to that of your own?
Tom Kyte
February 24, 2008 - 11:53 am UTC

... Are you suggesting that the stored procedure should return back to the client,
whilst still open (in terms of a transaction), to let the client decide whether
to commit or roll back the transaction? ...


Not suggesting, shouting at the top of my voice I am. YES, YES - that is what should happen. The CLIENT is the only one that knows when to commit and when to rollback. When to hold the cards and when to fold.


then two separate
applications could make handle the result differently.


and that is exactly, precisely what I want - no NEED to enable.

I have two stored procedures:

change_address
change_phone_number


Now, if they both commit - how can I make a nice transaction that either does BOTH or NEITHER (hint: answer is FAIL, you lose)

now, say I just had one API call:

modify_account( p_account_number, p_by_amount )

that would add p_by_amount to a given account and I needed to write a transaction:

transfer:
modify_account( 1234, 500 );
modify_account( 1234, -500 );


I just want to transfer - if modify_account commits - FAIL, cannot do it, must replicate code all over the place.


The client is the only one that reasonable knows when the entire transaction is over. We, at the level of minutiae - cannot tell.


You might commit in plsql - at the very very very top level (meaning 99.999% of your procedures DO NOT COMMIT)

but what I find is either

a) every single silly procedure commits or rolls back, as if it were important enough to know.

b) none of them do - the client does it all.


I take (b) over (a) ANY DAY

IM text

Robert, February 24, 2008 - 12:58 am UTC

...Reviewer: Sokrates
Tom, thx for the link, I've read this article before and absolutely agree with you....

What is "thx"? As it is not a word, I would like to know what it is!

Karthick, February 24, 2008 - 10:49 am UTC

To Robert...

BEGIN

BEGIN
STEP1: INSERT INTO...
STEP2: UPDATE....
STEP3: INSERT INTO...
STEP4: DELETE....
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

COMMIT;
END;

Tell me what will happen if STEP 3 fails??? It will go to the exception and what it does????

Nothing good!!!

Comes out of the block and gets committed. What gets committed just step 1 and 2. Was the transaction completely over???

A big NO...

So my choice of commit would be like this...

BEGIN

BEGIN
STEP1: INSERT INTO...
STEP2: UPDATE....
STEP3: INSERT INTO...
STEP4: DELETE....

COMMIT;<<--- Here i am sure my transaction is complete successfully.
EXCEPTION
WHEN OTHERS THEN <<--- This crazy stuff must be kicked out of the code
NULL;
END;

END;

Tom Kyte
February 24, 2008 - 11:58 am UTC

well, I will argue that the only sensible implementation would be:
BEGIN
        STEP1: INSERT INTO...
        STEP2: UPDATE....
        STEP3: INSERT INTO...
        STEP4: DELETE....
END;

Rollback

Robert C, February 24, 2008 - 1:29 pm UTC

No, the transaction should be rolled back in that case. If you let the application decide, then the applications will start to own the data. You're "shouting" that the application should take ownership of the data, which I disagree with. I do not want a VB application deciding whether or not to commit or rollback. This decision should be made by the database developer.

If an exception occurs, then do one of the following:

Rollback the transaction
Handle the exception and continue on processing.

Each of which is possible as it depends on the level at which you manage your exceptions.

Now, let's say we do it your way, and let the applications control transaction management. A new developer comes along and makes a modification to a small Java GUI application. A change is made such that If X occurs, then commit the data, instead of to roll back the transaction, as is the current behaviour. The result is that data integrity is now compromised.

You may be very trusting of the application developers, but I will not allow for the integrity of my data to be decided on a per application basis.
Tom Kyte
February 24, 2008 - 2:24 pm UTC

do you understand that a stored procedure call is atomic

that if you call:

begin procedure; end;

and it fails, it is AS IF it was never called? that the client could call

begin p1; end;
begin p2; end;

and decide what to do it p2 fails?

but if p1 or p2 commits - they have no say in the matter? they cannot do the right thing.

I think 99% of the world does not understand that PLSQL calls are already ATOMIC.

And I don't think that the failure of a single component of a multi-component transaction demands that the entire thing be rolled back. Hey, IT COULD HAVE BEEN A DEADLOCK - eg: retry operation condition. You don't know, you are not smart enough at the component level to make that decision, you are (in stored procedure land) a little worker bee doing a bit of the work

and you are atomic, you will not leave the database all messy.


.... A change is made such that If X occurs, then commit the
data, instead of to roll back the transaction, as is the current behaviour. The
result is that data integrity is now compromised. ...


if that happens, that would mean the integrity is enforce in the application - and if that is true, you have lost already.

Data integrity IS NOT compromised, that is the one thing I more than anything else am concerned about.

If the STATEMENT invoked by the application FAILS it is as if that statement never happened.

It is the (ok, going to be harsh here - not at you Robert, you are not proposing this, but at the when others NO RAISE people) "not at all smart people" that cause the entire problem here.

it is when they turn a when others NOT INTO AN ERROR (they do not raise or re-raise) that they change the behavior of the database.

If you keep SQL atomic, then

begin p1; end;
begin p2; end;
commit;

in a client is not any different than

update t1 ...
update t2 ...
commit

in the client - it is when people have exception handlers that catch things THEY HAVE NO CLUE about and do not re-raise them that they break transactional integrity and may cause logical data integrity issues


You never need to rollback in a procedure, if you let the error propagate up - IT ALREADY ROLLED BACK!!!!!




To the above poster

Robert C, February 24, 2008 - 1:33 pm UTC

...Tell me what will happen if STEP 3 fails??? It will go to the exception and what it does????...

Handle it. As you would with any procedure in any programming language. Consider a java application:

Function main()

doA()
doB()
doC()
doD()
doE()

If you have only one exception which is controlled at the level of main(), you're level of control is limited. Do all, or do none. If it's possible to only do some, or to encounter an error on B, recover from it, and continue processing, then as with any programming language, you would write code to specifically handle doB() also.
Tom Kyte
February 24, 2008 - 2:29 pm UTC

if step 3 failed, and we let the error go back to the client, it is as if the block of code NEVER WAS EXECUTED.

all statements are ATOMIC, they either

a) entirely happen
b) do not happen at all

it is only when you inject transactional statements like commit, rollback OR turn errors into "non errors" that you BREAK THIS BASIC TENANT - that of atomicity.

see the huge difference here:


ops$tkyte%ORA10GR2> create table t ( x int check (x>0) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          insert into t values ( 2 );
  3          insert into t values ( 1 );
  4          insert into t values ( 0 );
  5          insert into t values (-1 );
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C007462) violated
ORA-06512: at line 4


ops$tkyte%ORA10GR2> select * from t;

no rows selected

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          insert into t values ( 2 );
  3          insert into t values ( 1 );
  4          insert into t values ( 0 );
  5          insert into t values (-1 );
  6  exception
  7          when others
  8          then
  9                  dbms_output.put_line
 10                  ( 'Hey, there was a problem and like a not smart person,
 11                     I''m print you a nice message about it' );
 12                  dbms_output.put_line( sqlerrm );
 13  end;
 14  /
Hey, there was a problem and like a not smart person,
                   I'm print you a nice
message about it
ORA-02290: check constraint (OPS$TKYTE.SYS_C007462) violated

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t;

         X
----------
         2
         1


Your java example does not resonate with me, I don't know what you were trying to "say" there (other than java is not a transactional language - but so what? it allows you to catch and handle errors and do the right things...

SQL Server

Robert C, February 24, 2008 - 4:53 pm UTC

Hi Tom,

Thank you for your clarification. As I'm sure you're aware, I most certainly do not advocate the use of WHEN OTHERS THEN NULL, or any such similar situation where a RAISE is not issued.

I think my view in my previous two posts was based on my experience with SQL Server (argh). Referring back to you example, in SQL Server the following would occur:

insert into test values (1)
insert into test values (2)
insert into test values (0)

* Invoke this block of code *

SQL Server output:

(1 row(s) affected)

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint "CK__test__number__7D78A4E7". The conflict occurred in database "work", table "dbo.test", column 'number'.
The statement has been terminated.

select * from test

number
1
2

I hope this may serve to explain the angle of my above posts. In PL/SQL, how would you handle the situation where you need to perform some rule checking in a PL/SQL block, and based on the outcome, either continue or rollback the transaction?

My initial thoughts would be to code something like this (the principle):

if <rule not satisfied> then raise() + custom error message.

The result is:
The transaction is rolled back automatically by the DBMS and a custom error message is raised. That way, the application receives an exception as expected, but also can actually make sense of the violation of the custom rule check by examining the corresponding error message. Of course you can map database constraints to messages or numbers, so the application can later go back and query this table, but with custom error messages, the text of these (or a key to the mapping table) would need to be explicitly returned.

Is my understanding of this last point correct?


Tom Kyte
February 25, 2008 - 2:05 am UTC

I've actually written in my book Expert One on One Oracle that many a bad transaction habit comes from *other* databases brought over into Oracle with the assumption that they are all the same.

When someone asks how to roll back in a trigger and looks aghast when I say "you don't", I know they are a sql server developer where rolling back is something you have to do upon error!



... either continue or rollback the
transaction? ....

I do not believe you are smart enough in the plsql routine to know that you can rollback everything, you are a bit player in a larger production. You can rollback the STATEMENT (the block of code that invoked you) by raising an error (unless some other bit of code 'catches you')

You see, if I call 5 routines - and they each have the ability to commit or rollback - ending my transaction - MY TRANSACTION - that leads to horrible spaghetti code that no one can figure out.

You need to rewrite:

The result is:
The transaction is rolled back automatically by the DBMS and


to read:

The result is:
The STATEMENT is rolled back automatically by the DBMS and

Robert C, February 24, 2008 - 4:58 pm UTC

..And I don't think that the failure of a single component of a multi-component transaction demands that the entire thing be rolled back. Hey, IT COULD HAVE BEEN A DEADLOCK - eg: retry operation condition...

Ah... Exactly. If Procedure A calls Procedure B and Procedure B fails, then I wouldn't want to issue a rollback, but rather raise the exception of B to A and let A decide what to do. If my understanding is correct here, then I agree with you entirely.

Tom Kyte
February 25, 2008 - 2:05 am UTC

that is correct.

Clarification

Robert Cline, February 24, 2008 - 10:40 pm UTC

...I just want to transfer - if modify_account commits - FAIL, cannot do it, must replicate code all over the place.

The client is the only one that reasonable knows when the entire transaction is over. We, at the level of minutiae - cannot tell. ...

Tom,

It would be wrong and dangerous - absolutely wrong - to develop a procedure to modify an account to be used in the context of a transfer account, or any other procedure, for that matter. This is a view that I have always had and I agree wholly with you on this point.

In fact to create such a procedure would be to support Table APIs! And we all know they are the single biggest error one can make with respect to database API programming!! OK, perhaps the second, behind not using bind variables ;)

As we both agree on, transaction APIs are the only way to permit access to a database, with bulk operations a suitable candidate (other cases may apply, but they're beyond the scope of this discussion). Going back to your example, to illustrate simply the concept of transferring from account A to B, I would develop a procedure as follows:

Procedure Transfer(

StepA
StepB
StepC
)

StepA to StepB would ideally just be code fragments, and not stored procedure calls. I am of this view, as I am generally against code re-use in business specific solutions. For example, I don't agree with having a stored procedure to "select from a table" (what ever that means). So in this transfer example, all steps would be completed or the transaction would complete as if not a single step had been performed. This we agree on.

I think I may not have explained myself very clearly in my original comment. I was trying to point out that when a custom rule is violated (or some predicate P1 is not satisfied to be more general), the stored procedure should handle this AND raise the error to the client.

The stored procedure should say: OK, I need to perform Steps A, B and C. Now, step B involves a check that must be done with the procedure - ie, is not enforced via a database constraint. To handle this, if the check fails, I will do the following:

1) Rollback the transaction
2) Raise the error to the client

Now of course, this transaction represents, well, a transaction - a logical pience of work - such as Tranfer Account. You would never develop a procedure with the power to initiate a rollback to modify a single table - never!! This is why I dislike Table APIs and, especially, automatically generated stored procedures! (OK, well, most of them). You MUST NEVER initiate a rollback in a table api - NEVER!

* I thought I'd put some emphasis in my post to keep in style with the thread.

I hope this has clarified my position and original question.

Robert

Massive Transactions.

Karthick Pattabiraman, February 25, 2008 - 12:52 am UTC

Tom what you say would be correct in an OLTP system where the transaction may be short.

But what about the transactions that do massive changes to the database. Updating millions of records like in warehouse systems.

I would like my pl/sql to define what a transaction is there. And make it to commit or rollback accordingly.



Tom Kyte
February 25, 2008 - 2:20 am UTC

of course, there are exceptions. ugh, I don't know how to say this better.


http://asktom.oracle.com/Misc/hah-they-didn-even-mention-variable.html
it is like I wrote there - I wish they would take these 4 things away from plsql because they are done wrong so very often that I would forgo their existence to "save" developers from themselves.

Same with commit and rollback - it is almost ALWAYS DONE WRONG.


Can commit be safe in a procedure? Sure, when the PROCEDURE ITSELF IS THE DEFACTO CLIENT, the controller. 99.9% of plsql code that contains commit or rollback - SHOULD NOT. Most of it SHOULD NOT.

But when plsql is the client, then it is

a) ok to commit/rollback - you own the transaction, it is YOURS (and hopefully you are smart enough to realize the responsibility that comes with that and have made your application, your client restartable so that when it fails (1555, power failure, whatever) you can just restart it and it naturally picks up where it left off)

b) ok to catch when others and NOT RAISE an error (just log the message and exit - you might do that in the job queues for example if you just want to be executed ONCE, send email about the results and then be done - regardless of the outcome)

Never say Never
Never say Always
I Always say.

Robert Cline, February 25, 2008 - 1:39 am UTC

...
To Robert...

BEGIN

BEGIN
STEP1: INSERT INTO...
STEP2: UPDATE....
STEP3: INSERT INTO...
STEP4: DELETE....
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

COMMIT;
END;

Tell me what will happen if STEP 3 fails??? It will go to the exception and what it does????..

I was not suggesting this approach, nor have I ever done so. What I was raising was the issue of how what should control a database transaction. I believe this is the sole responsibility of a a database stored procedure (see above - ignoring exceptions such as bulk processing for example).

I do NOT believe, nor have I said, that a block of PL/SQL code should be resonsible for issuing rollback or commit statements. This responsibility should be managed by the outer most block of calling code, in otherwords, the transaction.

When I heard the word "client" used earlier, I thought of a JAVA, C# or VB application. These applications should never have the ability to control a transaction. They must only call transactions.

If Tom, in his earlier usage of the word "client", was referring to the outer most block of PL/SQL code (the well-defined database transaction), then there is no confusion.

Tom Kyte
February 25, 2008 - 2:31 am UTC

... I believe this
is the sole responsibility of a a database stored procedure ...


and here you and i will forever disagree.

Only in the rare case where the plsql block at the top level IS THE CLIENT (eg: being run by dbms_job/dbms_scheduler, being submitted as a batch job by some framework like enterprise manager)


...
When I heard the word "client" used earlier, I thought of a JAVA, C# or VB
application. These applications should never have the ability to control a
transaction. They must only call transactions.
......

we absolutely disagree with each other then. You start doing that and they'll start adding resource managers on top of you in order to string the N things together they need and start doing two phase commits against a single database (ugh, that is ugly).

Robert C, February 25, 2008 - 3:49 am UTC

So then you're saying that business logic should be done in the application. That, is ugly.

It seems that you'd be happy to see this in an application:

- Open database connection
- Invoke stored procedure A
- Check results of procedure A
- Invoke stored procedure B
- Examine the results
- If this, then call that procedure
- If that, then call another procedure
- If everything went OK, then commit.
- Close the database connection.

I just hope that when a new application comes along, the developers remember the rule for which stored procedures should be invoked and under what conditions the transaction should be committed or rolled back.
Tom Kyte
February 25, 2008 - 8:04 am UTC

... So then you're saying that business logic should be done in the application. ...

that is not what I'm saying at all. I'm saying "you don't know when the complete transaction is done, the client does"

the client could well be PLSQL itself - you could take this and say "plsql is my end point, clients will just call stored procedures and never do commit, rollback, error handling, whatever"

I find that too limiting in most cases.

I *never* said business logic should be done in the application, I don't see how you got there from here.

I would be happy with the above flow, sure.


They do not have to remember that flow, that flow is that flow. Someone else might just call A and commit. Nothing wrong with that.

Robert C - on SQL Sever

Sarma, February 25, 2008 - 5:50 am UTC

...select * from test

number
1
2...


Oracle doesn't allow "dirty reads". That is the reason, you won't see such a thing in Oracle.

SQL> create table sample1(col1 number check (col1 <> 3))
  2  /

Table created.

SQL> begin
  2     insert into sample1 values(1);
  3     insert into sample1 values(2);
  4     insert into sample1 values(3);
  5     insert into sample1 values(4);
  6     commit;
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-02290: check constraint (ETL_ADMIN.SYS_C006207) violated
ORA-06512: at line 4


SQL> select * from sample1
  2  /

no rows selected

SQL> 
SQL> drop table sample1 purge
  2  /

Table dropped.

SQL> 

SeánMacGC, February 25, 2008 - 6:00 am UTC

- Open database connection
- Invoke stored procedure A
- If no error, commit,
- Invoke stored procedure B
- If no error, commit

OR

- Open database connection
- Invoked stored procedure C, which is a combination of both procedure A and procedure B (A Transaction API)
- If no error, commit.

No need to be more involved that it has to be in Oracle.
Tom Kyte
February 25, 2008 - 8:08 am UTC

But both of those scenarios might be one you need to play out - neither is wrong, both are OK.

Only if each represents a logical transaction

Robert C, February 25, 2008 - 12:14 pm UTC

...
But both of those scenarios might be one you need to play out - neither is wrong, both are OK.
...

I believe we actually do agree on this topic, but my methods in using SQL Server have led me to appear to not fully acknowledge the behaviour of Oracle in the context of this discussion. I am now aware that within Oracle it is not required to explicitly control the transaction flow within a procedure - as Tom pointed out, this is done automatically. The examples posted above by Tom and others confirm this.

From my experience which I would argue as not being insufficient, but rather in a different technology, I would always ensure that each block of T-SQL code was explicitly rolled back or committed AND that exceptions were raised. Of course, you could execute multiple of these "blocks" - the procedure for doing so is different. I imagine it's different in Oracle too.

So yes, on this point, I have acknowledged that my inexperience in this area on Oracle led to my fall. But part of the reason was a tendency that I have (as in me as a person) to focus on the principle and not so much on the implementation. In this case that was clearly not a wise nor practical decision to make. I was trying to really argue the case about transaction APIs vs Table APIs, but due to my not correctly acknowledging the behaviour of Oracle in my responses, I served only to dig myself into a whole from which I could not dig myself out of!

What I would still like to put forth is that in response to Tom's most recent comment (which is quoted at the start of my reply), I would agree with you only if each of the procedures / blocks/ code fragments/ etc, can be classified as an independent transaction, and not a table API.

I think we both agree here, perhaps as I said above, my other posts overlooked the implementation details?
Tom Kyte
February 25, 2008 - 2:07 pm UTC

I'd now say we agree, yes.

variable value should be extract from table

vaneeza Ahmad, February 28, 2008 - 12:06 am UTC

HI Tom,

I was sick thats why I could not check your site. Thanks for reply and for such a harsh comments as I have already explained that I am quite new in Oracle and just learning.

I want to explain my query little bit more to make it clear to you.

my table name is lets say T and column name is 'A' and under this column I got the following values.


A
.THE SEW ON
.THE KNOWLES M J
INDOOR CRICKET AND NETBALL
QUEENSTOWN PANELBEATERS LTD
.THE CRAIGPINE TIMBER LTD

REQUIRED OUTPUT

A
THE SEW ON
THE KNOWLES M J
INDOOR CRICKET AND NETBALL
QUEENSTOWN PANELBEATERS LTD
THE CRAIGPINE TIMBER LTD

The procedure should use in USING values from 'A' not 'A' itself.





Tom Kyte
February 28, 2008 - 3:35 pm UTC

Unless you are a brand new programmer:

  BEGIN
      EXECUTE IMMEDIATE SQLST;

        EXCEPTION WHEN OTHERS THEN

          NULL;

  END; 


is unacceptable (forget oracle, just talking basic *programming skills* here), and if you are a brand new programmer, it is still unacceptable.

Harsh is in the eye of the beholder, if you see someone doing something so BASICALLY *WRONG*, inexcusably wrong - I wonder, how do you react?

what is the possible LOGIC BEHIND SUCH A CONSTRUCT. Please, validate your approach, tell us why you think this even makes sense?!?!?!?



you wrote:

....
A
.THE SEW ON
.THE KNOWLES M J
INDOOR CRICKET AND NETBALL
QUEENSTOWN PANELBEATERS LTD
.THE CRAIGPINE TIMBER LTD

REQUIRED OUTPUT

A
THE SEW ON
THE KNOWLES M J
INDOOR CRICKET AND NETBALL
QUEENSTOWN PANELBEATERS LTD
THE CRAIGPINE TIMBER LTD

The procedure should use in USING values from 'A' not 'A' itself.
.....

umm are you just trying to remove leading "." from strings? that is all I can figure out from this "this is what I have and this is what I want to have". Please - use "specifications", tell us the LOGIC here


Otherwise, I just thing

update t set c = ltrim(c,'.');

is all you need.

want to use bind variable

vaneeza Ahmad, February 28, 2008 - 5:40 pm UTC

Hi Tom,

Yes i want to trim leading '.' when there is '.THE '. If this is me who needs to perform this task then definately i will go for a single sql commmand but i am creating this procedure for end users (operators) who do not have any technical knowledge and they do data cleaning manually. The procedrue i have sent you earlier performs the same task but without bind variable. According to my understanding then it will do hard parse for each record. My question is this that how can i do soft parse here. Hope this is not a silly question and I have made it clear what i want. Thanks for explaing not to use
When others then
Null and how, where and who should commit.


SQLST := 'UPDATE ' ||UPPER(TABLENAME) || ' SET '||UPPER(FIELDNAME) || '=LTRIM('||UPPER(FIELDNAME)||',''.'')';

SQLST := SQLST || ' WHERE SUBSTR('||UPPER(FIELDNAME)||',1,5)=''.THE ''';

Tom Kyte
February 29, 2008 - 1:58 am UTC

... According to
my understanding then it will do hard parse for each record. ...

no, not for each record, for each execution of the statement (regardless of the number of affected records)


This seems like a very insecure thing you are doing here.

and well, frankly, I already showed you how to do this in the original answer with dbms_assert and binding.

So, what am I missing here....

arun pradhan, April 17, 2008 - 12:45 pm UTC

Hi Tom,
After reading you say no to WHEN OTHERS THEN NULL, i had to ask you the right solution.The proc i am working on is droping the index from the procedure. But the problem is sometimes index is in unusable state or not even present(that might give and error saying index not present or sthng). so to make sure that proc dont stop in the middle of process we are using when others then null so that if there is no index just let the proc flow...and it has been working great with no flaws. But, as you mentioned it not good practice can you point out the flaws and harm on doing this . i hope you would enlighten me .

heres the code i use:
proc xyz
Begin
.....
BEGIN
EXECUTE IMMEDIATE 'drop index box_idx1';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
........
INSERT into table1.....
EXECUTE IMMEDIATE 'create index box_idx1....';
Analyze table1.....
execute immediate '
ALTER TABLE archieve_table
EXCHANGE PARTITION ' || p_date || ' WITH TABLE table1
WITHOUT VALIDATION';
END;

Also, i guess sometimes when we exchange the partition then the index gets unusaable...so we have to used the when other then null ..so if we dont rebuild the index it should not create any failure..

thnx in advance
Tom Kyte
April 17, 2008 - 4:27 pm UTC

why not catch the exceptions YOU ANTICIPATE.

You are currently being lazy.

If you don't have to drop the index, which apparently YOU DO NOT HAVE TO, why both dropping it "sometimes"

Ok, index does not drop - say it fails.

So, insert works.

And create index FAILS (because the drop didn't drop) - that commits your insert, but the analyze and alter table NEVER HAPPEN.

Now what?!?!?!

this is very buggy code you have posted, with the potential for a great big failure.

Documentation full of "When others then ..."

A reader, May 14, 2008 - 6:07 pm UTC

Great, spot on commentary as usual.

I'm seeing a lot of the "when others then null" code also, but I think part of the problem is that developers see examples like this in documentation and books. The official Oracle documentation is full of examples coded this way. For example:

Example 1-12 Creating a Stored Subprogram

-- including OR REPLACE is more convenient when updating a subprogram
CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT commission_pct / 100 INTO commission FROM employees
WHERE employee_id = emp_id;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE employees SET salary = salary + bonus*commission
WHERE employee_id = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
commission := 0;
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref196

Is there anything that can be done to get examples like this fixed in the Oracle documentation?
Tom Kyte
May 16, 2008 - 11:57 am UTC

yes, point them out to me...

More Oracle docs with "when others then null"

Stew Ashton, May 16, 2008 - 3:42 pm UTC

I looked only in the 11G docs.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28415/ch_phtalbapps.htm#CHDGHFBE
"Example 3-6 Procedure insert_new_photo" has two.

http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_app_dbms_aw004.htm#sthref5965
Example B-1 Sparsity Advisor Script for GLOBAL

CONNECT global/global
SET ECHO ON
SET LINESIZE 300
SET PAGESIZE 300
SET SERVEROUT ON FORMAT WRAPPED
 
-- Define and initialize an advice table named AW_SPARSITY_ADVICE
BEGIN
     dbms_aw.sparsity_advice_table();
EXCEPTION
     WHEN OTHERS THEN NULL;
END;
/

http://download.oracle.com/docs/cd/B28359_01/server.111/b28327/rarconflictres.htm#sthref419
"Step 5 Create a package that sends a notification to the conf_report table when a conflict is detected."
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_repos_events.htm#sthref1983
"Example 30-3 PL/SQL Code Implementing Event Listeners"
http://download.oracle.com/docs/cd/B28359_01/server.111/b28327/raruserconflictres.htm#sthref2668
CREATE OR REPLACE PACKAGE BODY notify AS
 -- Define a PL/SQL index-by table to hold the notification message
 TYPE message_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
 PROCEDURE report_conflict (
<snipped>
     EXCEPTION WHEN others THEN NULL;

http://download.oracle.com/docs/cd/B28359_01/text.111/b28303/acase.htm#CCAPP9378
procedure showDoc (p_id in varchar2, p_query in varchar2) is
<snipped>
     exception
      when no_data_found then
         null;
     end;

     exception
      when others then
        null; --showHTMLdoc(p_id);

http://download.oracle.com/docs/cd/B28359_01/text.111/b28303/view.htm#CCAPP9197
end of procedure showdoc.
Tom Kyte
May 19, 2008 - 3:13 pm UTC

thanks

when others then null in C# ODP.NET example

rc, May 18, 2008 - 9:23 am UTC

public static void Setup(OracleConnection con)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", con);

blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch
{
}

I found this code in an Oracle example that comes with ODP.NET.

See ...\product\11.1.0\db_1\ODP.NET\samples\2.x\LOB\sample1.cs . ODP.NET deserves better examples!

local member varialbe

anjali, March 29, 2011 - 12:32 am UTC

PROCEDURE LOYALTYDB.CST_MEMBERSHIP_BALANCE_U_PROC (
chrMembershipCode IN NVARCHAR2 ,
decWalletBalance IN NUMBER
)
IS
walletAmt NUMBER;
BEGIN
select LOYALTYDB.TBL_MEMBERSHIP_LP.DECWALLETBALANCE into walletAmt from LOYALTYDB.TBL_MEMBERSHIP_LP
where LOYALTYDB.TBL_MEMBERSHIP_LP.CHRMEMBERSHIPCODE = chrMembershipCode;
walletAmt := walletAmt + decWalletBalance;

UPDATE LOYALTYDB.TBL_MEMBERSHIP_LP SET DECWALLETBALANCE =walletAmt
WHERE CHRMEMBERSHIPCODE = chrMembershipCode;
COMMIT;
END;
Tom Kyte
March 29, 2011 - 4:22 am UTC

why is that anything more than:

update tbl_membership_lp set walletAmt = walletAmt+decWallectBalance where CHRMEMBERSHIPCODE = CST_MEMBERSHIP_BALANCE_U_PROC.chrMembershipCode;

???

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library