DML, DDL in Function

 

Can we have out, inout parameter to a function?

 

There is no such restrictions. Functions can have OUT or IN OUT parameters.

However, Oracle recommends against using them.

OUT and IN OUT parameters prevent a function from being used from plain SQL, marked as a DETERMINISTIC function or used as a result-cached function. So these type of parameters are mainly a problem if you want to use the function in a SQL query.

However, You can assign out parameter values in a variable.

SQL>  create or replace function fun (a in number,b out number, c in out number) return number is

  2   begin

  3    b:=a+10;

  4    c:=c+a;

  5    return(100);

  6   end;

  7   /

Function created.

SQL>  declare

  2   x number:=10;

  3   y number:=20;

  4   z number;

  5   begin

  6   z:=fun(x,z,y);

  7   DBMS_OUTPUT.PUT_LINE('X'||X||'y'||y||'z'||z);

  8   end;

  9   /

X10y30z100

PL/SQL procedure successfully completed.

 

How to use DML Statements in a Function ?

 

We can not place any DML statement inside a function simply and also a function with a DML statement cannot be used inside a SELECT query.  

It will be compiled successfully in case of  DML statement inside.  

CREATE OR REPLACE

  FUNCTION fun1(i_empno IN NUMBER)

    RETURN NUMBER

  AS

    i_count NUMBER;

  BEGIN

    --

    DELETE FROM emp WHERE empno = i_empno;

    --

    i_count:=sql%rowcount;

    --

    RETURN i_count;

  END;

  /

If we try to run the above function in a select query, the oracle engine throws the error as shown below:

SQL> SELECT fun1(1) FROM dual;   ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SYSTEM.FUN1", line 6 14551. 00000 -  "cannot perform a DML operation inside a query " *Cause:    DML operation LIKE INSERT, UPDATE, DELETE OR select-for-UPDATE            cannot be performed inside a query OR UNDER a PDML slave. *Action:   Ensure that the offending DML operation IS NOT performed OR            USE an autonomous TRANSACTION TO perform the DML operation WITHIN            the query OR PDML slave.

 

To use a DML statement inside a Function and also to use that function inside a SELECT query, we have to use PRAGMA AUTONOMOUS_TRANSACTION inside the function.

CREATE OR REPLACE   FUNCTION fun2(       i_empno IN NUMBER)     RETURN NUMBER   AS     pragma autonomous_transaction;     i_count NUMBER;   BEGIN     DELETE FROM emp WHERE empno=i_empno;     i_count:=sql%rowcount;     COMMIT;     RETURN i_count;   END;   /

You can use the above function ‘fun2’ in a select query as shown below:

SELECT fun2(7499) FROM dual;

 

DDL

You can't have static DDL in a function:

create or replace function f

  return int as

  update_count int;

begin

 

  create table t2 ( x int );

 

  return update_count;

end;

/

 

Errors: check compiler log

 

sho err

 

LINE/COL ERROR

-------- -----------------------------------------------------------------

6/3      PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

Though you can use dynamic SQL to execute DDL:

create or replace function f

  return int as

  update_count int;

begin

 

  execute immediate 'create table t2 ( x int )';

 

  return update_count;

end;

/

sho err

 

var v number;

exec :v := f;

desc t2

 

Name Null?    Type      

---- -------- ----------

X    NOT NULL NUMBER(38)

 

To use a DDL statement inside a Function and also to use that function inside a SELECT query, we have to use PRAGMA AUTONOMOUS_TRANSACTION inside the function.