Dienstag, 15. November 2011

Setting up the basic environment...

Ok maybe this is not so APEX related, but we need a basic structure for our application.Our setup will have at least 3 users

  • SYS the owner of the dbms_profiler package
  • MAINTENANCE the Apex workspace
  • HR as a placeholder for any user who runs the profiler. We will use HR to get some data.

Lets assume that you haved managed to install Oracle XE 11gR2 and to upgrade APEX to 4.1.

I will try to remember how that worked and write another post if wanted.

First you should create the Apex Workspace you want to use for your application. My goal is to create a maintenance application so I simply named my Workspace and the cooresponding database schema MAINTENANCE.

Setting up dbms_profiler
Run the script profload.sql as user sys. That will install the dbms_profiler package. It is mandatory to have the package installed within the sys schema.
After that grant the necessary privileges to the MAINTENANCE schema.

GRANT EXECUTE ON sys.dbms_profiler TO maintenance WITH GRANT OPTION;

GRANT SELECT ANY dictionary TO maintenance;

Script => runasSys.sql

Setting up the profiler tables
Run the script proftab.sql as user maintenance. This will create the table used by dbms_profiler. Usually they are located in the schema that is runing the profiler (hr in our case), but I decided to have them in a central place as part of the apex workspace.

After that grant the necessary privileges to the HR schema.

GRANT INSERT,UPDATE,SELECT ON plsql_profiler_runs TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_units TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_data TO hr;
GRANT SELECT ON plsql_profiler_runnumber TO hr;

GRANT EXECUTE ON sys.dbms_profiler TO hr;

CREATE SYNONYM hr.plsql_profiler_runnumber FOR maintenance.plsql_profiler_runnumber;
CREATE SYNONYM hr.plsql_profiler_runs FOR maintenance.plsql_profiler_runs;
CREATE SYNONYM hr.plsql_profiler_units FOR maintenance.plsql_profiler_units;
CREATE SYNONYM hr.plsql_profiler_data FOR maintenance.plsql_profiler_data;

Script => runasMaintenance.sql
Test your environment
Create a very simple procedure as user HR

FOR x IN 1..100
END test;

and execute it with an activated profiler.


select * from plsql_profiler_runs;

If you see at least one (additional) line of data everything is fine and the basic setup of our environment is done.







You can delete the procedure if you want.

Script =>  runasHr.sql


I already started some days ago to learn APEX, because I'm really new to it.
Having a solid backround in PL/SQL and SQL there is still not much knowledge when it comes to Javascript, jQuery and Ajax e.g.
So the first posts will be based on the experience I have made during the last days. I hope that I don't skip important findings that I have made during my sessions. If there should be something missing please ask!

With what?

Ok a simple list:

  • APEX 4.1
  • Oracle XE 11gR2
  • Firefox
  • Firebug
  • SQLDeveloper
To be continued!


Being back in the service business from 1st of November, there could be situations where I do not have access to the tools I need for development and maintenance.
Already today I have no tool to handle the results from the package dbms_profiler in a proper way. Toad displays the results, but the result is useless in my opinion and Allroundautomations PL/SQL Developer is already not available.
My initial idea to write a plugin for the SQL Developer, I gave up quickly. In search of a free tool for rapid design of user interfaces I've come across Oracle Application Express (APEX).
My goal is to create considerable user interfaces without much effort.