Solve “cannot perform a DML operation inside a query ” [duplicate]

1
votes

The pl SQL script below transfers the amount c of money from account a to b. Why isn't allowed to update the table in the function / how can it be fixed?

 create or replace function ueberweisung (a varchar2, b varchar2,c number)   

 RETURN varchar2 IS   
 k1 number; -- Variablendeklaration   
 k2 number;  

 BEGIN   

 SELECT saldo into k1   
 FROM konto   
 WHERE konto_nr=a;   

 SELECT saldo into k2   
 FROM konto   
 WHERE konto_nr=b;   

 k1:=k1-c;  
 k2:=k2+c;  

 update konto 
 set saldo = case
when konto_nr=a then k1
when konto_nr=b then k2
end;
commit;

 RETURN (c ||'Eur überwiesen von Konto ' || a || 'a uf Konto ' || b);   
 END ueberweisung;
1
sqloracleplsqlstored-functionsupdate-statement
A function is intended just to return data, not to change the data. Basically, it's just a reader, not a writer. Why don't you use a stored procedure? – HoneyBadger
It is allowed to update the table from the function. (It's convention to use a procedure rather than a function if you're modifying data, but it is allowed). What is not allowed is to then call that function from SQL. You can still call it (or a procedure) from PL/SQL, but not from a SQL context, You haven't shown how you call it, but the error means you've made it part of a SQL query. – Alex Poole

1 Answers

1
votes

It's part of Restrictions on PL/SQL Functions

in order to guard against nasty side effects and upredictable behavior, the Oracle Server makes it impossible for your stored function in SQL to take any of the following actions: The stored function may not modify database tables.

  • It cannot execute an INSERT, DELETE, or UPDATE statement.

Use PL/SQL procedure instead

PL/SQL has two types of subprograms, procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.