Creating a package

Question

I am trying to Create a package MSGG_SESSION with a procedure authenticate that accepts two VARCHAR2 parameters for username and password. Put an package-private NUMBER variable for the current person ID. If “authenticate” matches a username and password in MSGG_USER , put the matching PERSON_ID in the new variable. when i put this into oracle sql developer i get and error. i already created a table MSGG_SESSION and a MSGG_USER table already exists. (PRIV-NUMBER is my package private number variable for the current person ID)

CREATE OR REPLACE PACKAGE MSGG_SESSION
IS

    PROCEDURE AUTHENTICATE (USERNAME IN VARCHAR2, PASSWORD IN VARCHAR2);
    FUNCTION AUTHENTICATED_USER RETURN VARCHAR2;
END MSGG_SESSION;
/

CREATE OR REPLACE PACKAGE BODY MSGG_SESSION
IS
    PRIV_NUMBER VARCHAR2(100);

    PROCEDURE AUTHENTICATE (USERNAME_TO_AUTH IN VARCHAR2, PASSWORD_TO_USE IN VARCHAR2)
    IS
    BEGIN
        PRIV_NUMBER := NULL;
        SELECT USERNAME
            INTO PRIV_NUMBER
        FROM
            USER_PASSWORD
        WHERE
            lower(username) = lower(username_to_auth) and password = password_to_use;
    exception
        when NO_DATA_FOUND then
            raise NOT_AUTHENTICATED;
        when others then
            raise;
    END AUTHENTICATE;

    FUNCTION GET_USER_ID
    RETURN VARCHAR2
    is
    begin
        return priv_number;
    end get_user_id;

END MSGG_SESSION;
/

Never store passwords as plaintext. Even if this is only a homework question, it's good to learn now that this code is completely unacceptable. — Jon Heller, Sep 19 '19 at 00:18

Littlefoot · Answer 1 · 2019-09-19T17:38:51.473

A few things you did wrong:

procedures declared in package specification MUST match those in the package body in name, number and datatypes of their parameters
if you are raising an exception, you have to declare it

See comments within the code:

Specification:

SQL> CREATE OR REPLACE PACKAGE MSGG_SESSION IS
  2    -- parameter names must be changed so that they match those in package body, which
  3    -- is a better option as table columns are named USERNAME and PASSWORD
  4    PROCEDURE AUTHENTICATE (USERNAME_to_auth IN VARCHAR2, PASSWORD_to_use IN VARCHAR2);
  5    FUNCTION AUTHENTICATED_USER RETURN VARCHAR2;
  6  END MSGG_SESSION;
  7  /

Package created.

Body: why do you raise your own exception if nothing is found? I mean, what's wrong with NO_DATA_FOUND itself? What benefit does NOT_AUTHENTICATED exception brings?

SQL> create or replace package body msgg_session is
  2    priv_number varchar2(100);
  3
  4    procedure authenticate (username_to_auth in varchar2, password_to_use in varchar2)
  5    is
  6      -- you didn't declare it
  7      not_authenticated exception;
  8    begin
  9      select username
 10          into priv_number
 11        from user_password
 12      where lower(username) = lower(username_to_auth)
 13        and password = password_to_use;
 14    exception
 15      when no_data_found then
 16        -- now goes the clumsy part of code
 17        begin
 18          raise not_authenticated;
 19        exception
 20          when not_authenticated then
 21            raise_application_error(-20000, 'Not authenticated');
 22        end;
 23      when others then
 24        raise;
 25    end authenticate;
 26
 27    -- it exists in the specification, so it must exist in body
 28    function authenticated_user
 29      return varchar2
 30    is
 31    begin
 32      return null;
 33    end;
 34
 35    -- it is local to body (and can't be used out of it)
 36    function get_user_id
 37      return varchar2
 38    is
 39    begin
 40      return priv_number;
 41    end get_user_id;
 42  end msgg_session;
 43  /

Package body created.

SQL>

i do not really need the NOT_AUTHENTICATED exception i can just make it NO_DATA_FOUND instead — ndubizuacn, Sep 20 '19 at 19:23

Creating a package

1 Answers1