jump to navigation

Workaround the DMLs: PL/SQL function restrictions July 14, 2009

Posted by sendtoshailesh in Uncategorized.
trackback

When a PL/SQL function is called from PL/SQL code, There is no restriction as long as consistency  is maintained.
But any DML from functions are not allowed if function is called from sql. following I am presnting some examples which shows these restrictions:

Following a demo table is required.

SQL> create table f1 as select * from dual;

Table created.

SQL> select * from f1;

D

X

SQL> insert into f1 values(‘Y’);

1 row created.

SQL> insert into f1 values(‘Z’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from f1;

D

X
Y
Z

Now our demo table have above sample data. Let us make a PL/SQL function.

SQL> ed
Wrote file afiedt.buf

1  create or replace function ft
2  (name varchar2)
3  return varchar2
4  is
5  begin
6  return name;
7* end;
SQL> /

Function created.

SQL> ed
Wrote file afiedt.buf

1* select dummy,ft(‘X’) from f1
SQL> /

D

FT(‘X’)
————————————————————————–
X
X

Y
X

Z
X

PL/SQL function should not have out parameter, if it is called from SQL.

SQL> ed
Wrote file afiedt.buf

1  create or replace function ft
2  (name in out varchar2)
3  return varchar2
4  is
5  begin
6  return name;
7* end;
SQL> /

Function created.

SQL>  select dummy,ft(‘X’) from f1;
select dummy,ft(‘X’) from f1
*
ERROR at line 1:
ORA-06572: Function FT has out arguments

SQL> variable b1 varchar2(10)

SQL> execute :b1:=’X’

PL/SQL procedure successfully completed.

SQL> execute :b1:=ft(:b1)

PL/SQL procedure successfully completed.

SQL> print b1

B1
——————————–
X


Next some DML is performed with-in function.

SQL> ed
Wrote file afiedt.buf

1  create or replace function ft
2  (name varchar2)
3  return varchar2
4  is
5  begin
6  insert into f1 values(name);
7  return name;
8* end;
SQL> /

Function created.

SQL> ed
Wrote file afiedt.buf

1  select ft(dummy)
2* from f1
SQL> /
select ft(dummy)
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at “HR.FT”, line 6

next, function is called from another DML on same table.
SQL> ed
Wrote file afiedt.buf

1  update f1
2  set dummy=ft(dummy)
3* where dummy=’X’
SQL> /
set dummy=ft(dummy)
*
ERROR at line 2:
ORA-04091: table HR.F1 is mutating, trigger/function may not see it
ORA-06512: at “HR.FT”, line 6

Lte us have TCL statement in function.
SQL> ed
Wrote file afiedt.buf

1  create or replace function ft
2  (name varchar2)
3  return varchar2
4  is
5  begin
6  rollback;
7  return name;
8* end;
SQL> /

Function created.

SQL> select ft(dummy)
2  from f1
3  /
select ft(dummy)
*
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at “HR.FT”, line 6

Let us call function from SELECT statement as the same time finction can fire SELECT statement from same table.
This is allowed.
SQL> ed
Wrote file afiedt.buf

1  create or replace function ft
2  (name varchar2)
3  return varchar2
4  is
5  c varchar2(1);
6  begin
7  select dummy
8  into c
9  from f1
10  where dummy=’Z’;
11  return c;
12* end;
SQL> /

Function created.

SQL> select ft(dummy)
2  from f1;

FT(DUMMY)
————————————————————————–
Z
Z
Z

Function should not be called from DML statement, if function is selecting the same table.
SQL> ed
Wrote file afiedt.buf

1  update f1
2* set dummy=ft(dummy)
SQL> /
set dummy=ft(dummy)
*
ERROR at line 2:
ORA-04091: table HR.F1 is mutating, trigger/function may not see it
ORA-06512: at “HR.FT”, line 7

Let us restrict DML only to those row which function is not selecting, Then also it is not allowed.

SQL> ed
Wrote file afiedt.buf

1  update f1
2  set dummy=ft(dummy)
3* where dummy<>’Z’
SQL> /
set dummy=ft(dummy)
*
ERROR at line 2:
ORA-04091: table HR.F1 is mutating, trigger/function may not see it
ORA-06512: at “HR.FT”, line 7

All these above function restriction is imposed to maintain the consistency of data.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: