Let us try to recreate the fun of crafting APEX by making our own DAD and doing a few things with it. This will help us boost our understanding of the ins and outs of APEX. Make sure that you have set your XMLDB HTTP server to work on the port of your choice using DBMS_XDB.sethttpport. You will have to use this port and the IP address of your database server in all the URLs of this section. <your_database_ip:<your_db_http_port> will replace localhost:8080 in the URLs of this section.

The code in 4-98-9_Appendix_create_dad.sql creates and configures a DAD for you. The DAD you create is authorized to execute the procedures in the schema you mention in the Enter the name of the schema with which you want to associate this DAD: prompt. Execute the following using sysdba.

SQL> @4-98-9_Appendix_create_dad

I believe that you have compiled the appendix package in your schema. If not, then check the beginning of the Appendix to find to command to do this. Open the Appendix package and have a look at the appendix.dad_proc procedure.

Invoke the following URL. Here packt_dad is the name of the DAD which we just created:

http://localhost:8080/packt_dad/APPENDIX.DAD_PROC?val=Hello%20World&redirect_url_var=http://www.google.com

We can also use the following URL:

http://localhost:8080/packt_dad/PACKT_SCHEMA.APPENDIX.DAD_PROC?val=Hello%20World&redirect_url_var=http://www.google.com

Invoking the previous URL opens the Google home page and inserts Hello World into the dad_table. The insertion is shown in the previous screenshot.

In this example, we saw the use of OWA package to do web interaction. We also saw insertions in the database. The best part was that both the web interaction and the database insertion were done by the same URL. This is pretty much what APEX does but it does it on a lot bigger scale.

We can call the same function in the Listener configuration by giving execute rights on this function to APEX_PUBLIC_USER and listing the function in the defaults.xml file of the Listener configuration. Defaults.xml can be found in the Listener's configuration directory. We can find the location of the Listener configuration directory by executing the following command:

C:\ >java -jar <Directory holding apex.war>\apex.war configdir

Add the following tag in the defaults.xml file and restart WebLogic:

<entry key="security.inclusionList"> apex, p, v, f, wwv_*,y*, c*, PACKT_SCHEMA.*,packt_schema.*, apex_util.*</entry>

This will ensure that the procedures defined in PACKT_SCHEMA are directly accessible from the browser. If your schema name is different, then put the same instead of PACKT_SCHEMA in the previous code.

Execute the following using sysdba. This will ensure that APEX_PUBLIC_USER has execution rights on the APPENDIX package in packt_schema:

grant execute on packt_schema.appendix to apex_public_user;

The following link will open the Google home page and insert Hello World into dad_table:

http://<apex_listener_webserver>:<apex_listener_webserver_port>/apex/PACKT_SCHEMA.APPENDIX.DAD_PROC?val=Hello%20World&redirect_url_var=http://www.google.com