Bind Variable Not Declared??
 

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!!!

Re: Bind Variable Not Declared??
 

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;
/

Re: Bind Variable Not Declared??
 

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