Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1905
  • Last Modified:

Bad Bind Variable in procedure compilation

I get a bad bind variable error on "g_date" after doing this:


set serveroutput on
variable g_date varchar2 (11);

create or replace procedure status_sp
(p_idbasket IN bb_basketstatus.idbasket%TYPE)
IS
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;
/

why? and, is there a list of what Oracle error messages mean somewhere on the web? All I could find with Google was confused newbie posts like this one. Thanks!
0
Sapphireblue
Asked:
Sapphireblue
  • 4
  • 3
2 Solutions
 
jrb1Commented:
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;
/
0
 
SapphireblueAuthor Commented:
I am using a bind variable because my class assignment tells me to, I'm afraid.
0
 
jrb1Commented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jrb1Commented:
Oops...easier than I thought:

http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html

We declare a bind variable as follows:
     VARIABLE <name> <type>
where the type can be only one of three things: NUMBER, CHAR, or CHAR(n).

You can't define it as a varchar.
0
 
SapphireblueAuthor Commented:
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;
/
0
 
jrb1Commented:
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.
0
 
earth man2Commented:
-- the variable statement is a sqlplus command telling sqlplus environment to create a LOCAL store for gdate.
variable g_date varchar2 (11);

-- the create or replace procedure status_sp statement tells the SERVER to create a stored procedure within the database.
-- thus it is blithely unaware of g_date which is in memory on some sqlplus client on some computer on the network.

g_date and status_sp_statement have different SCOPE.  If you need to pass g_date as a parameter to your procedure, then you must dump the colon.  Alternatively you can use an anonymous pl/sql block instead of a stored procedure.

set serveroutput on
variable g_date varchar2(11);

declare
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;
/
0
 
SapphireblueAuthor Commented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now