Greetings,
I'm having a heck of a time trying to get this pl/sql program to work. I'm to invoke it using shopper id and then again by shopper last name. I've banged my head on this for a while now and it seems that every change I make creates more problems. Here is my code, and i'll also include the error message. thanks
CREATE OR REPLACE PACKAGE shop_query_pkg IS
PROCEDURE shopper_search_pp
(P_id IN bb_shopper.IDSHOPPER%TYPE,
p_name OUT bb_shopper.LASTNAME%TYPE,
p_city OUT bb_shopper.CITY%TYPE,
p_state OUT bb_shopper.STATE%TYPE,
p_pnum OUT bb_shopper.PHONE%TYPE,
p_email OUT bb_shopper.EMAIL%TYPE);
PROCEDURE shopper_search_pp
(p_name IN bb_shopper.LASTNAME%TYPE,
p_id OUT bb_shopper.IDSHOPPER%TYPE,
p_city OUT bb_shopper.CITY%TYPE,
p_state OUT bb_shopper.STATE%TYPE,
p_pnum OUT bb_shopper.PHONE%TYPE,
p_email OUT bb_shopper.EMAIL%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY shop_query_pkg
IS
PROCEDURE shopper_search_pp
(p_id IN bb_shopper.IDSHOPPER%TYPE,
p_name OUT bb_shopper.LASTNAME%TYPE,
p_city OUT bb_shopper.CITY%TYPE,
p_state OUT bb_shopper.STATE%TYPE,
p_pnum OUT bb_shopper.PHONE%TYPE,
p_email OUT bb_shopper.EMAIL%TYPE)
IS
BEGIN
SELECT LASTNAME, CITY, STATE, PHONE, EMAIL
INTO p_id, p_name, p_city, p_state, p_pnum, p_email
FROM bb_shopper
WHERE IDSHOPPER = p_id;
END;
PROCEDURE shopper_search_pp
(p_name IN bb_shopper.LASTNAME%TYPE,
p_id OUT bb_shopper.IDSHOPPER%TYPE,
p_city OUT bb_shopper.CITY%TYPE,
p_state OUT bb_shopper.STATE%TYPE,
p_pnum OUT bb_shopper.PHONE%TYPE,
p_email OUT bb_shopper.EMAIL%TYPE)
IS
BEGIN
SELECT IDSHOPPER, CITY, STATE, PHONE, EMAIL
INTO p_name, p_id, p_city, p_state, p_pnum, p_email
FROM bb_shopper
WHERE LASTNAME = p_name;
END;
END;
/show errors
Errors for PACKAGE BODY SHOP_QUERY_PKG:
LINE/COL
ERROR
12/4
PL/SQL: SQL Statement ignored
13/9
PLS-00403: expression 'P_ID' cannot be used as an INTO-target of a SELECT/FETCH statement
14/4
PL/SQL: ORA-00913: too many values
26/6
PL/SQL: SQL Statement ignored
27/9
PLS-00403: expression 'P_NAME' cannot be used as an INTO-target o f a SELECT/FETCH statement
28/4
PL/SQL: ORA-00913: too many values
BTW, what do you use in the wrap code fields? I tried SQL and oracle. thanks
