Embedded PL/SQL Gateway Configuration    Sept  29, 2006

recent release of Oracle (9.2 +) have XML DB and an embedded HTTP Server, In Release 10.2, Oracle has extended the functionality to add PL/SQL  Gateway Support (which is implemented using mod_plsql in Apache HTTP) .

There is a package called DBMS_EPG   (EPG stands for Embedded PLSQL Gateway), which allows you to define DAD connection (DAD is Database Access Descriptor)

First of all, you need to make sure that HTTP Port is configured. The following listener.ora extract shows the proper configuration.

# listener.ora Network Configuration File: c:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (SID_NAME = orcl1)
        )
    )

LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = localhost)
                 (PORT = 1521)
                )
        )
        (DESCRIPTION=
            (ADDRESS =
                (PROTOCOL=tcp)
                (HOST=localhost)
                (PORT=8080))
            (Presentation=HTTP)(Session=RAW)
            )
        )

restart the listener

Run the following after logging as SYS user

BEGIN
    DBMS_EPG.create_dad (
    dad_name => 'Ammar',
    path => '/pls/apex/*');
END;
/
 

A DAD called Ammar is created, and can be accessed by using pls/apex/ path in the url

To test the Gateway

login in to SCOTT

SQL>Create Or Replace Procedure  TEST is

begin

   Htp.print('Hello World");

end;

/

Then invoke your browser and use the following URL

http://127.0.0.1:8080/pls/apex/test

The browser will then prompt you with login screen

Username: Scott

Password : Tiger

The browser should then display  Hello World

This is just a simple example of configuring a DAD, there are more attributes to add when defining a DAD, one needs to refer to the DBMS_EPG to full details, for now, i will only add a simple example used to configure Oracle HTML_DB

begin

    dbms_epg.set_dad_attribute('Ammar','database-username','ANONYMOUS');
    dbms_epg.set_dad_attribute('Ammar','default-page','apex');
    dbms_epg.set_dad_attribute('Ammar','document-table-name','wwv_flow_file_objects$');
    dbms_epg.set_dad_attribute('Ammar','document-path','docs');
    dbms_epg.set_dad_attribute('Ammar','nls-language','american_america.al32utf8');
    dbms_epg.set_dad_attribute('Ammar','document-procedure','wwv_flow_file_mgr.process_download');
    dbms_epg.set_dad_attribute('Ammar','request-validation-function','wwv_flow_epg_include_modules.authorize');
end;