Oracle: Embedded PL/SQL Gateway

Data Access Descriptors (DADs)

Data Access Descriptors is a set of configuration values that allow to configure database access using HTTP.

DADs are created and dropped with dbms_epg.create_dad and dbms_epg.drop_dad`.

Values for DAD attributes can be set with dbms_epg.set_dad_attribute

The dads.README of MOS Note 295535.1 (How To Create a DAD using OHS Standalone or From Oracle Database 10g Companion CD) was helpful to me when assembling the above table.

A DAD is represented as a servlet in XML DB HTTP Server.

Configuration values

The following values can be configured:

EPG Name	mod_plsql Name	mult. occ.	ORDS Setting
`after-procedure`	`PlsqlAfterProcedure`		`procedure.postProcess`	Name of procedure to be called after calling the requested procedure. Used for debugging/hooking purposes.
`always-describe-procedure`	`PlsqlAlwaysDescribeProcedure`			If procedures a described at first access only and then cached, or if they're described at each access.
`authentication-mode`	`PlsqlAuthenticationMode`			`Basic`, `SingleSignOn`, `GlobalOwa` or `PerPackageOwa`. MOS Note 1112237.1 observers that `CustomOwa` cannot be set, contrary to the documentation.
`before-procedure`	`PlsqlBeforeProcedure`		`procedure.preProcess`	Name of procedure to be called before calling the requested procedure. Used for debugging/hooking purposes.
`bind-bucket-lengths`	`PlsqlBindBucketLengths`	✓
`bind-bucket-widths`	`PlsqlBindBucketWidths`	✓		The rounding size to use while binding the number of elements in a collection bind (only relevant if also using procedures with array parameters)
`cgi-environment-list`	`PlsqlCGIEnvironmentList`	✓
`compatibility-mode`	`PlsqlCompatibilityMode`
`database-username`	`PlsqlDatabaseUsername`		`db.username`	There is no `database-password` config corresponding to mod_plsql's `PlsqlDatabasePassword` because DADs do not store passwords.
`default-page`	`PlsqlDefaultPage`		`misc.defaultPage`	The procedure («page») to be called/shown if only the DAD Path is requested.
`document-path`	`PlsqlDocumentPath`			A virtual path. Requesting a document from this path (for example `http://epgserver/dad-path/DOCPATH/spreadsheet.xls`) will download the document from the configured document table (`document-table-name`).
`document-procedure`	`PlsqlDocumentProcedure`			Name of the procedure to be called when document download is initiated. Used to generate a response after the file is uploaded. Default seems to be `wpg_testdoc.process_download`.
`document-table-name`	`PlsqlDocumentTablename`		`owa.docTable` (or `apex.docTable`?)	Name of table into which documents are uploaded. It seems that this table is filled when a `POST` request is made. Default value seems to be `wpg_document`.
`error-style`	`PlsqlErrorStyle`		`debug.printDebugToScreen`
`exclusion-list`	`PlsqlExclusionList`	✓	`security.exclusionList`
`fetch-buffer-size`	`PlsqlFetchBufferSize`
`info-logging`	`PlsqlInfoLogging`
`owa-debug-enable`	`PlsqlOWADebugEnable`
`max-requests-per-session`	`PlsqlMaxRequestsPerSession`		`dbc.MaxConnectionReuseCount`
`nls-language`	`PlsqlNLSLanguage`			Overrides/sets the value of `nls_language`. If different from value in database, an `alter session set nls_language=<LANG> nls_territory=<TERR>` will be executed every time a new database connection is created for the DAD. The value of this setting influcences 1) the character set of the content coming back from the database, 2) The character set used when binding arguments passed via OCI 3) the IANA character set used in the `Content-Type` header of an HTTP response
`path-alias`	`PlsqlPathAlias`			See `path-alias-procedure`. The default values seems to be: `sys., dbms_", utl_, owa_, owa., htp., htf.*`. Will setting this directive to `#NONE#` disable all protection?
`path-alias-procedure`	`PlsqlPathAliasProcedure`			The name of the procedure to call when the virtual path in the URL matches `path-alias`.
`request-validation-function`	`PlsqlRequestValidationFunction`		`security.requestValidationFunction`
`session-cookie-name`	`PlsqlSessionCookieName`			The name of the HTTP Cookie to identify sessions.
`session-state-management`	`PlsqlSessionStateManagement`
`transfer-mode`	`PlsqlTransferMode`			`Char` or `Raw`
`upload-as-long-raw`	`PlsqlUploadAsLongRaw`			Files with this extensions are uploaded as `long raw` rather then the default `blob`.

Because EPG runs on a database (and does not connect to another database), there is no equivalent to the PlSqlDatabaseConnectString

Authentication schemes

How a user of a DAD is authenticated depends on the value of the DAD's database-username setting and if dbms_epg.authorize_dad is invoked.

If 'database-username' is set:

    
    If 'database-username' = ANONYMOUS:

        Authentication mode := anonymous

    Else If UserAuthorized:
    
        Authentication mode := static

    Else:

        Authentication mode := dynamic restricted

Else:

        Authentication mode := dynamic

dba_dad_authorization shows which users have authorized use of a DAD.

Static authentication requires the database account ANONYMOUS to be unlocked, otherwise EPG will answer a request with the HTTP status 401 Unauthorized.

Invoking EPG

mod_plsql is invoked like

protocol://hostname[:port]/DAD_location/[[!][schema.][package.]proc_name[?query_string]]

I assume that EPG is similarly invoked.

The optional ! indicates to use the flexible parameter passing scheme.

Links

The chapter Developing PL/SQL Web Applications in the Database Developer Guide