Oracle Apex Table Based Login

 To enable table based login in Oracle You can follow the following rule:

Login Table :

 CREATE TABLE APPSUSERS
   (     USERNAME                VARCHAR2(100) NOT NULL,
         PSSWRDS                 VARCHAR2(30)  NOT NULL,
         FULLNAME                VARCHAR2(64)  NOT NULL,
         EMAIL                   VARCHAR2(64),
         USERTYP                 VARCHAR2(1)   NOT NULL,
         ACTIVE                  VARCHAR2(1)   NOT NULL,
        ORGANIZATION            NUMBER(10)
   );

 User Type :

 CREATE TABLE USERTYP
(
ID      VARCHAR2(1),
NAME    VARCHAR2(50)
);

INSERT INTO USERTYP (ID,NAME) VALUES ('1','ADMINISTRATOR');
INSERT INTO USERTYP (ID,NAME) VALUES ('2','CONTRIBUTOR');
INSERT INTO USERTYP (ID,NAME) VALUES ('3','OPERATOR'); 
INSERT INTO USERTYP (ID,NAME) VALUES ('4','CUSTOMER'); 

COMMIT;

Procedure Based Login :

Previous (Default):

  apex_authentication.login(
    p_username => :P9999_USERNAME,
    p_password => :P9999_PASSWORD );

 

New :

declare
  CURSOR C_USER IS SELECT USERNAME,PSSWRDS,ACTIVE FROM APPSUSERS
  WHERE UPPER(LTRIM(RTRIM(USERNAME)))=UPPER(LTRIM(RTRIM(:P9999_USERNAME)));
  R_USER C_USER%ROWTYPE;
  vMess varchar2(2000):='';
BEGIN
  OPEN C_USER; FETCH C_USER INTO R_USER; CLOSE C_USER;
  if R_USER.USERNAME is null then
    vMess:=vMess||'User Not Found,';
  end if;
  IF R_USER.ACTIVE='N' THEN
    vMess:=vMess||'User is In-Active,';
  END IF;
  IF :P9999_USERNAME is null then
    vMess:=vMess||'Give User Name,';
  END IF;
  IF :P9999_PASSWORD is null then
    vMess:=vMess||'Give Password,';
  END IF;
  IF UPPER(ltrim(rtrim(:P9999_USERNAME)))<>UPPER(LTRIM(RTRIM(R_USER.USERNAME))) THEN
    vMess:=vMess||'User Name Not Found,';
  END IF;
  IF UPPER(ltrim(rtrim(:P9999_USERNAME)))=UPPER(LTRIM(RTRIM(R_USER.USERNAME)))
     and UPPER(ltrim(rtrim(:P9999_PASSWORD)))!=UPPER(LTRIM(RTRIM(R_USER.PSSWRDS))) THEN
    vMess:=vMess||'Password Not Matched,';
  END IF;
  IF LENGTH(LTRIM(RTRIM(vMess)))>3 then
    apex_error.add_error(p_message => VMESS,p_display_location => apex_error.c_inline_in_notification);
  ELSE
    Wwv_Flow_Custom_Auth_Std.Post_Login(:P9999_USERNAME -- p_User_Name
                                       ,:P9999_PASSWORD -- p_Password
                                       ,v('APP_SESSION') -- p_Session_Id
                                       ,:APP_ID || ':1' -- p_Flow_page
                                       );
  END IF;
END;