Friday, July 20, 2012

Functions cannot be invoked from Select Query


Functions cannot be invoked from Select Query : if the function is having any ddl/dml operations inside it.

Example :


create or replace function xx_temp_fun(p_deptno number)
return char
is
begin
insert into xx_dept(deptno,dname) values (p_deptno,'Bench') ;
commit;
return 'x';
end ;
select xx_temp_fun(21) from dual; --> this will give you an error. as the function written above is having DML operations inside it.
The follow works without any error.
declare
v_char varchar2(10);
begin
v_char := xx_temp_fun(19);
end;



Post a Comment