Workaround the DMLs: PL/SQL function restrictions

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.

Comments:

blah blah

Posted by Blah Jamia on July 14, 2009 at 11:03 PM IST #

1. IMHO it's hard to read your examples. Very hard. 2. Show all restrictions at first and then describe them examples. 3.
    select CHR(ASCII('A')+level-1),Level from dual
    connect by level  < 10;

or do as Tom 
select * from user_objects where  rownum  < 100;

4. May be use links for Oracle Documentation at the end of your article?
 

                

Posted by August on July 16, 2009 at 10:55 AM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

What I learned about Oracle

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today