Custom Logon Authentication in Apex 4.2

Step 1.
CREATE TABLE  MY_USER
(
  USER_NAME  VARCHAR2(10 BYTE),
  USER_PWD   VARCHAR2(10 BYTE)
)
TABLESPACE INVENTORY
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Step 2.
Insert into MY_USER
   (USER_NAME, USER_PWD)
 Values
   ('Tom', 'Secret');
COMMIT;

Step 3.

CREATE OR REPLACE FUNCTION validate_user_from_db (
   p_username   IN   VARCHAR2,
   p_password   IN   VARCHAR2
)
   RETURN BOOLEAN
AS
   v_pw_check   NUMBER (10);
BEGIN
   SELECT COUNT (1)
     INTO v_pw_check
     FROM my_user
    WHERE UPPER (user_name) = UPPER (p_username) AND user_pwd = p_password;

   IF v_pw_check >= 1
   THEN
      RETURN TRUE;
   ELSIF v_pw_check <= 0
   THEN
      RETURN FALSE;
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END validate_user_from_db;
Step 4.
 Edit logon page
=> share component => Authenticate Schemes => Create=> Next => Give a  Authentication name => press create authenticate scheme=> edit => name section select scheme Type="Custom"
=> setting section give Authentication function = "validate_user_from_db" => apply change

Nb: function parameter and login page userid password parameter must be same .

To support all browser for oracle forms 10gR2 please complete the following steps:

To support all browser for oracle forms 10gR2 please complete the following steps:

#################################################################################

To run in all browser we need "jre-6u23-windows-i586-s.exe" this file.
*""* Please collect/download "jre-6u23-windows-i586-s.exe" this file. *""*

1. Change the formsweb.cfg like below
---------------------------------------------------------------------------------

# System parameter: default base HTML file
baseHTML=webutiljpi.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=webutiljpi.htm
# System parameter: base HTML file for use with Sun's Java Plug-In
baseHTMLjpi=webutiljpi.htm
# System parameter: base HTML file for use with Microsoft Internet Explorer
baseHTMLie=webutiljpi.htm



jpi_download_page=/forms/jinitiator/us/jre-6u23-windows-i586-s.exe
jpi_classid=clsid:CAFEEFAC-0016-0000-FFFF-ABCDEFFEDCBA
jpi_codebase=/forms/jinitiator/us/jre-6u23-windows-i586-s.exe#Version=1,6,0,23
jpi_mimetype=application/x-java-applet
# Internet Explorer uses mimetype that includes version specifier.
jpi_ie_mimetype=application/x-java-applet;jpi-version=1.6.0.23
# New parameter to use in OBJECT/EMBED tags to indicate java version.
# Parameter uses a trailing "*" to use a version family.
java_version=1.6*




2. Change the basejpi.htm and webutiljpi.htm like below(file located in <ORACLE_HOME>\forms\server\)
----------------------------------------------------------------------------------------------------

<!-- Forms applet definition (start) -->
<OBJECT classid="%jpi_classid%"
        codebase="%jpi_codebase%"
        WIDTH="%Width%"
        HEIGHT="%Height%"
        HSPACE="0"
        VSPACE="0">
<PARAM NAME="TYPE"       VALUE="%jpi_ie_mimetype%">
<PARAM NAME="JAVA_VERSION" VALUE="%java_version%">

<EMBED SRC="" PLUGINSPAGE="%jpi_download_page%"
        CODE="Applet.class"
        TYPE="%jpi_mimetype%"
        java_codebase="%codebase%"
        java_code="oracle.forms.engine.Main"
        java_archive="%archive%" (for webuti use java_archive="%archive%,%webUtilArchive%")
        java_version="%java_version%"
        WIDTH="%Width%"
        HEIGHT="%Height%"


3.
Now keep the jre-6u23-windows-i586-s.exe software in D:\DevSuiteHome_1\jinit\us\

4.
Configuration complete but if u use Opera, Googe Chrome,Safari etc
Please run the software first on IE or Mozilla Firefox. then it will work for all browser.


Key Map Example
---------------
[key_jre]
form=D:\BEFTN\FORMS\FRM_LOGON_MICR_S.fmx
userid=beftn/beftn@dba
pageTitle=digiPay:: Developed By ERA InfoTech Ltd#envFile=default.env
width=100%
height=100%
lookAndFeel=oracle
colorscheme=blue
imagebase=documentbase
splashscreen=none
logo=none
separateFrame=false
background=none
baseHTML=webutiljpi.htm
baseHTMLjinitiator=webutiljpi.htm
baseHTMLjpi=webutiljpi.htm
baseHTMLie=webutiljpi.htm
jpi_download_page=/forms/jinitiator/us/jre-6u43-windows-i586.exe
jpi_classid=clsid:CAFEEFAC-0016-0000-FFFF-ABCDEFFEDCBA
jpi_codebase=/forms/jinitiator/us/jre-6u23-windows-i586-s.exe#Version=1,6,0,43
jpi_mimetype=application/x-java-applet
jpi_ie_mimetype=application/x-java-applet;jpi-version=1.6.0.43
java_version=1.6*
HTMLbodyAttrs=scroll="no" toolbar="no" leftmargin="0" topmargin="0" marginheight="0" marginwidth="0"
WebUtilArchive=frmwebutil.jar,jacob.jar
# baseHTMLjinitiator=webutiljini.htm
# baseHTMLjpi=webutiljpi.htm
archive_jini=frmall_jinit.jar,f90all.jar,rolloverbutton.jar,lablediconbutton.jar,swingbutton.jar
archive=frmall.jar,f90all.jar,rolloverbutton.jar,lablediconbutton.jar,swingbutton.jar,frmwebutil.jar,jacob.jar,handleimage3.jar,jdic.jar,eHTMLBrowser.jar,getClientInfo.jar,,myIcons.jar
escapeparams=true
WebUtilLogging=on
WebUtilLoggingDetail=normal
ebUtilErrorMode=Alert
WebUtilTrustInternal=true
WebUtilDispatchMonitorInterval=5
WebUtilMaxTransferSize=16384

Oracle Database 11g Passward Expire Problem

Different from other releases, Oracle 11g sets by default password expiration.

Suppose you have an user with the status "expired":

Please check at:

1) SQL> select username, account_status, expiry_date, profile from dba_users;

USERNAME             ACCOUNT_STATUS       EXPIRY_DA PROFILE
-------------------- -------------------- --------- ----------
SOE                  OPEN                 18-JAN-12 DEFAULT
LOUL                 EXPIRED              18-JAN-11 DEFAULT
SCOTT                EXPIRED & LOCKED     15-JUL-11 DEFAULT
ORACLE_OCM           EXPIRED & LOCKED     05-SEP-10 DEFAULT
XS$NULL              EXPIRED & LOCKED     05-SEP-10 DEFAULT
MDDATA               EXPIRED & LOCKED     05-SEP-10 DEFAULT
DIP                  EXPIRED & LOCKED     05-SEP-10 DEFAULT
APEX_PUBLIC_USER     EXPIRED & LOCKED     05-SEP-10 DEFAULT

2) SQL> alter user loul identified by netapp;

3) To avoid to have users with "expired" password, you have two options:

- Create a new profile and set your own definitions about dates to expire the passwords
- Change the default profile

4) Let's make an example how to do that:

Checking profiles:

SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT';

  PROFILE    RESOURCE_NAME                    LIMIT
---------- -------------------------------- ----------------------------------------
DEFAULT    COMPOSITE_LIMIT                  UNLIMITED
DEFAULT    SESSIONS_PER_USER                UNLIMITED
DEFAULT    CPU_PER_SESSION                  UNLIMITED
DEFAULT    CPU_PER_CALL                     UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION        UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL           UNLIMITED
DEFAULT    IDLE_TIME                        UNLIMITED
DEFAULT    CONNECT_TIME                     UNLIMITED
DEFAULT    PRIVATE_SGA                      UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS            10
DEFAULT    PASSWORD_LIFE_TIME               180

PROFILE    RESOURCE_NAME                    LIMIT
---------- -------------------------------- ----------------------------------------
DEFAULT    PASSWORD_REUSE_TIME              UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         NULL
DEFAULT    PASSWORD_LOCK_TIME               1
DEFAULT    PASSWORD_GRACE_TIME              7

5) Changing the password_life_time for unlimited

SQL> alter profile default limit password_life_time unlimited;

--------------OR-----------

For development you can disable password policy if no other profile was set (i.e. disable password expiration in default one):

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Then, reset password and unlock user account. It should never expire again:

--------------END-----------

Difference Between 'IN' and 'EXIST' Operator

This is a commaon question that comes to each developers mind.  Most of them considers IN and EXISTS as same
but with different syntax. This aint true .

We can use both operators to fetch the same results. Let me take few examples before we go to the actuals.

SELECT * FROM TABLE_1 returns records as shown below
Field1
1
2
3
4

SELECT * FROM TABLE_2 returns
Field2     Field3
     
1     4
2     6
3     7
4     8

if we want to get the data in TABLE_1 which are present in “Field3″
of TABLE_2 then we can use the query with IN operator as

    SELECT * FROM TABLE_1
    WHERE FIELD1 IN ( SELECT FIELD3 FROM TABLE_2)

We can also use the query with EXISTS as

    SELECT * FROM TABLE_1
    WHERE EXISTS(    SELECT ‘X’
    FROM TABLE_2
    WHERE TABLE_1.FIELD1 = TABLE_2.FIELD3
    )

when using EXISTS always use the where clause in the subquery to join the tables.
Not doing so will result in fetching all the records from the main table. for eample if we consider the query below

    SELECT * FROM TABLE_1
    WHERE EXISTS(    SELECT Field3
    FROM TABLE_2

    )

will fetch all the records from TABLE_1 and is same as the query

    SELECT * FROM TABLE_1

The other difference is in performance( depending on which table is selected in outer/ inner query).
 EXISTS works faster than IN. you can check the performance plans of the above query for more info.