Home » SQL & PL/SQL » SQL & PL/SQL » plsql (oracle10g, 10.2.0.2.0, AIX52)
plsql [message #425436] Fri, 09 October 2009 02:44 Go to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Hi All,

We have below plsql block.

/* start procedure */

accept dep_acct_num prompt ' Enter Account number: '
accept dep_amt prompt ' Enter amount to be Deposit : '

DECLARE
deposit SB_ACCOUNT.BALANCE_AMOUNT%TYPE;
account_num SB_ACCOUNT.ACCOUNT_NO%TYPE;
temp1 number(30);
begin
account_num := '&dep_acct_num';
deposit := '&dep_amt';
select a.BALANCE_AMOUNT + deposit into temp1 from SB_ACCOUNT a, dual where ACCOUNT_NO=account_num;
update SB_ACCOUNT
set BALANCE_AMOUNT= temp1
where ACCOUNT_NO=account_num;

COMMIT;
end;
/

Above is fine. When i was run this plsql block, we will get the message like 'PL/SQL procedure successfully completed.' But i also require '1 row updated' message once the update statement completed.

Please help me in this regard.

Regards,
Chandu
Re: plsql [message #425439 is a reply to message #425436] Fri, 09 October 2009 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The '1 row update' message isn't generated by the database - it is generated by SQL*Plus, in the same way tbhat TOAD or SQL*Developer will display a little message in the status bar of the window when you do an update.

You can look at the variable SQL%ROWCOUNT after a DML statement to see the number of rows affected if you want to do it yourself.

Your elect statement:
select a.BALANCE_AMOUNT + deposit into temp1 from SB_ACCOUNT a, dual where ACCOUNT_NO=account_num;
does not need to reference DUAL at all.

Additionally, you can rewrite your code in pl/sql to be smaller and more performant:
DECLARE
deposit     SB_ACCOUNT.BALANCE_AMOUNT%TYPE;
account_num SB_ACCOUNT.ACCOUNT_NO%TYPE;
begin
  account_num := '&dep_acct_num';
  deposit := '&dep_amt';

  update SB_ACCOUNT
  set BALANCE_AMOUNT= BALANCE_AMOUNT + deposit
  where ACCOUNT_NO=account_num;
  COMMIT;
end;

or, even smaller:
BEGIN
  update SB_ACCOUNT
  set BALANCE_AMOUNT= BALANCE_AMOUNT + '&dep_amt'
  where ACCOUNT_NO='&dep_acct_num';
  COMMIT;
end;
Re: plsql [message #425440 is a reply to message #425439] Fri, 09 October 2009 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For the last you don't need BEGIN and END and so OP's problem no more exist.
BUT this last one do not use bind variables for the update statement and should be avoided.
In addition, it does not check the input values against column definitions as it does it in PL/SQL declare section.

Regards
Michel
Re: plsql [message #425444 is a reply to message #425440] Fri, 09 October 2009 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As the OP is assigning both of his (probably numeric) variables to contain strings, I'm not going to loose too much sleep over the data typing.
Re: plsql [message #425445 is a reply to message #425444] Fri, 09 October 2009 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: plsql [message #425466 is a reply to message #425436] Fri, 09 October 2009 04:37 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Just I have sent the sample plsql code to convey my requirement.

I got the solution for the same. Thanks a lot to all.

I have one more question. Is there any way to store the value of DBMS_OUTPUT.PUT_LINE(sql%rowcount) into a variable ?
Re: plsql [message #425467 is a reply to message #425466] Fri, 09 October 2009 04:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Store variable before you use it in dbms_output?

You can use dbms_ouptut.get_line in another piece of code to read messages that were written using dbms_output.put_line, but I really wouldn't recommend it.
Previous Topic: ORA-29279: SMTP permanent error: 501 Syntax error, missing brackets
Next Topic: Ora-00604 while dropping user.
Goto Forum:
  


Current Time: Thu Sep 29 14:07:36 CDT 2016

Total time taken to generate the page: 0.10427 seconds