Bad Bind Variable in procedure compilation

Why are you using a bind variable?  What's wrong with this?

create or replace procedure status_sp
(p_idbasket IN bb_basketstatus.idbasket%TYPE)
IS
g_date varchar2(11);
lv_idstage bb_basketstatus.idstage%TYPE;
lv_dtstage bb_basketstatus.dtstage%TYPE;
BEGIN
select idstage,dtstage into lv_idstage,lv_dtstage from bb_basketstatus where idbasket = 3 AND dtstage IN (
     select max(dtstage) from bb_basketstatus WHERE idbasket = 3);
g_date := TO_CHAR(lv_dtstage);
case when lv_idstage = 1 then DBMS_OUTPUT.PUTLINE('Status: Submitted and received on ' || g_date);
when lv_idstage = 2 then DBMS_OUTPUT.PUTLINE('Status: Confirmed, processed, sent to shipping on ' || g_date);
when lv_idstage = 3 then DBMS_OUTPUT.PUTLINE('Status: Shipped on ' || g_date);
when lv_idstage = 4 then DBMS_OUTPUT.PUTLINE('Status: Cancelled on ' || g_date);
when lv_idstage = 5 then DBMS_OUTPUT.PUTLINE('Status: Backordered on ' || g_date);
else DBMS_OUTPUT.PUTLINE('No status is available.');
end case;
end;
/

I am using a bind variable because my class assignment tells me to, I'm afraid.

No problem.  Just an FYI on the rules here...from the FAQ

"Most commonly, this means that Experts are prohibited from doing your homework for you. They'll guide you and teach you, but don't ask them to write code to answer a question that seems like it was written for a test. It should be noted that just like bribery, while it's bad to offer a bribe, it's worse to take it -- so don't do homework."

I'm looking at this now...will give you a hint as to the problem in a bit.

I appreciate your sensitivity to the rules. However, when I am asking you to do my homework for me---as opposed to help me understand why the homework I have done is not working---you'll know it.

as for no varchars, I'll try it, but am not sure that's the answer, since this one (that I wrote... as homework... all by myself...) works fine:


variable g_check varchar2(10)
variable g_pass varchar2(40)
begin
:g_pass := 'kile';
end;
/

create or replace procedure member_ck_sp
(p_userid IN bb_shopper.username%TYPE,
p_password IN OUT bb_shopper.password%TYPE,
p_check OUT varchar2)
IS
BEGIN
SELECT Firstname || ' ' || Lastname || ' ' || Cookie
INTO p_password FROM bb_shopper WHERE username = p_userid AND password = p_password;
DBMS_OUTPUT.PUT_LINE(p_password);
EXCEPTION
WHEN NO_DATA_FOUND THEN p_check := 'Invalid'; DBMS_OUTPUT.PUT_LINE(p_check);
END;
/

OK, I'll look again in the morning.  I can tell you're not trying to get your homework done for you..and certainly don't mind helping.

OK, the solution was something different. (earthman, the terms of my homework question also dictated that it be a stored procedure, not an anonymous block.) I think the deal is (I'm new at this, clearly) that I can't change the value of the bind variable directly within the body of my procedure. Instead, I have to define an OUT parameter for the procedure, to hold the value I will want to set the bind variable to, and then when I call the procedure, pass the name of the bind variable I want to change as the value for the OUT parameter.

So the whole thing looks like this:

SQL> create or replace procedure status_sp
  2  (p_idbasket IN bb_basketstatus.idbasket%TYPE,
  3  p_date OUT varchar2)
  4  IS
  5  lv_idstage bb_basketstatus.idstage%TYPE;
  6  lv_dtstage bb_basketstatus.dtstage%TYPE;
  7  BEGIN
  8  select idstage,dtstage into lv_idstage,lv_dtstage from bb_basketstatus where idbasket = 3 AND dtstage IN (
  9  select max(dtstage) from bb_basketstatus WHERE idbasket = 3);
 10  p_date := TO_CHAR(lv_dtstage);
 11  case when lv_idstage = 1 then DBMS_OUTPUT.PUT_LINE('Status: Submitted and received on ' || p_date);
 12  when lv_idstage = 2 then DBMS_OUTPUT.PUT_LINE('Status: Confirmed, processed, sent to shipping on ' || p_date);
 13  when lv_idstage = 3 then DBMS_OUTPUT.PUT_LINE('Status: Shipped on ' || p_date);
 14  when lv_idstage = 4 then DBMS_OUTPUT.PUT_LINE('Status: Cancelled on ' || p_date);
 15  when lv_idstage = 5 then DBMS_OUTPUT.PUT_LINE('Status: Backordered on ' || p_date);
 16  else DBMS_OUTPUT.PUT_LINE('No status is available.');
 17  end case;
 18  end;
 19  /

Procedure created.

SQL> execute status_sp(4,:g_date);

... and then everything works fine.

I'm just going to split the points between jrb1 and earthman2 for their effort. Thanks.