Answer Posted / abhishekjaiswal

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

Is coalesce faster than isnull?

966

---

define sql insert statement ? : Sql dba

1030

---

Do we need to create index on primary key?

929

---

What are the parts of a basic sql query?

967

---

What is dynamic query?

976

---

What does subquery mean in sql?

1056

---

What is a left join?

954

---

what is datawarehouse? : Sql dba

1017

---

What does an inner join do?

1182

---

Can a view be mutating? If yes, then how?

1028

---

What is the use of %rowtype?

1041

---

What is clustered index sql?

984

---

What is the purpose of the partition table?

1049

---

How to Execute a Package in PL/SQL.?

1205

---

What does rownum mean in sql?

1025

---