CODEWITHSUNDEEP
oracle-databaseplsql
Rebooting
Rebooting

Reputation: 2938

PL/SQL: Statement ignored

I am trying to create a procedure which updates a transaction table according to the transaction type... but i am unable to correct the error: PL/SQL: Statement ignored My code is :

create or replace procedure proc(acn transaction.acctno%TYPE, tt transaction.transtype%TYPE, amt transaction.amount%TYPE, bal transaction.balance%TYPE, dt transaction.dot%TYPE)
IS
ano transaction.acctno%TYPE;
ba transaction.balance%TYPE;
BEGIN
select acctno,balance into ano,bno from transaction where accctno=acn;
if tt='W' and bno>500 
then
update transaction set balance=balance-amt;
elsif tt='D'
then
update transaction set balance=balance+amt;
else
dbms_output.put_line('Insufficient balance');
end if;
END;

full error:

ERROR at line 7: PL/SQL: Statement ignored

5. BEGIN
6. select acctno,balance into ano,bno from transaction where accctno=acn;
7. if tt='W' and bno>500 
8. then
9. update transaction set balance=balance-amt;

Upvotes: 0

Views: 3445

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

Your update statements are updating the entire table. You probably want to "select for update", and then update "where current of".

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40499

I first thought that the error is because your table name is transaction which is a reserved word:

select * from v$reserved_words where lower(keyword) = 'transaction';

This is not the case however. But it seemst that the variable bno into which you want to select is not declared. You have, however, declared another variable ba transaction.balance%TYPE;. Proboably, you should update the variable name either in the declaration or in the statement so that both are the same.

Upvotes: 1

Related Questions