Solving "Cannot perform DML inside a query" error in Oracle

Question

I've created regular function. It has been created successfully. But when I run it with

select reg('awlad','01968688680','545466455','12345') from dual

It gives me this error:

ORA-14551: cannot perform a DML operation inside a query

How can I solve this?

CREATE OR REPLACE FUNCTION reg(
name in varchar2,
cellNo in varchar2,
voterId in varchar2,
pass in varchar2
)
RETURN NUMBER
IS 
succ NUMBER;
BEGIN 
      succ:=0;
      insert into logInfo values(loginfo_seq.nextval,cellNo,pass,0);
      succ:=1;
      insert into passInfo values(name,cellNo,voterId);
      succ:=2;
      RETURN succ;
END;

Answer 1

A function is supposed to compute and return a result, not change the state of the database. If you want to do DML in a function (i.e. if you want to insert rows into tables), you cannot call that function in a SELECT statement since a SELECT statement cannot change the state of the database. In general, you'd be better off creating this sort of thing as a stored procedure rather than a stored function.

You can call this function from a PL/SQL block just as you would call a stored procedure

DECLARE
  l_success_code NUMBER;
BEGIN
  l_success_code := reg('awlad','01968688680','545466455','12345');
END;

If you want to create this as a procedure

CREATE OR REPLACE PROCEDURE reg( name in varchar2, 
                                 cellNo in varchar2, 
                                 voterId in varchar2, 
                                 pass in varchar2, 
                                 succ out NUMBER ) 
AS 
BEGIN 
  succ:=0; 
  insert into logInfo values(loginfo_seq.nextval,cellNo,pass,0); 
  insert into passInfo values(name,cellNo,voterId); 
  succ:=1; 
END;

then you'd need to call the procedure by passing in the OUT parameter

DECLARE
  l_success_code NUMBER;
BEGIN
  reg('awlad','01968688680','545466455','12345', l_success_code);
END;

Answer 2

If all you want to do is log information, it would be appropriate to use an autonomous transaction to do the intermediate insert.

CREATE OR REPLACE FUNCTION reg(NAME IN VARCHAR2, 
                               cellNo IN VARCHAR2, 
                               voterId IN VARCHAR2, 
                               pass IN VARCHAR2)
   RETURN NUMBER IS
   --
   PROCEDURE do_loginfo (p_id NUMBER, 
                         p_cellNo VARCHAR2, 
                         p_pass VARCHAR2, 
                         p_x NUMBER) IS
   PRAGMA AUTONOMOUS_TRANSACTION
   BEGIN
      INSERT INTO logInfo VALUES (p_id, p_cellNo, p_pass, p_x);
      COMMIT;
   END do_loginfo;
   PROCEDURE do_passInfo (p_name VARCHAR2, 
                          p_cellNo VARCHAR2, 
                          p_voterId VARCHAR2) IS
   PRAGMA AUTONOMOUS_TRANSACTION
   BEGIN
      INSERT INTO passInfo VALUES (p_name, p_cellNo, p_voterId);
      COMMIT;
   END do_passInfo;
   --
   succ NUMBER;
BEGIN
   succ := 0;
   do_logInfo (loginfo_seq.NEXTVAL, cellNo, pass, 0);
   succ := 1;
   do_passInfo (NAME, cellNo, voterId);
   succ := 2;
   RETURN succ;
END;

Note that it will be useful for debugging purpose, but since it is not transactional, it should not be used to record data (since the rows inserted will stay even if the main transaction is rolled back).