Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, santhoshreddy.

Asked: July 26, 2017 - 12:05 pm UTC

Last updated: July 27, 2017 - 4:02 pm UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

Hi,

can we write DML and DDL statements inside a function(stored procedures)?

and Chris said...

Which do you mean? Function or procedure?

In any case the answer is the same for both.

DML

You can have (static or dynamic) DML in a function:

create table t (
  x int 
);
create or replace function f
  return int as 
  update_count int;
begin
  insert into t values (1);
  update t set x = x + 1
  return count(*) into update_count;
  
  return update_count;
end;
/

var v number;
exec :v := f;
select * from t;

         X
----------
         2


Though we recommend that functions are free from side-effects. Which includes changing the contents of a table with insert/update/delete. This is because if a function does have side effects, it can stop you using it in SQL.

http://docs.oracle.com/database/122/ADFNS/coding-subprograms-and-packages.htm#GUID-69A02381-FE34-42D9-8A19-9AA8A51514C6

You should place your DML in a procedure instead.

Also remember: the official definition of DML includes select.

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) 


This is usually a questionable practice. Using functions to create or alter your objects is generally a bad idea. Though there are some DDL commands you may wish to run (such as truncate).

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not Working

santhoshreddy, July 27, 2017 - 7:32 am UTC

Inside function you inserted only one record, so Table contain only one record but you got result as 2 rows updated. how?
and also i written a same function but it did not update any records going to exception block. below is the function
create or replace function t_f return number as
v number;
begin
insert into t_emp values (1000,'Santhu','CLERK',7902,'17-DEC-1980',2000,NULL,20);
update t_emp set ename = 'San'
return count(*) into v;
return v;
exception
when others then
return 2;
end;
Chris Saxon
July 27, 2017 - 10:45 am UTC

Copy-paste error, I ran the function twice! ;)

santhoshreddy, July 27, 2017 - 12:12 pm UTC

Actually when i run function its executing Exception block.
so i removed exception block and compiled. when i ran the function i got below error.

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.T_F", line 4
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.
Chris Saxon
July 27, 2017 - 1:07 pm UTC

What precisely was the statement that lead to that error?

Sounds like you called the function in a SQL statement. Which, as the error says, is something you can't do. Which is part of the reason we recommend only changing tables in procedures, not functions!

santhoshreddy, July 27, 2017 - 1:10 pm UTC

yes i ran function in SELECT statement.so Function can not change the table when it runs with select statement?
and also what is this PDML Slave?
Chris Saxon
July 27, 2017 - 4:02 pm UTC

Correct, it can't.

"and also what is this PDML Slave?"

Parallel DML.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library