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