Create a package that uses packaged variables to assist in the user logon proces

Question

Create a package that uses packaged variables to assist in the user logon process. When a returning shopper logs on, the username and password entered will need to be verified against the database. In addition, two values need to be stored in packaged variables for reference during the user session: the shopper ID and the first three digits of the shopper's zip code (used for regional advertisements displayed on the site).

Create a function that accepts a username and password as arguments and verifies these values against the database for a match. If a match is found, return the value Y. Set the value of the variable holding the return value to N. Include a NO_DATA_FOUND exception handler to display a message that the logon values are invalid.

Use an anonymous block to test the procedure, using the username gma1 and the password goofy.

Now place the function in a package, and add code to create and populate the packaged variables specified earlier. Name the package LOGIN_PKG.

Use an anonymous block to test the packaged procedure, using the username gma1 and the password goofy to verify that the procedure works correctly.

Use DBMS_OUTPUT statements in an anonymous block to display the values stored in the packaged variables.

Explanation / Answer

-- create the function create or replace function verify_user (usernm in varchar2, passwd in varchar2) return char is temp_user bb_shopper.username%type; confirm char(1) := 'N'; begin -- if this select succeed, we can return Y select username into temp_user from bb_shopper where password = passwd; confirm := 'Y'; return confirm; exception -- if it fails, return N WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('logon values are invalid'); end; / -- test w/ host variables variable g_ck char(1); begin :g_ck := verify_user('gma1', 'goofy'); end; / -- it worked! print g_ck / -- make it a package this time create or replace package login_pckg is function verify_user (usernm in varchar2, passwd in varchar2) return char; end; / -- body of the package create or replace package body login_pckg is function verify_user (usernm in varchar2, -- everything in the function is the same passwd in varchar2) return char is temp_user bb_shopper.username%type; confirm char(1) := 'N'; begin select username into temp_user from bb_shopper where password = passwd; confirm := 'Y'; return confirm; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('logon values are invalid'); end verify_user; end; / -- host variable variable g_ck char(1); -- test, asignment and output in one block for convenience begin :g_ck := login_pckg.verify_user('gma1', 'goofy'); dbms_output.put_line(:g_ck); -- it worked! end; /

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.