Oracle APEX Architecture Free Tutorial

This free tutorial explains APEX architecture, i.e. how the HTTP request/response cycle works, how an APEX application works (prerequesite knowledge), then introduces the options for an HTTP listening service: the embedded PL/SQL gateway, the Apache mod_plsql module, and the APEX listener deployed to either a Glassfish or a WebLogic container.

We summarize with benefits and recommendations for the various listener options.

Audience:  Oracle DBA’s, Internet Architects, System Admins, APEX Developers, anyone interested in learning how APEX works.

Presenter: John Watson, Oracle Certified Master and SkillBuilders’ Director of Oracle Database Services.

Moderator: Dave Anderson, SkillBuilders President and Oracle DBA

his free tutorial is segmented into the following modules:

  1. Introduction to John Watson, SkillBuilders Director of Oracle Database Services (1:59)
  2. Tutorial Agenda (2:35)
  3. Understanding the HTTP Request / Response Cycle (6:56)
  4. How an APEX Application Really Works (A Bit of Architecture) (12:21) (click on video below)
  5. Listener Options Part 1: Embedded PL/SQL Gateway & Apache (5:26)
  6. Listener Options Part 2: The Oracle APEX Listener (9:18)
  7. Recommendations and Next Steps (1:27)

Date: Jul 25, 2013

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


How an APEX Application Really Works (A Bit of Architecture)

Oracle APEX Architecture and Listener Options


Session 4 – What is an APEX Application?


>> John:  APEX itself is just a PL/SQL web application. The procedure generates pages of HTML for display in browsers.




We invoke the procedures through the web listener, through the GET or POST methods. I was demonstrating a very simple GET at that point. HTTP is the protocol of the browser and web listener. It does cause issues with session maintain sessions because HTTP is a stateless protocol and each contact from browser to the web listener is as far as the web listener is concerned a brand new contact, it has no memory, but APEX has techniques for maintaining or simulating states even though HTTP itself is stateless.


Having reached the web listener, the web listener will forward the request onto the database over SQL Net or Oracle Net [01:04 inaudible] the correct term and then within the database server they execute the f procedure with all type of parameters that pass through on the URL.




How do we generate that page of HTML to get sent back to the browser? Several techniques, but you can do it the hard way. I’m going to do it the hard way just to demonstrate it, just to demonstrate what’s going on. That’s what the packages HTP and HTF. More cut packages OWA_UTIL, OWA_COOKIE, many, many more. These packages do make development much easier than if we’re designing HTML pages yourself character by character. These packages will do a lot of the work for you.




How does it work? What I’m going to do is proceed to a UNIX system, which I happen to have got here.




First off, we need a web listener. We need a web listener. We can’t use APEX at all. So lsnlctl status. Do we have anything listening? My database listener is monitoring ports 1521 and that’s it. There’s no HTTP listening going on here.


The first thing I need to do is enable a listener for HTTP, sql plus /as sysdba and run a couple of packages, it will enable listening. We have a package called dbms_xdb. Describe it if you wish, desc dbms_xdb, and among other things, this gives us the ability to configure listeners for FTP and HTTP. I’m not interested at FTP at the moment, but I will enable and FTP port. Which port? 8008, I just picked that at random.




Once that call completes – if I return to my database listener – what do we see?




We see that I do now have listener 8008 is now listening for HTTP. I’ve set up a very basic HTTP listener. This, by the way, when we move on is effectively the embedded PL/SQL Gateway that I’m using at this point, which we’ll talk about in great detail a bit later on.


I’ve enabled HTTP listening. Then I need to create the virtual path. Just remember when you issue a URL, you have to setup this thing. In this case, it was pls/otn. We have to create that as the path that can be made visible or available to users.




Again, within the database environment we have another package. This call here to dbms_epg creates what we call DAD. A DAD is a database access descriptor. Database access descriptor is just a logical name. It’s a logical name for a virtual path and I’m creating a virtual path scott for reasons I [04:23 inaudible] later on.




My dad is called scott dad, it generates or define the virtual path slash (/) scott. That’s the virtual path the main users will put into their browser when they get to the application. So instead of pls/otn they’re going to put in scott.


Now I need to design my application.




I shall connect to the scott schema. Very simple application.




Take a look at – I’m not connected, am I – sqlplus scott/tiger. We’ll then connect scott/tiger. Pretty much copy/paste going on.




Now design my application, which at this point is going to be a pretty simple application. Create or replace procedure hello world as begin.


Next package. This is a standard package, not part of APEX, htp.print. htp.print. If we look at the htp package – desc htp, it has a whole set of procedures and functions, procedure open table, procedure table header, procedure style, strike. Those of you who work with HTML a lot will recognize that what we’re doing here is we’re generating HTML text.




If we were to use htp, for example, these are the text with creating forms. I’m just using htp.print. What am I going to print? Hello world and then concatenate that to a time stamp. That’s a very, very simple procedure. Create and replace procedure hello world as begin, htp.print and your text string, end and compile.




I can try to invoke it. So if I go to my browser HTTP:// – moving on security. At this point, I am not going to put in a password. You will see the effect of not getting it to use a password first off, Just remember 8008 was the listening port I defined for HTTP at this point.


The virtual path /scott. Why scott? Because that’s the virtual path I defined with the procedure called there.




Before this is going to work I need to associate log ons through that with a particular schema. One more call in the database, which will be scott, one more call to the database will be – and I’ll try straightaway without using any security and see if I can actually do with no security at all at this point.


The procedure I wrote was called hello_world. No security, I got prompted for a log on. This is where I could’ve used a username and password in the URL itself. I’m going to log on as user scott, password tiger, and there we go. The procedure is executed, the hello world procedure is executed and returned the string that I specified there. If execute the URL again, you can’t see [08:05 inaudible]. If I click there, note the time 6:21:25, 6:21:43, 6:21:45. So I’m executing that procedure each time.




I don’t have too much time. I’ll make my application slightly more complicated but I’m trying to at this point, you can probably realize, is basically demystify what PL/SQL web applications are.




Here’s another example, another procedure. Create or replace procedure printtab and I’m giving this one an argument, tname varchar2.




I need to setup a variable at this point, dummy which is Boolean. That’s because the procedure I’m going to use returns a Boolean, so I need somewhere to put it, and then begin. Dummy = owa_util. I’ve used the owa_util package at this point. Owa_util has a wonderful procedure called tableprint and it will generate all the HTML printing a table with just one call. They’ll pass into it, tname, which is the variable, the argument being passed into my procedure. I’m sure you work out what this is going to do. It’s going to print out whatever table the end user requests to be printed. End, and see if it compiles. It does.


The next stage of my application, I’ll use my application – what shall I call it? Printtab.




Printtab, passing through an argument, tname = dept. Back comes the department table, tname = emp. Back comes the emp table.




And the final example, what about DML? We can do DML through this as well. One last procedure and then we can move on to how the listening is working.


A procedure called newsal. I’m going to change the salary of someone in my company. Print or replace procedure newsal as begin, and give it a DML statement. Update. Update emp set sal = salary where = eno. Salary and eno are the arguments I’m defining for this procedure, both numeric. End.


Try invoking that one now through the web. The procedure is called newsal and then my most complicated prompt to string yet, empno =. Let’s try to update Mr. Miller, 7934, ampersand (&) is the delimiter you want to prompt the string and here salary = 8000. That will do.




Issuing that gives me nothing back and that’s because my very simple procedure here doesn’t do any user interface work but normally of course a message will come back. Go back to my table and we should now see that Mr. Miller his salary has gone up to 8000. Has it indeed?




Yes, it has.


That is all there is to APEX applications. That’s all APEX application really is. All the application actually is, is just a set of wizards for generating code such as that code I’ve just written. That’s all it is. It’s set of wizard for generating PL/SQL packages, PL/SQL procedures and it makes extensive use of packages like this for generating the HTML. It’s nothing clever except how the web listener itself is configured.


Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!