Bind Variable Not Declared??

[bondo]

Ok, so I'm learning about procedures and functions in Oracle and I'm stuck here with my procedure. I have the function part down pretty good, but the whole returning variables with procedures through OUT mode is confusing me. My task is this: "Write a procedure that accepts a zipcode & returns the city & state for that value as well as a "status" parameter whose value 'ok' or 'nok' includes whether or not the request was satisfied."

So here is the procedure I wrote:

CREATE OR REPLACE PROCEDURE getCityState
(v_zipcode IN location.zipcode%type,
 v_city OUT location.city%type,
 v_state OUT location.state%type,
 v_status OUT varchar2)
AS
BEGIN
    SELECT city, state INTO v_city, v_state
    FROM location
    WHERE zipcode = v_zipcode;
    IF SQL%FOUND THEN
        v_status := 'ok';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    IF SQL%NOTFOUND THEN
        v_status := 'nok';
    END IF;
    WHEN others THEN
        v_status := 'nok';
END getCityState;

And I'm trying to call the procedure, but I don't think I'm doing it correctly and I haven't found anything that's too terribly helpful for my dilemma. Here is my calling statement:

declare
v_city location.city%type := 0;
v_state location.state%type := 0;
v_status varchar2 := 0;
begin
getCityState(99004, :v_city, :v_state, :v_status);
end;
/

I continue to get several different errors depending on how I structure the statement, but with this particular structure I am getting a "Bind variable "v_status" not declared" error. Can anyone help me and tell me what I am doing wrong, or give me a link to a site or something that can explain this a bit better to me? The lame-ass text book I have is a total piece of garbage and my instructor isn't exactly the kind of fellow who is pron to helping.

Thanks in advance!!!

[yilmazhuseyin]

Hi! you are calling local variable the way you should call bind variables. so oracle things your variables are bind variables. you should not use ':' sign before variable names. anyway you should write it as

declare
v_city location.city%type := 0;
v_state location.state%type := 0;
v_status varchar2 := 0;
begin
getCityState(99004, v_city, v_state, v_status);
end;
/

[debasisdas]

you need not use bind variables for that. just try to use as suggested in the previous post.