Originally posted by Crassus
I would like to know if it be possible to pass collections to procedures or functions.
If so, do you have an example?
SQL> create type ntab_type as table of number;
SQL> create or replace procedure p( x in ntab_type ) is
3 for i in 1..x.count loop
5 end loop;
SQL> set serverout on
SQL> exec p( ntab_type(1,2,3) )
PL/SQL procedure successfully completed.
Is F_CREATE_SORT_CLAUSE defined in the package body before the point where it is used? Is there more than one (overloaded) definition of the function? Without seeing the entire package body it is difficult to comment.
However, I have certainly created package bodies with private functions that take user-defined types as arguments, without this problem.
Looking again at your screenshot, I see that F_CREATE_SORT_CLAUSE appears to be defined at the end of the package body. In that case, you must either move it up above P_VERIFY, or create a forward declaration - i.e. a declaration just like you would put in the package specification, but in the package body. Example:
PACKAGE BODY pkg IS
-- Forward declaration
FUNCTION square ( p1 IN NUMBER ) RETURN NUMBER;
PROCEDURE p IS
v := square(2);
FUNCTION square ( p1 IN NUMBER ) RETURN NUMBER