Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Unanswered: Collections as arguments

    I would like to know if it be possible to pass collections to procedures or functions.

    If so, do you have an example?
    Cordialement

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Collections as arguments

    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?
    Yes. Example:

    Code:
    SQL> create type ntab_type as table of number;
      2  /
    
    Type created.
    
    SQL> create or replace procedure p( x in ntab_type ) is
      2  begin
      3    for i in 1..x.count loop
      4      dbms_output.put_line(x(i));
      5    end loop;
      6  end;
      7  /
    
    Procedure created.
    
    SQL> set serverout on
    SQL> exec p( ntab_type(1,2,3) )
    1
    2
    3
    
    PL/SQL procedure successfully completed.

  3. #3
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Re: Collections as arguments

    Thanks for the confirmation.

    The problem is then, in the declaration of procedures and functions in the package specification.

    There are functions that I would like to keep 'private'. However, on compilation, the error PLS-00306 is raised for some of these functions.

    To overcome this error, I must declare the function in the package specification.

    The question is:

    "In which case must I declare the function in the package specification?"
    Cordialement

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Collections as arguments

    You should not have to make any function public if you don't want to.
    Can you demonstrate the problem with an example?

  5. #5
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Re: Collections as arguments

    Originally posted by andrewst
    You should not have to make any function public if you don't want to.
    Can you demonstrate the problem with an example?
    The screen shows that the error is raised (not the first time to-day). This is directly related to the declaration of the function in the package specification.

    If I redeclare the function, the error goes away and all is well. Note that this function takes a user-defined type (table).

    Some other functions do not need the declaration.
    Attached Thumbnails Attached Thumbnails screen1.bmp  
    Cordialement

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Collections as arguments

    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.

  7. #7
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Re: Collections as arguments

    The function is indeed defined after the definition of the procedure that calls it.

    If this causes errors, it must be for that reason.

    Great, Tony.
    Cordialement

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Collections as arguments

    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:

    Code:
    PACKAGE BODY pkg IS
    
      -- Forward declaration
      FUNCTION square ( p1 IN NUMBER ) RETURN NUMBER;
    
      PROCEDURE p IS
        v NUMBER;
      BEGIN
        v := square(2);
      END;
    
      FUNCTION square ( p1 IN NUMBER ) RETURN NUMBER
      IS
      BEGIN
        RETURN p1*p1;
      END;
    
    END;
    /

  9. #9
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Re: Collections as arguments

    The forward declaration fixed it well.

    Thanks, again.
    Cordialement

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •