Procedure to update another table

[Hugo Nilsson]

So my procedure should add a row to the table INSÄTTNING. Some keys are reference keys. When the row has been inserted it should take P_BELOPP and subtract it with SALDO which is in another table called KONTO. The new amount should be added into that column and replace the old amount. My code looks like this so far:

create or replace procedure do_insättning( p_radnr in insättning.radnr%type, p_pnr in insättning.pnr%type, p_knr in insättning.knr%type, p_belopp in insättning.belopp%type, p_datum in insättning.datum%type) as begin insert into insättning(radnr,pnr,knr) values(select radnr from kontoägare,select pnr from bankkund, select knr from konto); insert into insättning(belopp,datum) values(p_belopp,p_datum); update konto set konto.saldo = konto.saldo - p_belopp where kontoägare.radnr = p_radnr; dbms_output.put_line('Saldo: '||''||saldo); end;
I get that SALDO must be declared error.

Hope its pretty understandable

[Paul Clapham]

Is "saldo" supposed to be the same as what you used elsewhere, "konto.saldo"?

[Hugo Nilsson]

If i understand what you mean then yes. The saldo should be updated with the calculation in SET and then printed out.

[Paul Clapham]

Then here's what I think: the SQL processor knows what "konto.saldo" means. It doesn't know what "saldo" by itself means. But you've said here that they mean the same thing to you. This suggests an obvious change to me; you should be able to see it.

[Hugo Nilsson]

So i made a change where i think it was meant:

set konto.saldo = konto.saldo - p_belopp where kontoägare.radnr = p_radnr; dbms_output.put_line('Saldo: '||''||konto.saldo); /*Changed here from saldo to konto.saldo*/

But i recieve two error:

Line/Col: 9/1 PL/SQL: SQL Statement ignored
Line/Col: 10/8 PL/SQL: ORA-00936: missing expression

[Tim Holloway]

And again, Oracle makes it perfectly clear in their error messages - NOT.

I believe that this is the offending statement:dbms_output.put_line('Saldo: '||''||konto.saldo);And the most probable reason for offence is that you're attempting to use a statement designed to work with a single argument value but one of the components of that argument is a multiple-row slice of a database table.

That is, "konto.saldo" isn't just one value, it is many.

[Hugo Nilsson]

How about if declare konto.saldo as number?

[Tim Holloway]

Many numbers is no better than many strings. I think what you probably wanted to do was loop through the table and print multiple rows.