1) create a new DAD hr (as user SYS or SYSTEM)

BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'hr',
    path     => '/hr/*');
 
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'hr',
    attr_name  => 'database-username',
    attr_value => 'HR');
        
  DBMS_EPG.authorize_dad (
    dad_name => 'hr',
    user     => 'HR');
 
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'hr',
    attr_name  => 'before-procedure',
    attr_value => 'hr.validate_login');
END;
/ 

2) create a test procedure TEST (schema HR)

create or replace procedure test is
begin
  htp.p('hello world');
END;
/ 

3) create a login validation procedure called validate_login (schema HR)

CREATE OR REPLACE PROCEDURE validate_login IS
BEGIN
   HTP.p ('check authentication...');
   HTP.p ('redirect to login page if not authenticated');
END validate_login;
/ 

Now you can call the test procedure via http://127.0.0.1:8080/hr/test

When you call it, the DAD first executes the 'before-procedure' validate_login.
In there you could check a cookie whether the user has a valid session.

You could even determine which procedure / package the user wants to call and allow public access to the public pages.

If the user wants to access a protected page you would use owa_util.redirect_url to redirect him to your public login form (be aware that validate_login will executed again, please allow the access there :).

In the login form you would post the username/password to your authentication function and set up the cookies. After that you would redirect the user to the page he wanted to access originally.

When the DAD is created, it is possible to configure it with the set_dad_attribute procedure. In this example, we will specify the default page for the DAD.

BEGIN
DBMS_EPG.set_dad_attribute ('olvoyage', 'default-page', 'mon_package.home');
END;
/

'olvoyage' is the name of the DAD on which we'll add a new attribute of 'default-page' type.
We'll assign a value with the 'my_package.home' format which is the procedure called as the start page. This page is the result of the home procedure (from the my_package package) that you want to display as the home page.

To access the web application, type the following address in your web browser:
http://127.0.0.1:8080/olvoyage/

You can configure Static Authentication to avoid login demands while accessing your pages.

1. Grant the execution right on your package to lydy.

GRANT EXECUTE ON TEST TO lydy;

2. Use account with XDBADMIN role to set your DAD for lydy static authentication (you don’t need specify the password)

BEGIN
      dbms_epg.set_dad_attribute(dad_name => 'olvoyage',attr_name => 'database-username', attr_value => 'LYDY');
      END
      /

Warning: respect username case.

3. lydy must authorize the embedded gateway to use her privileges to invoke procedures and access document tables through the DAD. As lydy:

BEGIN
      dbms_epg.authorize_dad('LYDY');
      END;
      /

If your web page displays an error, you can see the errors with the activating error logging in Oracle XE Web server.

It allows you to trace PL/SQL errors when you develop a web application.

You must first activate the log option with a PL/SQL command.

SQL> execute dbms_epg.set_global_attribute('log-level', 3)

The different log-levels are:

0 - LOG_EMERG
1 - LOG_ALERT
2 - LOG_CRIT
3 - LOG_ERR
4 - LOG_WARNING
5 - LOG_NOTICE
6 - LOG_INFO
7 - LOG_DEBUG

After you can see the file:
/usr/lib/oracle/xe/app/oracle/admin/XE/bdump/xe_s00?_????.trc or C:\oraclexe\app\oracle\admin\XE\bdump\ xe_s00?_????.trc

To see the last error, you must open the most recent file with xe_s00 in its name.