Q

ORA-03001 error with dynamic query

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."

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;
/

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close