jQuery in Oracle APEX - An Introduction for Database Developers

Uploaded by skillbuildersDotcom on 30.04.2012

>> DAN: Welcome, everybody. Glad you could join us today. Letís talk a little bit about
what weíll be doing today. Iím going to introduce you to jQuery in APEX, but Iím
not going to do it as I would to a new group of new programmers because youíre not. Youíre
database developers, and that gives you an edge over new developers.
So Iím going to leverage that fact and introduce you to jQuery in APEX through a series of
analogies that relate critical concepts back to what you already know in the database.
I think youíll be surprised at how similar these technologies can actually be.
The first analogy: A webpage is like a dataset. As database developers, weíre used to data,
all kinds of it, but we rarely think of HTML webpages as just another dataset. We see something
like this and we flip out. Why? Because the data doesnít look quite right. Itís foreign
and out of our comfort zone.
When we see data that looks like this, weíre happy. It takes us very little time to understand
it. In fact, letís prove that and take a moment to get to know this data a little bit
better. We start with a header row. Basically, it describes the data that weíre looking
at. The first column is ID and the second is Parent ID, so we know that thereís some
kind of hierarchical relationship amongst the rows in this particular dataset.
The next column is Element and it looks like every single row here has an element. You
could say itís required. So this is really what weíre looking at, elements that are
hierarchically arranged, and then thereís a number of other attributes which seem to
be optional.
Okay, so thatís the basic dataset that weíre looking at. Easy to understand, right? Guess
what? Itís the same dataset we saw on the HTML page before. If I rearrange these rows
using the element column as the main attribute and then use these parent-child relationships
to lay out the elements hierarchically and finally, we take these other attributes and
put them in line with the element, it would look like this. Itís the same but different,
just another dataset, and with some practice, youíll feel right at home with it. We can
see here the same kind of relationships that we saw on the page before.
If I show you this one last time, we have HTML is the first row. Next, we have Head
and you can see that Head is beneath HTML. Title is linked up to Head. Going down a little
bit, we have Form -- Iím sorry -- Body, and Body links back to HTML. When we look at it
here, itís actually easier to see the relationship.
When people are new to web development and they hear the term DOM or document object
model, they can be a bit intimidated, but youíre already used to working with objects
in the database, and itís really very similar. The database object thatís most like the
DOM is a table.
Once again with our dataset, if we take our dataset here and move it into the database,
we get something like this, a table. Itís through tables and SQL that we select rows,
perform inserts, updates and deletes and even add new columns or remove columns.
The same is true when we take our HTML webpage and move it into a browser. The page comes
to life. This is how our browsers see the webpage, and through the DOM and JavaScript,
we can do the same things to our HTML webpage. We can select elements, insert, update and
delete them, even add and remove attributes.
So, when you do database development, itís important that you use the right tools, and
many of you are familiar with tools like Toad or SQL Developer. We use these tools for all
kinds of important functionality. A lot of that is listed here, things like debugging
code, manipulating objects. Itís really important to use the right tools. Some of you may still
be using SQL Plus as your main development IDE. I think you get the point.
In the web world, things are no different. We need to use the right tools for the job,
like in Firebug to the IDEs we use in the database world. Firebug is a plug-in for the
Firefox web browser. So that browser is a prerequisite, but with it, with the combination,
we get all kinds of functionality to help us work with our webpages.
In fact, a lot of the functionality we see listed her is the same that we saw for the
SQL IDEs, the ability to debug code, manipulate elements or, as theyíre often referred to,
objects. If youíre not doing web development with these tools, well, why would you do that
to yourself?
Letís take a moment to explore some of Firebug and get an idea of what itís capable of.
Hereís the webpage weíve seen a few times now. Behind the scenes, it is the content
we saw in the HTML webpage and of course, as you can see, Iím in Firefox. Down below,
you can tell I have Firebug installed. You see this little bug, and when I click the
bug, it essentially turns Firebug on.
The first thing youíll notice with Firebug is that we have a couple of tools over here
on the left. Next to those, we have a number of tabs. These tabs can be disabled by default.
When you first start working with the product, youíll need to enable them and the way you
do that, you just click on the tab and youíll see these arrows here and youíll see the
enable and disable. So, if they are disabled and you go to them, just click on that and
you can enable and disable.
The first tab weíll take a look at is the console. In most IDEs, like SQL Dev, people
are used to using dbms_output.put_line. It allows us to see output from that buffer.
Well, in JavaScript, we have something similar. We have whatís known as the console. Not
all browsers support console yet. Most of the newer ones do. Firefox does, and Firebug
allows us to see it.
This is the equivalent of dbms_output.put_line. Weíll do another ìHello Worldî example.
You can come down here and hit run, and you can see the code thatís executed and its
output. Just ignore that return value.
So a lot of the same stuff youíre already used to, and this particular technology can
be used for warnings, debug info and things like that. If you don't see -- after you install
Firebug into Firefox, if you donít see this window, itís closed by default. Just look
in the lower right-hand corner and click that arrow to open it up. Otherwise, itís available
at the bottom as a single line.
The next tab I want to show you is the HTML tab. Do you remember the old days of the web
-- let me shut this down for a sec. What would we do if we wanted to see the HTML behind
this page? We would right click somewhere in the content and weíd go over to ìView
page source.î For this particular page, because itís so simple, it actually still works.
But most webpages today are ten times this size easy, typically many more times that
size. So itís very ineffective to continue that technique. Firebug has the right tools
for todayís webpages.
Now we need to use this HTML browser, and you can think of this kind of like the various
object browsers in your IDEs. Only here, weíre working with the webpage. Notice it shows
us the content, and as I hover over various elements, theyíre highlighted above. Now
we can scroll down and get more granular. Likewise, I can do the reverse. Using the
ìselectorî tool, I can hover over the content above and it will find it below.
Now, once you find what youíre looking for, if you click it, it locks it in, and once
an element is selected here, you then have access to these tabs on the right which basically
describe that particular object or element.
Now, Iím not using any style attributes here but if I were, you could see them. You can
get layout information such as padding, border and margin, figure out why things are pushed
in or not, and then we have direct access to the DOM for this particular element. So
very powerful, and if youíre looking for something specific, say, ìjQuery is fun,î
you can type it in here and it will find it for you.
The next tab is CSS. Iím not using any external CSS style sheets, but if I were, I could see
them here. I could choose from the various style sheets, view them and see why things
look the way they do. Likewise, we have a script tab. I have to refresh the page to
see it, but I am using a script file in this one. This is how it works: You can select
that file and then see the contents. This one is compressed, so it doesnít do us much
good, but it often works better for CSS.
The next tab is the DOM. Itís similar to the DOM tab we saw before when I selected
a particular element, but this is in a more general sense, the entire DOM tree. Then finally,
we have NET, and NET can be used to help gauge performance-related issues with regard to
the page loading essentially, and you can see that bringing in external scripts adds
to the overall page weight and therefore requires more time to load, and you can get a breakdown
of that.
Again, this is a really simple page. So if you do this with the pages youíll be working
with on a regular basis, youíre probably going to see a lot more content here. Images
make a big difference, and you can figure out perhaps whatís taking a page so long
to load. Maybe images are not caching as they should be. So thatís Firebug in a nutshell.
In the database world, a lot of what we do starts with and depends on queries. With jQuery,
itís no different. Pretty much everything starts with a query, but in jQuery, we call
them ìselectors.î
In databases, a query gives us a result set. Typically, it will contain one or more rows
of data. jQuery selectors, on the other hand, return one or more jQuery objects and not
rows of data. We get jQuery objects. Weíll learn more about these objects as we go, but
first letís take a look at some of these selectors and starting here with example one,
if you can think back to our HTML table which, as you saw, I did, in fact, load that into
database. We can run a select count star from HTML table and of course weíd expect to see
the number of rows that were in that table, all of them. We can do the same thing with
jQuery, only weíre selecting them from the page.
Do you see the dollar sign? I like to sort of read that like select or an ASP for select.
What goes in between the parentheses is essentially your where clause. In this case, Iím saying
select star or everything. Itís a lot like the count star above and rather than use count,
we have to access a length attribute.
Going back to our webpage, if you took a look here and manually counted all of these elements,
weíd get a count of 33. Thatís how many rows are in that table. Using Firebug and
the console, I can put in some jQuery code that shows us this. Hereís our select statement.
Weíre selecting ìall attributesî -- or ìall elementsî rather -- and displaying
the link. Iíll run this. We get a count of 34, so relatively straightforward.
Next example: This time, weíre adding a where clause to our select statement. So, rather
than seeing all of the rows, I only want to see the rows where the element is an input.
Now, for those of you that are familiar with HTML, when you look at that webpage, you may
think you see two inputs, one and two. The fact of the matter is that there are three
Iím going to right click on this button. Iím going to go down to ìinspect element,î
again, a tool from Firebug. When I hover over this one, look above and see whatís highlighted.
This is a button element. Now, the one to its side is actually another input element.
Itís a type of submit, but it is an input element and, as you can see, they look identical.
Okay, so there are, in fact, three inputs here.
So, if we ran that query from SQL, weíd get a total of three rows returned and below that,
you can see the equivalent in jQuery. Weíre saying select where element is input. So,
when youíre targeting a specific type of element, this is how it looks. You actually
just put the name of the element between parentheses. Then, of course weíre just calling dot-length*
again. Bring in that jQuery code. So here weíre selecting just the inputs. Looking
for the length, we run this and we get back three. So far so good.
In this example, Iíve made a slight adjustment to the SQL. If you remember that dataset,
ID and Parent ID were actually numeric. In the web world, itís a bit different. If there
is an element in an HTML webpage that has an ID attribute, it is like a primary key
in the database. It should be unique across the entire webpage, but theyíre not numeric,
at least not usually. Theyíre just static text.
What Iíve done, Iíve moved the SQL around a bit to make this more clear but the idea
is the same. Weíre targeting a specific row by its primary key. In this case, we want
to see the row where the ID is wwwvflowform*, and that looks a bit confusing. The reason
for that particular ID is because I pulled the content or I should say used APEX as the
basis for this particular page, and thatís what the main form is called.
In the jQuery below, we see how we can target the attribute of ID. Itís with that hash
before the ID value. So weíre not targeting an element by its type now. Weíre actually
going for an elementís attribute. This particular attribute is ID.
Go back to the page. Iím going to start here, right click and go to ñ well, I can just
to HTML. Weíll scroll to the top. We see the form here. It sort of envelopes everything
else. Hereís that ID. We bring in the jQuery code to select it. We run it. Now weíre down
to one. So, as you can see in the first two examples, we got back multiple rows or multiple
jQuery objects. In the last example, we got a single jQuery object.
Hereís another example: This time, rather than doing a count as weíve been doing, itís
a more practical example. In SQL, we might select an attribute like, in this case, action.
Weíre selecting the action column from the table, again targeting that specific row.
In jQuery, Iím using the same selector we saw before. This time, Iím using the attribute
method to get the value of action.
Let me clear these out. In looking again at the page, hereís that form. Hereís the attribute
action. Itís flow.accept, wwwvflow.accept. Go back to the console, bring in that jQuery
code. Weíre selecting that using attribute to access action. We run it and of course
we see the value. So the same types of select operations that we have access to in SQL we
also have available to us in jQuery.
Letís take a closer look at attribute. Attribute, in addition to retrieving values, can also
be used to update them. Itís a lot like an update statement or the SET clause in an update
statement. Here, weíre doing an update in SQL to the HTML table, setting the size attribute
for a particular row, and weíre targeting one row because of course weíre referencing
In jQuery, we already know the selector for IDs, and weíre targeting the job title and
using the attribute, this time passing in two parameters. When you pass in a single
parameter, it returns the value of that particular attribute. In this case, two parameters, weíre
setting the value.
Here is the job title. We can see the input here. Here is the ID and hereís the size
attribute. Itís currently set to ten. The one above it, this one, is set to 30. So,
if we set them both to 30, they should be the same size. Letís go to the console, bring
in the jQuery code, our select attributed size to 30, and look above when I click run.
Watch that job title field. There we have it, same size.
So thatís interesting. The page is not static. Itís dynamic, and thatís made available
to the DOM, just like a table is in the database.
Of course, as we work with data in SQL, we often have to insert new data. We do that
via the ìinsert statementî and we can see an example of inserting a new row into our
HTML table here. One thing I want you to notice is the ID and Parent ID. What weíre essentially
doing here, if you were to look into that table where you see Parent ID, the value here
is five, okay, and if you look at that particular row, itís an H1 element. So what weíre doing
is adding an H3 element essentially beneath it and setting its text value to ìWhy not
give it a try?î
Below that, you can see the jQuery equivalent. We start with the H1 element and on this particular
page, thereís only one H1 element, so I know Iím just going to get that one. And ìafterî
works a lot like ìinsert.î Frankly, with jQuery, there are other methods we could use
for this depending on exactly what we needed, but ìafterî is a good example here. So weíre
saying after the H1, add and you can see the H3 along with its text value.
Hereís the H1. Iíll inspect that. We can see it here. So what Iím trying to do is
to add an H3 element directly below it. Letís take a look. jQuery code. We hit ìrun,î
look above, and there we have it. So we can add elements to the DOM that didnít exist
when the page first loaded. You can even now use Firebug to see them.
Of course, if we can do inserts, we can do deletes, right? In the SQL above, Iím deleting
the job title row from the table. jQuery below, equally as simple. We already know how to
select an element by its ID and then we simply call the remove method. Note that we do need
the parenthesis on the end. Again, hereís the job title. We bring in the jQuery code,
select that element and hit ìremove.î Look above, I hit ìrunî and itís gone, completely
removed from the DOM.
What Iím going to do now is sort of the equivalent of a rollback operation in, say, SQL Developer.
Iíve made some changes to the DOM at this point. Weíve added an element. Weíve removed
one. If I refresh the page, weíre going to go back to that source HTML file and bring
things back to the way they were. So Iíll just hit ìRefresh,î and then we have it.
Thatís a lot of code, huh? Perhaps I should take a moment first to explain what it is
that weíre trying to do here. So I want to hide a row. If we look at the content here,
weíll see that thereís an HTML table and beneath that table is an HTML body and within
the body we have one, two, three rows, and what I want to do is remove this row in the
middle. The problem is that -- actually, I don't want to remove it. I just want to hide
The problem is that I don't have a good way to select it. Notice that that particular
table row does not have an ID or any other attribute I could use to get at it. So the
only way for me to get it really is to use relationships that it has with other elements
on the page or, in the case of our dataset, other elements in the table.
Admittedly, the example is a bit contrived, but follow along. So we start down here. Iím
selecting the ID from the table where the element is table row, so Iím getting those
three, and Iím using the relationship with connect by to basically traverse the tree
and Iím scraping off the bottom there the bottom two rows.
So we get two rows from the first set here and then we scrape off of that, targeting
the -- getting rid of, I should say, the tops and now weíre left with the bottom -- Iím
sorry -- the middle. That gives us the ID weíre looking for and at that time, we set
its style to ìdisplay none.î
If youíre familiar with connect by in SQL, you don't need this example to understand
what it does. It basically allows us to traverse the hierarchical relationships in our databases.
Itís far more powerful than the tools that people have in other database systems, very
easy to use.
Hereís the equivalent in jQuery. In my opinion, itís even easier. What we see here is an
example of whatís known as chaining. We start with the table and thereís only one table
in that page, so we know weíre going to get one jQuery object. It returns a jQuery object.
Then with that, we can all the children method. The children, of course, gives us the table
body, but it returns it as a jQuery object. So then we can call the children again which
of course gives us the table rows and returns those rows as a jQuery object.
Then we can call EQ, and EQ is a way to filter a result set of jQuery objects, but itís
a zero-based index. So, if you want the first row, you say ìzero.î If you want the second,
you say ìone,î and thatís the one I want, and once we have that, we can use the CSS
method to display attribute ìequal to none.î
Hereís our page again. This is the row I want to hide. Go to the console, bring in
the jQuery code and hit run, look above, and itís gone. The fact of the matter is in this
particular case, itís not actually gone. Weíve only hidden it.
Before any of you call me out, I know that this particular trigger would not compile.
Again, itís a contrived example, but I think it illustrates the point. What weíre doing
with this trigger is basically looking to see when the value of a select elements changes.
Now, if you remember that webpage, thereís only one select element. So, if it changes,
then what weíre saying is if the new value is active, then update the table and set disabled
to known; in other words, remove the disabled attribute for the job title row. Here we have
the opposite. If itís not active, then set disabled equal to disable for that job title.
Itís pretty simple.
Hereís the jQuery equivalent. Essentially, what weíre doing is creating whatís known
as an event binder here. Weíre starting with selecting status which of course is our driver,
and weíre saying if it changes, then call this function. The function does not have
a name, but its body is simple enough. If this value is active, then find the job title
attribute and remove -- Iím sorry --find the job title element and remove the attribute
disabled. * find job title and set disabled to disable. So, if itís not active, weíll
disable job title. If itís active, weíll make it available.
Refresh this. Okay, so hereís job title and notice if I toggle to inactive now, I can
still access both elements in active, no change. Letís put a trigger on the page. Hereís
the jQuery code, just like we saw. Weíre starting with status and on change, weíre
calling this function. Iíll run this, and nothing happened because all weíve done is
set up the trigger. The trigger is now waiting, so when I change status from active to inactive,
now I cannot access this element. I can access this one, not this one. We change it back
to active. Ah, now I can get in there.
One last change to this page. We have a reset-form button here. Right now, it doesnít do anything.
If I want it to do something, I could use JavaScript, jQuery. In this case, Iím targeting
the button. And remember that this oneís a button, this oneís action input. So when
I target button, Iím only going to get one. Weíll call this function if itís clicked.
Call this function, and Iím getting the select and setting its value to nothing and then
finding the input. If you remember, there are three, but I don't want all three. I only
want the inputs where the ID does not equal submit form. In other words, I want this input
and this input, and Iím doing the same thing, setting the value to null or an empty string.
So letís see how this works if I type some stuff in here and in here and have this on
inactive. Right now, I hit reset form; nothing. We add this event binder. We click the button.
It clears it all out.
As you can see, there are a lot of concepts in the web world that are very similar to
what youíre already doing in the database world. If youíd like to learn more, I have
a couple of next steps listed here, starting with dynamic actions. For those of you that
are already using APEX, hopefully youíve heard of dynamic actions at this point. Basically,
what theyíre doing is taking the kind of code you saw me putting in the webpage and
making it declarative.
Iíll show you a simple example. Iím in a 4.0 APEX instance here and Iím showing you
the sample application. This is an application used for learning APEX. If we go to the product
tab, drill down on a product, weíll see a form page, and what I want to do here is some
of the stuff we were doing in the previous demo page. I want to basically hide some elements
or maybe disable them if something else on the page changes. In this case, if the product
available is changed to no, maybe I want to disable the list price. It sounds simple enough.
Letís see how we can do this with dynamic actions. Go to ìedit the page.î The driver
in this case is the list price. So Iíll right click that and Iíll go to ìCreate dynamic
action.î APEX can handle a number of dynamic actions. The standard are the easy ones to
start with
and because I started by right clicking the item -- some of these things are set correctly
already -- if the list price is equal to, letís say, no; in other words ñ did I go
with list price? I meant product available. Sorry about that.
If the product available is equal to no, then I want to do something. So, rather declarative,
as you can see. And if itís no, what do I want to do? Letís say if itís not available,
I want to disable something, and thereís a checkbox here that will create the opposite
false action; in other words, enable it if itís not equal to no. Then we have to specify
what it is that we want disabled or enabled, and in this case, weíre working with the
list price item. Weíll create that, run the page. Itís currently enabled. We switch this
to no and itís disabled.
In fact, if you look at this disabled compared with the disabled that I had in my example,
this oneís a bit more clear. In addition to actually disabling the element, the style
has been changed as well. The background color has been changed to give us a better indication
that itís disabled. So itís doing a little bit more for us.
So you may be saying to yourselves at this point, ìWait a second. Why am I even here?
If dynamic actions can do all of this for me, why do I need to learn this stuff?î Well,
the best analogy for that I can give you is itís a bit like saying, ìIím not going
to bother learning SQL because I can use a graphical query building tool to build my
SQL for me,î and thatís true. You can do that, but youíre really going to limit what
youíre capable of, the complexity of the type of queries you can create by not learning
If you look under the hood, the people that created dynamic actions were well aware of
this fact. If we go into the dynamic action, youíll see that jQuery or the ability to
work with jQuery was built right in. APEX now includes jQuery and jQuery UI built into
the product, and the dynamic action framework has been built to support that.
Another next step I recommend is to go to jQuery.com to start learning more about this
tool. Hereís the site, jQuery.com. If you look toward the bottom, youíll see some books
that are good for learning. I recommend this one written by one of the authors. In addition
to the books, if you prefer learning, you know, just through the site, youíll want
to go to this documentation part.
We worked a lot with selectors today, so hereís the API reference and hereís the page on
selectors. Itís not very well organized. Itís actually really hard to understand and
read. So I want you to remember this little trick. Look in the upper left-hand corner
for the dynamic API browser. Give that a click instead. It takes a little bit to load. If
you can remember this URL, API.jQuery.com/browser, you can go directly there. Isnít this a bit
more organized?
Letís look at selectors. Okay, it drills us down a bit. Letís learn the basics. Okay.
* ID, element, and so on. You could learn about class if you wanted. This is far better
than using the regular API documentation.
Before I get to the questions, I just wanted to let you know about a couple of things.
We have some upcoming training, a class on HTML CSS in JavaScript starting February 28th.
Weíre going to customize that class for APEX. Then of course we have our regular intro and
advanced APEX classes after that. In addition to training, we offer a number of other APEX-based
or Oracle-based services, from micro to long-term consulting and full application life-cycle
development as well as plug-in development. We have some contact information related here.
Also, if youíre using jQuery, I just want to show you on our page, you can just go to
APEX -- Iím sorry -- SkillBuilders.com/APEX to get to this page. We have a new plug-ins
tab. If youíre using plug-ins in APEX, make sure to check out this tab and download some
of these plug-ins and weíll have some new plug-ins and updates to existing plug-ins
coming out in the very near future.
Iíd like to open it up to any questions we may have at this time.