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