can we call procedure inside a function in PL/SQL?

Question

we can call the function inside the procedure , but is it possible to call procedure inside the function ? I tried but I can't call procedure inside the function. could you tell me why we can not call the procedure inside the function?

Answer 1

create or replace function test_fun(id in number) return number 
as 
val number;
begin 
get_data(id,val);
return val;
end;

create or replace procedure get_data(a in number ,b out number)
as
id number; 
begin
b:=a*a;
end;

Answer 2

My guess is that you are using call proc or exec proc. See below an example how to call the procedure.

CREATE OR REPLACE function f() return number as
BEGIN
  your_proc;
  another_proc_with_param(2, 'John');
  return 0;
EXCEPTION when others then return -1;
END f;

However, if your function(or procedures called by your function) does DML, your function can't be used in sql statements.(Can be used only in PLSQL blocks).

Answer 3

I'm going to take a guess here that you have the function declared first, with the procedure following, similar to:

DECLARE
  FUNCTION my_func RETURN NUMBER IS
  BEGIN
    RETURN 2;
  END my_func;

  PROCEDURE my_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(my_func + 1);
  END my_proc;

BEGIN  -- main
  my_proc;
END;    -- main

As shown above, with the function declared first you can call the function from the procedure. However, if you try something like the following (function declared before procedure, and function calls procedure):

DECLARE
  FUNCTION my_func RETURN NUMBER IS
  BEGIN
    my_proc;
    RETURN 2;
  END my_func;

  PROCEDURE my_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('22');
  END my_proc;

BEGIN  -- main
  DBMS_OUTPUT.PUT_LINE(my_func);
END;    -- main

the compile will fail, because my_func cannot 'see' my_proc. To make it work you need to put in a 'prototype' declaration of my_proc, as follows:

DECLARE
  PROCEDURE my_proc;

  FUNCTION my_func RETURN NUMBER IS
  BEGIN
    my_proc;
    RETURN 2;
  END my_func;

  PROCEDURE my_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('22');
  END my_proc;

BEGIN  -- main
  DBMS_OUTPUT.PUT_LINE(my_func);
END;    -- main

Share and enjoy.

Answer 4

" I tried but I can't call procedure inside the function."

How did you try? What did you try? In what way did you fail?

Because it is permitted to call procedure inside the function. So if it isn't working for you, then the cause is something wrong in your code. We cannot possibly diagnose that without you providing a lot more information than you currently have.

The two most likely reasons are:

1. You have a syntax error in your code which is preventing it from compiling, or some other bug which is hurling a runtime exception.

2. The function might be in scope of the procedure but not vice versa.

3. Your procedure is doing something which is not allowed when we call a function in a query (such as issuing DML) and you are calling your function in a SELECT statement.

Answer 5

--------------------------procedure inside function-----------------------------

create or replace function f_2  return date as

begin

declare

today_date date;

x number; 

-------procedure declaration-----------------

procedure pro_3(d_date out date )

is

begin

d_date:=sysdate;

end pro_3;

BEGIN 

---------procedure called--------------------

pro_3(today_date);

for x in 1..7 LOOP 

IF TO_CHAR(today_date,'FMDAY')='SUNDAY' THEN 

GOTO label_name; 

END IF; 

today_date:=today_date+1; 

END LOOP; 

<<label_name>> 

DBMS_OUTPUT.PUT_LINE(TO_CHAR(today_date,'DAY')||today_date);

end;

DBMS_OUTPUT.PUT_LINE('today is  ' ||TO_CHAR(sysdate,'DAY'));

return sysdate;

end;

----------------------------------execution---------------------------------

exec dbms_output.put_line(f_2);