Member Avatar for 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!!!

  • 3 Contributors
  • 2 Replies
  • 2K Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by debasisdas
Member Avatar for 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;
/

Member Avatar for debasisdas

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.