ApEx: Parent-Child Select lists and onChange Submitting

Within ApEx (Oracle Application Express), it is easy to create select lists that auto-populate based on the selection of a prior select list.

In this example, lets setup two select lists. The first will determine what populates the second, and the second submits with both entries. This could be good for selecting a record based on a two-key (or primary-secondary key) table/view. (You could use DML to then popualte a form given you know your record.)

Create the first select list as a select list with redirect. Setup the LOV with a SQL query to select the distinct possibilities of the primary key. Remember that LOV queries in ApEx require selecting two things...the select list display and its corresponding value. Here are the steps (with examples)

From a page definition screen

  1. Click the icon to add a new item
  2. Choose select list and click Next
  3. Choose Select List with Redirect and click Next
  4. Name your item and click next
  5. Add Null Text (like 'Select ID') and a null value (like -1)
  6. then make your LOV Query
  7. select distinct class.name n, class.id d
    from owner.class
    order by 1

  8. Provide a Label and click Next
  9. Click the Create button

Create the second select list as a a select list with submit. Again, create it as a LOV with a SQL query. This time, the SQL query will query for all records given the first key is satisfied (value retireved form the first menu. Again, we'll branch to the submit.

  1. Click the icon to add a new item
  2. Choose select list and click Next
  3. Choose Select List with Submit and click Next
  4. Name your item and click next
  5. Add Null Text (like 'Select Name') and a null value (like -1)
  6. then make your LOV Query
  7. select chair.name n, chair.id d
    from owner.chair, owner.class
    where class.id = chair.class_id
    and chair.id = :P2_CLASSNAME
    order by 1

  8. Provide a Label and click Next
  9. Click the Create button

Finally, create the branch. Select which page you wish to go to, passing the appropriate values.

  1. Click the icon to add a new branch
  2. Choose On Submit: Before Computation (Before Computation, Validation, and Processing)
  3. Choose Branch to Page or URL
  4. Select recipient page
  5. Set these Items (values from the two pull down lists will go to two fields (that can be hidden if desired) on the page you are branching to
  6. Click Next
  7. Click Create Branch

That's the bare bones process. Since the form will only submit onChange, you may wish to have a submit button as well in case you wish to edit the same item twice in a row (session will keep the pulldown entries as they were).

Getting off the ApEx island: Database links

Much of the beauty of ApEx is its ability to take non-Oracle data and create secure applications based on that newly secured data. However, for as good the training/tutorial material is (including 'The Book'), no mention was ever made on how to link to Oracle databases not located within the ApEx world, leaving me to worry that ApEx (and more importantly, its data) was on an island.

This, thanks to Laurianne, was solved with only a little digging. You can link to other oracle databases by creating a database link. To do so:

From the Home page

  1. Click the SQL Workshop icon
  2. Click the Object Browser icon
  3. Click the Create button
  4. Choose database link
  5. Give it a name, schema, password,hostname.IP,port, and SID
  6. Click the create button

It can then be accessible to you in your everyday SQL commands by appending '@linkname' to the tables you are selecting from

For example, if you created a database link in ApEx called personLink, and it linked to your oracle table (or view) called 'personnel' table you could incorporate it into ApEx by typing

select * from personnel@personLink
where ...

...and ApEx just got a whole lot better fo me!

ApEx, application express, oracle application express, html-db, database links

Book: Easy Oracle HTML-DB (Cunningham & Crotty)

HTMLDBBOOKBeing new to ApEx, and to a lesser extent, databases in general, my bud Jon lent me a copy of the above book. We decided to take it for a spin, and our first task was to actually get an instance of ApEx up and running. This is what much of chapters 1 and 2 do, and according to our resident Seux-DBA Scott, it is well worth it. It was installed quickly, and an odd bug forced a patch to be installed (tracked down quickly onlne) and has since worked like a dream.

The next several chapters effectively take you through the process of creating workspaces and accounts, and then takes you into right into application building. The book is accompanied with an Online "Code Depot," which while it has the scripts needed to create some rudimentary tables in which to work, somtimes needed a little tweaking on copy/paste and most sql blocks of code were in a larger file instead of being in individual files as you might think as you go through.

In these chapters you do some interesting work, and I hope to benefit down the road by being able to either look at the book as a reference or literally at the work I did, but I have to admit the book might have led me through a little too directly. This is not necessarily a criticism of the book, but that my learning style is such that I often found myself just going through the steps...time will tell how much I really learned.

There is a clear demarkation at about chapter 10 where the book goes from directing you to being much more broad and nebulous -- talking more vaguely about what can be done...I liken it to the baby bird that just got shoved out of the nest. For this reason, I did not continue too deeply into the second half of the book as I felt I now need real and institutionally relevant work. I therefore reserve the right to add to this if my work finds me rushing back ot the book in the future.

All in all I strongly recommend this book for anyone considering starting with this product regardles of prior experience. It serves as an installation guide, tutorial, and reference. It may have its quirks, but all books do, and that should not stop you.

ApEx, application express, oracle application express, html-db, book, easy oracle html-db, cunningham, crotty, burleson

Welcome to ApEx, rookie!

ApEx is short for Oracle Application Express . It originally cut its teeth as HTML-DB (note the current logo still possesses the old monicker) -- and it is up to me to learn it. ApEx logo?

So what you [hopefully] will witness over the coming weeks and months is an account of my experiences -- both good and bad (hopefully more of the former).

So why is that so special?
Because I really have no DBA experience, and do not have much more than a basic understanding of databases, SQL, and DB philosophy. Since we implemented WebCT Campus Edition (CE) 6, I have been able to do some tinkering on the database to get a feel for how they work.

So why me?
If that ain't the $64,000 question...
Seriously, I am not wihtout my abilities, and though we have two very talented Oracle trained DBAs, if we as an MIS team put anything more on their shoulders, their respective floors would collapse and, well, at least their A/C problems would be solved.

I have a knack for learning quickly and I have a great team to help me when I get stuck. (You've been warned!!!)

oracle, application express, ApEx, HTML-DB

So why are you writing?
While ApEx is truly designed to help you create secure web-based databases without knowing too much about the databases themselves, I imagine many who have mastered it have that knowledge. The documentation on Oracle's web site is great and I know will help, but I will likely still have very basic questions that others may clearly have the answers for. Perhaps the next yokel who comes around starting from scratch will be able to learn a little from my work.

Look out for future posts!