Oracle

Bind Variable Not Declared??

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2007

Posts: 37

Reputation: bondo is an unknown quantity at this point

Solved Threads: 0

bondo

Offline

Light Poster

Bind Variable Not Declared??

Oct 24th, 2008

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:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
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;
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:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
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;
/
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!!!

•

Join Date: Oct 2006

Posts: 48

Reputation: yilmazhuseyin is an unknown quantity at this point

Solved Threads: 5

yilmazhuseyin yilmazhuseyin is offline

Offline

Light Poster

Re: Bind Variable Not Declared??

Oct 24th, 2008

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