DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: PLS-00306 Error: Wrong number of types of arguments in call to procedure

  1. #1
    Join Date
    Dec 2005
    Posts
    39

    PLS-00306 Error: Wrong number of types of arguments in call to procedure

    I have been working to resolve this for 2 days. I have a package
    specification and body that compile successfully. I have a file
    containing a call to the procedure that is in the package body. This file has the variables listed first and then the EXEC statement listed next. It is very long, so I won't take up space to print the whole thing here, but here is a glimpse of what it looks like:

    VAR v_vis_c_fname VARCHAR2(10)
    VAR v_vis_c_lname VARCHAR2(15)
    [more variables listed here]...

    EXEC load_names_p.pr_load_names (:v_vis_c_fname, :v_vis_c_lname, [more variables listed here]...)

    I get the PLS-00306 error when I execute the command to call the
    procedure. I am executing this file from SQL*Plus using the @call_procedures command (call_procedures is the name of the file that contains the code above.

    I have checked over and over again to see if any of the variables in
    the calling procedure file above are misspelled or missing. I even took
    all the variables from the actual procedure in the package body, the
    declaration section of the file above that calls the procedure, and the variables listed after the EXEC statement in the file above that calls theprocedure, put them in an excel worksheet, looked at them side-by-side, and they are all identical.

    I also checked the data type of each variable in the declaration
    section of the file above that calls the procedure and compared them with their associated columns in the database tables, and they are all correct.

    The code in the procedure is rather simple at this point, but is many pages long, so I don't want to paste it all here. But suffice it to say that all this procedure does at this point is select values from tables into the variables that i've declared.

    If all of my variables are listed correctly in the file above that calls
    the procedure, and each of their data types is also correct, can
    anyone think of any other reason why I would get a "PLS-00306 Error: Wrong number of types of arguments in call to procedure" error
    message? What else could it be? I am at my wits end!

    Thank you,
    Alan

  2. #2
    Join Date
    Dec 2005
    Posts
    39
    Per my last message... Here is what my procedure looks like (it's
    too long to paste the whole thing, but it basically just repeats
    what is below with different variables):

    CREATE OR REPLACE PACKAGE BODY load_names_p AS
    PROCEDURE pr_load_names (
    v_vis_c_fname OUT emps.c_fname%TYPE,
    v_vis_c_lname OUT emps.c_lname%TYPE,
    [etc]...

    IS
    BEGIN
    BEGIN
    BEGIN
    SELECT c_fname INTO v_vis_c_fname FROM emps
    WHERE week_no=1 and dept='sales';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

    BEGIN
    BEGIN
    SELECT c_fname INTO v_vis_c_lname FROM emps
    WHERE week_no=1 and dept='sales';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

    [and so on...]
    END pr_load_names;
    END load_names_p;

    Everything is on a local machine.

  3. #3
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    I would start by writing a PL/SQL test harness for the procedure rather than using SQL*Plus bind variables. Write a small PL/SQL block of the form:

    declare
    v_vis_c_fname emps.c_fname%TYPE;
    v_vis_c_lname emps.c_lname%TYPE;
    ...
    begin
    load_names_p.pr_load_names (v_vis_c_fname, v_vis_c_lname, [more variables listed here]...);
    end;

    Get that working, then selectively replace each variable with its bind equivalent until you can track down the problem. Also, make sure you are not inadvertantly using two different user IDs - e.g. you could be compiling the package under one ID but running it under a different one (which has its own version of the package).

  4. #4
    Join Date
    Dec 2005
    Posts
    39
    Scorby, thanks for your help. I wrote the PL/SQL test harness as you suggested and got it to work:

    declare
    v_vis_c_fname emps.c_fname%TYPE;
    v_vis_c_lname emps.c_lname%TYPE;
    ...
    begin
    load_names_p.pr_load_names (v_vis_c_fname, v_vis_c_lname, [more variables listed here]...);
    end;

    Then I began putting colons in front of the variables in the call to the procedure:

    load_names_p.pr_load_names (:v_vis_c_fname,:v_vis_c_lname,...)

    There are 168 varibles in the parentheses. As you suggested, I kept adding the colons in front of the variables, I got an error message on the 140th variable, which is v_vis_inn2_rfb_pts.

    load_names_p.pr_load_names (...:v_vis_inn1_rfb_pts,:v_vis_inn2_rfb_pts,v_vis_inn3_rfb_pts...)

    When I executed this, I received:
    SP2-0552: Bind variable "V_VIS_INN2_RFB_PTS" not declared.

    I checked its spelling, etc, and can't find anything wrong with it. Then I took out the colon in front of v_vis_inn2_rfb_pts and put a colon in front of the next variable (v_vis_inn3_rfb_pts), like this (note where the colons are placed):

    load_names_p.pr_load_names (...:v_vis_inn1_rfb_pts,v_vis_inn2_rfb_pts,:v_vis_inn3_rfb_pts...)

    When I ran the file this time, I received this error msg:
    SP2-0552: Bind variable "V_VIS_INN3_RFB_PTS" not declared.

    This time it skipped over v_vis_inn2_rfb_pts (when it didn't have a colon preceding it) and gave me this error msg for the following variable v_vis_inn3_rfb_pts when I put a colon before it.

    Everything looks spelled correctly and have the correct data types. I don't understand. Can you tell from this what is wrong?

  5. #5
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    It's possible there is some sort of bug when it has to handle a large number of variables. A workaround might be to assign the PL/SQL variables to the bind variables before calling the package. Also put a dbms_output statement against each bind variable. If it hasn't been declared properly you should get an error here as well.

    Code:
    declare
    v_vis_c_fname emps.c_fname%TYPE;
    v_vis_c_lname emps.c_lname%TYPE;
    ...
    begin
    dbms_output.put_line(:v_vis_c_fname);
    v_vis_c_fname := :v_vis_c_fname;
    dbms_output.put_line(:v_vis_c_lname);
    v_vis_c_lname :=  :v_vis_c_lname;
    ..
    load_names_p.pr_load_names (v_vis_c_fname, v_vis_c_lname, [more variables listed here]...);
    end;

  6. #6
    Join Date
    Dec 2005
    Posts
    39
    I will try that, but I think I will eventually run into the same problem of handling a large number of varibales. One thing that I began to do and could not figure out was to, instead of using one large procedure, use several smaller procedures within the same package body, but in order to do this I would need to reference the variables from these smaller procedures from another procedure (that is, declare global variables that can be referenced from anywhere in the package body). But, if I use 2 or more procedures in the same package body, I don't know how to make the variables in one procedure available in other procedures. I think this would solve the problems that I'm having. Can you help me with this?

    This is an example of what I need to do:

    CREATE OR REPLACE PACKAGE BODY load_employees_p AS
    --load employees from DEPT1
    PROCEDURE pr_load_dept1 (
    v_emp1 OUT dept1.emps%TYPE,
    v_emp2 OUT dept1.emps%TYPE)
    IS
    BEGIN
    BEGIN
    BEGIN
    SELECT emp_name INTO v_emp1 FROM emps
    WHERE emp_no=1 and dept=1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

    BEGIN
    BEGIN
    SELECT emp_name INTO v_emp2 FROM emps
    WHERE emp_no=2 and dept=1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    END;
    END pr_load_dept1;

    --load employees from DEPT2
    PROCEDURE pr_load_dept2 (
    v_emp3 OUT dept2.emps%TYPE,
    v_emp4 OUT dept2.emps%TYPE)
    IS
    BEGIN
    BEGIN
    BEGIN
    SELECT emp_name INTO v_emp3 FROM emps
    WHERE emp_no=1 and dept=2;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

    BEGIN
    BEGIN
    SELECT emp_name INTO v_emp4 FROM emps
    WHERE emp_no=2 and dept=2;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    END pr_load_dept2;

    /*NOW THIS IS THE PART THAT I DON'T KNOW HOW TO DO. IN THE NEXT
    PROCEDURE, I NEED TO BE ABLE TO ACCESS v_emp1, v_emp2, v_emp3, and
    v_emp4 FROM THE PROCEDURES ABOVE.*/

    --Update temp table
    PROCEDURE pr_update_temp (
    v_week_no OUT week_no.temp%TYPE)
    IS
    BEGIN
    BEGIN
    BEGIN
    UPDATE temp SET emp_name=v_emp1 WHERE week_no=v_week_no and
    emp_no=1; --v_emp1 NEEDS TO BE REFERENCED FROM ANOTHER PROCEDURE
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    END pr_update_temp;
    END load_employees_p;

    How can I create this package body so that this last procedure can
    use variables from the 1st 2 procedures? I think the way I have it
    set up above is that all of the variables can only be accessed
    within their own procedures. Thus, it will fail when pr_update_temp attempts to reference v_emp1, which is in pr_load_dept1. How can I make v_emp1... v_emp4 need to be global within the package body?

  7. #7
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    If you want to use global variables, then all you have to do is declare them at the top of the package body. You can then have another procedure which sets them.

    Code:
    create or replace pacakge body as
    g_variable1  varchar2(50);
    g_variable2 varchar2(10);
    procedure set_vars (p_var1 in varchar2, p_var2 in varchar2) is 
    begin
    g_variable1 := p_var1;
    g_variable2 := p_var2;
    end;
    ...
    end;

  8. #8
    Join Date
    Dec 2005
    Posts
    39
    I modified my code by taking just one variable in a procedure and making it global as in your example. I put the global variable in the declaration section at the top of both the specification and body and they compiled fine.

    But when I attempted to set the global variable in the procedure like you did here:

    begin
    g_variable1 := p_var1;

    and then re-compiled the spb file, I got the following error msg:

    PLS-00371: at most one declaration for 'G_VARIABLE1' is permitted in the declaration section.

    Since I've declared this global variable only once in the specification and once in the package body, I do not know what this error msg is referring to.

  9. #9
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    No - you must either declare in the specification or the body, but not both. Package spec variables are different to package body variables. Package spec variables can be addressed by external procedures e.g.

    begin
    my_package.g_my_variable := 'x';
    end;

    whereas package body variables can only be referenced within the package itself.

  10. #10
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Possibly (wild guess) you are running into line length limitations within SQL*Plus. As a workaround you could try breaking the line into shorter ones, e.g:

    EXEC blah(:blah, :blah -
    , blah, :blah -
    , blah :blah );

    or replacing the SQL*Plus "EXECUTE" shortcut with the full PL/SQL "BEGIN ... END;" block syntax. Or of course reducing the number of parameters. Do you really need 168 of them?

    Making procedure parameters into global variables sounds like a bit of a hack to me.
    Last edited by WilliamR; 01-26-2006 at 08:07 PM.

Posting Permissions

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


Click Here to Expand Forum to Full Width