I am working in Oracle 10g. I am using a
dynamic query (with the help of EXECUTE IMMEDIATE) in the function and within the dynamic SQL I am calling another function created by me. But when I execute the function I am getting an error: "ORA-03001 unimplemented feature." Can anybody help me with this?
I have two functions. The first function is called "get_fiscal_year." This function returns a varchar2. The second function is called "test_execute." In the test_execute function I am using an execute immediate command to call the function get_fiscal_year. Both functions work as expected. Although I am using Oracle 9i release 2, these functions should work the same in 10g.
CREATE OR REPLACE function get_fiscal_year(p_date date default sysdate)
return varchar2 is
v_year varchar2(4);
v_quarter varchar2(1);
begin
if to_char(p_date,'mm') between '01' and '09' then
v_year := to_char(p_date,'yyyy');
else
v_year := to_char(p_date,'yyyy')+1;
end if;
case to_char(p_date,'q')
when '1' then
v_quarter := '2';
when '2' then
v_quarter := '3';
when '3' then
v_quarter := '4';
when '4' then
v_quarter := '1';
end case;
return (v_year||':'||v_quarter);
end;
/
CREATE OR REPLACE function test_execute return varchar2
as
statement varchar2(500);
v_year_quarter varchar2(10);
begin
statement := 'select get_fiscal_year(sysdate)from dual';
execute immediate statement into v_year_quarter;
return v_year_quarter;
end;
/