Oracle PL/SQL: Call DML procedure from a function

Question

I have a procedure that has DML commands. the procedure accepts a variable of type out, and it returns a value.
i need call this procedure from a function.
the goal is  that the function will return the value of the variable out returns the procedure.
(i need it for SSIS, but I believe that it is useful in other cases.)

During attempts, I got these errors:

ORA-14551: cannot perform a DML operation inside a query tips.
ORA-06519: active autonomous transaction detected and rolled back.

I'm looking for the right syntax to do it.

Answer 1

Example of a solution that works:

The procedure:

create or replace procedure MyProc(outRes OUT NUMBER) 
is
begin

  update some_table set some_description = 'abc';
  commit;

  if (some_condition) then
    outRes := 666;
  else
    outRes := 999;
  end if;

end MyProc;

Note: You must do commit; at the end of the DML command.

The function:

CREATE or replace FUNCTION MyFunc
  RETURN int IS 
  PRAGMA AUTONOMOUS_TRANSACTION;
  myVar number;
begin
  MyProc(myVar);
  return myVar;
END MyFunc;

Note that at the beginning of the function has: PRAGMA AUTONOMOUS_TRANSACTION;

And this function call:

select MyFunc() as res from dual;

Answer 2

Here is an example of what you need to do. Do know this is UNTESTED, but should give you a general idea of which way to go. This is known as Dynamic SQL and uses bind variables. There's a lot more I don't know such as the data type your procedure spits out and what not... so if it's not varchar2 then change it accordingly...

FUNCTION myFunc(procedure_call varchar2) RETURN VARCHAR2

IS
    v_out1 varchar2(500);

    BEGIN

      EXECUTE IMMEDIATE 'begin '||procedure_call||'( :out1 ); end;' using v_out1;
      RETURN v_out;

    END;