Oracle really is working hard to provide and eliminate the stuff that is not required. Th example here is the elimination of the need to have an extra server, a web server (Tomcat, OHS, 10gAS, weblogic, glashfish, apache ,oc4j - You name it). This is I think the sweetest way of accessing the Oracle 's PL/SQL
Package from the Oracle Database. I felt it so simple yet amazing. Salute to you Oracle !!
Steps:
Please note that my steps are from 11gr2 database:
1- First check and see if the EPG is available and it is in what state by using following script
Please execute as sysdba:
$ORACLE_HOME/rdbms/admin/epgstat.sql
The result is :
AMGHOST2 ->>sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 3 15:31:43 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: /as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> SQL> @$ORACLE_HOME/rdbms/admin/epgstat.sql +--------------------------------------+ | XDB protocol ports: | | XDB is listening for the protocol | | when the protocol port is non-zero. | +--------------------------------------+ HTTP Port FTP Port --------- -------- 8080 0 1 row selected. +---------------------------+ | DAD virtual-path mappings | +---------------------------+ Virtual Path DAD Name -------------------------------- -------------------------------- /apex/* APEX /change/* change 2 rows selected. +----------------+ | DAD attributes | +----------------+ DAD Name DAD Param DAD Value ------------ ------------------------ ---------------------------------------- APEX database-username ANONYMOUS default-page apex document-table-name wwv_flow_file_objects$ document-path docs document-procedure wwv_flow_file_mgr.process_download request-validation-funct wwv_flow_epg_include_modules.authorize ion nls-language american_america.al32utf8 change database-username CHANGE default-page helloworld 9 rows selected. +---------------------------------------------------+ | DAD authorization: | | To use static authentication of a user in a DAD, | | the DAD must be authorized for the user. | +---------------------------------------------------+ DAD Name User Name -------------------------------- -------------------------------- change CHANGE 1 row selected. +----------------------------+ | DAD authentication schemes | +----------------------------+ DAD Name User Name Auth Scheme -------------------- -------------------------------- ------------------ APEX ANONYMOUS Anonymous change CHANGE Static 2 rows selected. +--------------------------------------------------------+ | ANONYMOUS user status: | | To use static or anonymous authentication in any DAD, | | the ANONYMOUS account must be unlocked. | +--------------------------------------------------------+ Database User Status --------------- -------------------- ANONYMOUS OPEN 1 row selected. +-------------------------------------------------------------------+ | ANONYMOUS access to XDB repository: | | To allow public access to XDB repository without authentication, | | ANONYMOUS access to the repository must be allowed. | +-------------------------------------------------------------------+ Allow repository anonymous access? ---------------------------------- false 1 row selected. SQL>
In above case (My Case) it is enabled. It may not be enable. If that is the case then do following:
a- edit the $TNS_ADMIN/listener.ora file and add following:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amghost2)
(PORT=8080))(Presentation=HTTP)(Session=RAW))
b- stop and start the listener
c- execute the following
exec DBMS_XDB.setHTTPPort(8080);
You can choose any port. However if you choose port 80 then please note that root privileges are required.
d- Please make sure that listener started and is ready to listen on port 8080.
e- alter user ANONYMOUS account unlock;
f- alter
user ANONYMOUS identified by apex11;
g- GRANT
EXECUTE ON DBMS_EPG TO change; -- Here my schema was change so I grant the access to change . Please substitute as appropriate.
2 2- Step 1 was to check and make sure that EPG is running. Now at this step we will define the DAD, Grant the permission and in step 3 will test. As simple as that.
Please execute following. My DAD is change and schema is change. Please note that I will be using the same PL/SQL package hellotest to test and show the results here.
a- SQL> begin
dbms_epg.create_dad (dad_name => 'change',path
=> '/change/*');
end;
/
b- SQL>
begin
DBMS_EPG.set_dad_attribute (
dad_name
=>'change', attr_name=> 'database-username',
attr_value=>'CHANGE');
end;
/
c- SQL>
begin
DBMS_EPG.authorize_dad
(
dad_name
=>'change',
user
=>'CHANGE');
end;
/
3- Testing step. Now we can access the URL as http://:8080/change/hellotest. Please note that pls, dpls, jopa are all gone - just DAD access directly. Isn't it sweet. My results are pasted below:
That is it !!. I will post some stuff on APEX (Oracle's Application Express)- Its install, upgrade, and acees via DPLS and EPG. Stay Tune !!
