issues with package syntax

Question

i am trying to create a package called MSGG_SESSION with a procedure authenticate that accepts two VARCHAR2 parameters for username and password. i am suppose to 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. Add a function get_user_id to the package that returns the value of the variable holding the person ID.

but i get two erros saying table or view does not exits starting at the second is before not_authenticated_exception

and sql statement ignored starting at priv_number varchar2(100).

CREATE OR REPLACE PACKAGE MSGG_SESSION IS

  PROCEDURE AUTHENTICATE (USERNAME_to_auth IN VARCHAR2, PASSWORD_to_use 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     

    not_authenticated exception;

   begin

    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     

     begin

      raise not_authenticated;

     exception

      when not_authenticated then

       raise_application_error(-20000, 'Not authenticated');

     end;

    when others then

     raise;

   end authenticate;   

  function authenticated_user

    return varchar2

   is

   begin

    return null;

   end;



   function get_user_id

    return varchar2

   is

   begin

    return priv_number;

   end get_user_id;

  end msgg_session;

  /

Answer 1

You don't provide table DDL or the line number of the error message so it's not clear why you would get ORA-00942: table or view does not exist. Check the spelling of the table, make sure the table and the package are in the same schema and nothing is defined in double-quotes (e.g. user_password is not the same as "user_password").

Assuming that the table looks something like this:

create table user_password
( user_id   integer constraint user_password_pk primary key
, username  varchar2(30) not null constraint user_password_username_uk unique
, password  varchar2(30) not null );

with sample test data:

insert into user_password (user_id, username, password)
values (1, 'ndubizuacn', 'Kittens');

A fixed version of your package would look like this:

create or replace package msgg_session as

    procedure authenticate
        ( username_to_auth in user_password.username%type
        , password_to_use  in user_password.password%type );

    function get_user_id
        return user_password.user_id%type;

end msgg_session;
/

create or replace package body msgg_session as

    priv_number user_password.user_id%type;

    procedure authenticate
        ( username_to_auth in user_password.username%type
        , password_to_use  in user_password.password%type )
    is
    begin
        select user_id 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_application_error(-20000, 'Not authenticated');
    end authenticate;


    function authenticated_user
        return varchar2
    is
    begin
        return null;
    end authenticated_user;


    function get_user_id
        return user_password.user_id%type
    is
    begin
        return priv_number;
    end get_user_id;

end msgg_session;
/

Test:

begin
    msgg_session.authenticate('ndubizuacn', 'Kittens');
    dbms_output.put_line(msgg_session.get_user_id);
end;
/

Assuming dbms_output is enabled, this prints the value 1.

Using a global variable for something like this doesn't make a great interface, but it's a requirement of the assignment so I guess it shows how to use one. Same goes for needing to make two calls - perhaps you could expand your authenticated_user function to provide an alternative interface (pass in user and password, get back user_id all in one shot).

Storing passwords in plain text is an obvious security risk, and it is sometimes said that you should never use any online service that can send you your password if you forget it (you don't see that too often these days, but it used to be quite common). It would be more secure not to store the password at all but instead store ora_hash(upper(username)||'~'||password)), so for example for username ndubizuacn and password Kittens you would store 2160931220. Then your authentication function might be something like:

function authenticated_user
    ( username_to_auth in user_password.username%type
    , password_to_use  in user_password.password%type )
    return user_password.user_id%type
is
    l_user_id user_password.user_id%type;
begin
    select user_id into l_user_id
    from   user_password
    where  username = username_to_auth
    and    password_hash = ora_hash(upper(username_to_auth)||'~'||password_to_use);

    return l_user_id;
exception
    when no_data_found then
        raise_application_error(-20000, 'Not authenticated');
end authenticated_user;