0

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

2
Contributors
2
Replies
5
Views
9 Years
Discussion Span
Last Post by Lost in Code...
0

That is because both the variables P_ID and P_NAME are IN type variables. So you cant asign value to a In type variable in a select into statment.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.