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

How do I get sql certification?

1013

---

How many indexes can be created on a table in sql?

1107

---

What are the string functions in sql?

1015

---

what is an alias command? : Sql dba

1165

---

What makes a good primary key?

1093

---

What are schema-level triggers?

1148

---

What is pl sql block structure?

1082

---

Explain spool.

1214

---

Is inner join faster than left join?

1246

---

Is there a pl/sql pragma similar to deterministic, but for the scope of one single sql select?

1058

---

How do I create a memory optimized filegroup?

1007

---

what is subquery? : Sql dba

1182

---

How does postgresql compare to "nosql"?

1077

---

What is difference between group by and partition by?

1118

---

What is ttitle and btitle?

1108

---