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 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.

"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 :(

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

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 !

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

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

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

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

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,

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

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...

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

...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?

...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!

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;

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

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.

...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.

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

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?

..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.

...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

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.

...
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.

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.

...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> 

- 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.

...
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?

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.

  BEGIN
      EXECUTE IMMEDIATE SQLST;

        EXCEPTION WHEN OTHERS THEN

          NULL;

  END; 

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 ''';

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

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?

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.

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!

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;