Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    Unanswered: SP2-0552 "Bind variable not declared" Error

    I'm having a problem with the calling procedure (see below). I have 5 procedure calls in this file. The first 3 work fine with no problems. On the last 2 calls, I'm getting an SP2-0552 "Bind variable 'V_VIS_INN1_FAN_PTS' not declared" on the call for procedure pr_load_hitting_stats and SP2-0552 Bind variable 'V_VIS_SP_WHIP9_ERA' not declared" on the call for procedure pr_load_pitching_stats. I have checked many times over to make sure that I have all the correct variables listed in the file that calls the procedures and that each of their data types are correct.

    At this point, in the specification I have all 5 procedures and
    their variables (parameters) declared, and in the package body the 5
    procedures and their variables (parameters) declared, and have some
    statements that select values from database tables into the defined
    variables. All of the variables are declared as OUT. All of the
    data types are correct.

    I don't have the space to paste everything, but I can past what I have for one of the procedure that's giving the SP2-0552 error (pr_load_hitting_stats). This is a sample from the specification:

    CREATE OR REPLACE PACKAGE play_games_p AS
    PROCEDURE pr_load_hitting_stats (
    v_vis_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE);
    -----------------------------------------------------------
    Here is a sample from the package body:

    CREATE OR REPLACE PACKAGE BODY play_games_p AS
    g_vis_inn1_fname VARCHAR2(10);
    g_vis_inn1_lname VARCHAR2(25);
    .
    .
    .
    PROCEDURE pr_load_hitting_stats (
    v_vis_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_vis_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
    v_home_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE)
    IS BEGIN
    BEGIN
    BEGIN
    SELECT fan_pts INTO v_vis_inn1_fan_pts FROM hitting_stats_weekly
    WHERE week_no='1' AND fname=g_vis_inn1_fname AND
    lname=g_vis_inn1_lname;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    .
    .
    .
    END pr_load_hitting_stats;
    END play_games_p;
    -----------------------------------------------------------------
    NOTE: g_vis_inn1_fname and g_vis_inn1_lname are global variables and obtained their values from an earlier procedure in this package.

    And here is the file that calls the procedures (the 1st 3
    procedures work with no problem; the last 2 procedures (including the one below) fail with the "SP2-0552 Bind variable not declared" errors:
    -----------------------------------------------------------------
    SET serveroutput on size 1000000
    SPOOL CALL_PROCEDURES.LOG
    @cre_msg.sql
    .
    .
    .
    PROMPT CALL play_games_p.pr_load_hitting_stats
    VAR v_vis_inn1_fan_pts NUMBER(3)
    VAR v_vis_inn2_fan_pts NUMBER(3)
    VAR v_vis_inn3_fan_pts NUMBER(3)
    VAR v_vis_inn4_fan_pts NUMBER(3)
    VAR v_vis_inn5_fan_pts NUMBER(3)
    VAR v_vis_inn6_fan_pts NUMBER(3)
    VAR v_vis_inn7_fan_pts NUMBER(3)
    VAR v_vis_inn8_fan_pts NUMBER(3)
    VAR v_vis_inn9_fan_pts NUMBER(3)
    VAR v_home_inn1_fan_pts NUMBER(3)
    VAR v_home_inn2_fan_pts NUMBER(3)
    VAR v_home_inn3_fan_pts NUMBER(3)
    VAR v_home_inn4_fan_pts NUMBER(3)
    VAR v_home_inn5_fan_pts NUMBER(3)
    VAR v_home_inn6_fan_pts NUMBER(3)
    VAR v_home_inn7_fan_pts NUMBER(3)
    VAR v_home_inn8_fan_pts NUMBER(3)
    VAR v_home_inn9_fan_pts NUMBER(3)

    EXEC play_games_p.pr_load_hitting_stats
    (:v_vis_inn1_fan_pts,:v_vis_inn2_fan_pts,:v_vis_in n3_fan_pts,:v_vis_i
    nn4_fan_pts,:v_vis_inn5_fan_pts,:v_vis_inn6_fan_pt s,:v_vis_inn7_fan_p
    ts,:v_vis_inn8_fan_pts,:v_vis_inn9_fan_pts,:v_home _inn1_fan_pts,:v_ho
    me_inn2_fan_pts,:v_home_inn3_fan_pts,:v_home_inn4_ fan_pts,:v_home_inn
    5_fan_pts,:v_home_inn6_fan_pts,:v_home_inn7_fan_pt s,:v_home_inn8_fan_
    pts,:v_home_inn9_fan_pts)

    PROMPT END CALL PROCEDURES SCRIPT
    SET serveroutput off
    SHOW ERRORS

    SPOOL OFF

    Any ideas?

  2. #2
    Join Date
    Jan 2006
    Posts
    4
    By the way, I also replaced the "hitting_stats_weekly.fan_pts%TYPE" with NUMBER in the specification and package body and got the same SP2-0552 error.

  3. #3
    Join Date
    Jan 2006
    Posts
    4
    Another thing that is peculiar is that for all 3 procedures that fail, the error occurs on the 5th variable listed. For example, in the call
    EXEC play_games_p.pr_load_hitting_stats
    (:v_vis_inn1_fan_pts,:v_vis_inn2_fan_pts,:v_vis_in n3_fan_pts,:v_vis_i
    nn4_fan_pts,:v_vis_inn5_fan_pts,:v_vis_inn6_fan_pt s,:v_vis_inn7_fan_p
    ts,:v_vis_inn8_fan_pts,:v_vis_inn9_fan_pts,:v_home _inn1_fan_pts,:v_ho
    me_inn2_fan_pts,:v_home_inn3_fan_pts,:v_home_inn4_ fan_pts,:v_home_inn
    5_fan_pts,:v_home_inn6_fan_pts,:v_home_inn7_fan_pt s,:v_home_inn8_fan_
    pts,:v_home_inn9_fan_pts)
    the error occurs on the 5th variable in parentheses (:v_vis_inn5_fan_pts)
    SP2-0552: Bind variable "V_VIS_INN5_FAN_PTS" not declared

    I'm getting this same error message on the 5th variable listed in the other 2 procedures that gives the SP2-0552 error. The only problem is that I have these varibales, as well as all the rest of my variables, declared. I've been working to resolve this issue for over a week now and have double and triple and quadruple-checked all of the variables and their data types.

    Another thing: I had the procedure listed in my original post (pr_load_hitting_fan_pts) working last night. Then this morning, without changing anything in that particular procedure, I'm getting the SP2-0552 error for it again.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code you posted is full of typos (spaces where they shouldn't be). Now, if this is the case in your real procedure, fix it; below is a code that *should* compile. If this doesn't help, try to post correct code; as far as I know, there is no reason why would 3 procedures work correctly and fourth wouldn't if nothing changed at all.
    Code:
    BEGIN
       play_games_p.pr_load_hitting_stats (:v_vis_inn1_fan_pts,
                                           :v_vis_inn2_fan_pts,
                                           :v_vis_inn3_fan_pts,
                                           :v_vis_inn4_fan_pts,
                                           :v_vis_inn5_fan_pts,
                                           :v_vis_inn6_fan_pts,
                                           :v_vis_inn7_fan_pts,
                                           :v_vis_inn8_fan_pts,
                                           :v_vis_inn9_fan_pts,
                                           :v_home_inn1_fan_pts,
                                           :v_home_inn2_fan_pts,
                                           :v_home_inn3_fan_pts,
                                           :v_home_inn4_fan_pts,
                                           :v_home_inn5_fan_pts,
                                           :v_home_inn6_fan_pts,
                                           :v_home_inn7_fan_pts,
                                           :v_home_inn8_fan_pts,
                                           :v_home_inn9_fan_pts
                                          );
    END;

  5. #5
    Join Date
    Jan 2006
    Posts
    4
    Hi, thanks for your reply. I may not have been clear. The specification and package body is compiling fine. I get the error messages when I run the file that executes the procedures in the package body.

    I did try to change the file that calls the procedures into the format that you suggested, but it gave the following error messages:
    SP2-0552: Bind variable "V_VIS_INN1_FAN_PTS" not declared
    SP2-0734: unknown command beginning ":v_vis_inn..." - rest of line ignored

    The SP2-0734 error msg occurred 17 times, one time for each line that began with :v_...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is a short example of what you are trying to do (actually, what I *think* you want). If you compare your solution and my example, is there any obvious difference?
    Code:
    create or replace package play_games as
      procedure pr_load (v_in1 out number,
                         v_in2 out number,
                         v_in3 out number,
                         v_in4 out number,
                         v_in5 out number,
                         v_in6 out number);
    end;
    /
    
    create or replace package body play_games as
      procedure pr_load (v_in1 out number,
                         v_in2 out number,
                         v_in3 out number,
                         v_in4 out number,
                         v_in5 out number,
                         v_in6 out number)
      is
      begin
        select count(*) into v_in1 from tab;
        select count(*) into v_in2 from tab;
        select count(*) into v_in3 from tab;
        select count(*) into v_in4 from tab;
        select count(*) into v_in5 from tab;
        select count(*) into v_in6 from tab;
      end;
    end;
    /
    To test it, use such a script (try_me.sql):
    Code:
    var v_in1 number;
    var v_in2 number;
    var v_in3 number;
    var v_in4 number;
    var v_in5 number;
    var v_in6 number;
    
    exec play_games.pr_load(:v_in1, :v_in2, :v_in3, :v_in4, :v_in5, :v_in6);
    
    select :v_in1, :v_in2, :v_in3, :v_in4, :v_in5, :v_in6 from dual;
    Code:
    SQL> @try_me
    
    PL/SQL procedure successfully completed.
    
    
        :V_IN1     :V_IN2     :V_IN3     :V_IN4     :V_IN5     :V_IN6
    ---------- ---------- ---------- ---------- ---------- ----------
             6          6          6          6          6          6
    
    SQL>

Posting Permissions

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