Apex, PL/SQL and Javascript

I am working on creating a tabbed region on a page so a task can be created and multiple resources can be assigned to the task. Tabs are cleaner and more intuitiveto the user. Task and resource tabs look like related data that is linked via the tabs I am following Dan McGhan's blog (thank you very much) on the subject and some fine YourTube videos on the subject.

My current development environment is HP Laptop with Windows 7 Home Premium, Oracle XE 10g and Apex 4.01.

Apex 4.01 has jQuery 1.4.2 internally and the namespace (shortcut) is $ and apex.jQuery. The jQuery-UI version 1.8 is also installed and part of the head section for the templates I am using. Here is the HTML after the template substitution variable #HEAD# is replaced:

<link type="text/css" href="/i/css/apex_4_0.css" rel="stylesheet">
<link type="text/css" href="/i/libraries/jquery-ui/1.8/themes/base/jquery-ui-1.8.custom.min.css" rel="stylesheet">
<script type="text/javascript">
var apex_img_dir = "/i/", htmldb_Img_Dir = apex_img_dir;
</script>
<script type="text/javascript" src="/i/libraries/jquery/1.4.2/jquery-1.4.2.min.js">
<script type="text/javascript" src="/i/javascript/apex_4_0.js">
<script type="text/javascript" src="/i/javascript/apex_legacy_4_0.js">
<script type="text/javascript" src="/i/javascript/apex_widget_4_0.js">
<script type="text/javascript" src="/i/javascript/apex_dynamic_actions_4_0.js">
<script type="text/javascript" src="/i/libraries/jquery-ui/1.8/ui/minified/jquery-ui-1.8.custom.min.js">

I wanted to have the latest stable jQuery library available with the namespace (shortcut) of $jq to avoid confusion, and I am easily confused. The steps were

1. Download jQuery library from http://jquery.com/
2. In Apex upload/create a new image, using the mouse clicks:
                                   Applications Builder>Application XXX>Shared Components>Images
3. Browse to and the jQuery library just downloaded
4. Modify the Header page template(s) of the them you are using.

Before:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="&BROWSER_LANGUAGE." xmlns="http://www.w3.org/1999/xhtml" xmlns:htmldb="http://htmldb.oracle.com" xmlns:apex="http://apex.oracle.com">
<head>
<title>#TITLE#</title>
<link rel="icon" href="#IMAGE_PREFIX#favicon.ico" type="image/x-icon">
<link rel="shortcut icon" href="#IMAGE_PREFIX#favicon.ico" type="image/x-icon">
#HEAD#
<link rel="stylesheet" href="#IMAGE_PREFIX#themes/theme_1/css/theme_4_0.css" type="text/css" />


After:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="&BROWSER_LANGUAGE." xmlns="http://www.w3.org/1999/xhtml" xmlns:htmldb="http://htmldb.oracle.com" xmlns:apex="http://apex.oracle.com">
<head>
<title>#TITLE#</title>
<link rel="icon" href="#IMAGE_PREFIX#favicon.ico" type="image/x-icon">
<link rel="shortcut icon" href="#IMAGE_PREFIX#favicon.ico" type="image/x-icon">
<script src="#WORKSPACE_IMAGES#jquery-1.6.1.min.js" type="text/javascript"></script>
<script type="text/javascript">var $jq = jQuery.noConflict()</script>
#HEAD#
<link rel="stylesheet" href="#IMAGE_PREFIX#themes/theme_1/css/theme_4_0.css" type="text/css" />


I edited page 1; JavaScript>Execute when Page Loads; to verify that two versions of jQuery were available.
$(document).ready(function() {
$("a").click(function() {
    alert("hello");
    alert('You are running jQuery version: ' + $.fn.jquery);
    alert('You are also running jQuery version: ' + $jq.fn.jquery + ' as $jq');
});
});

Notice the use of $ and $jq. The $.fn.jquery returns 1.4.2 and the $jq.fn.jquery returns 1.6.1.

So there are some learnings here.
1. /i/ in Oracle XE is not a directory per se. The jQuery library I created as an image was not found in c:\oraclexe\apex\images but it did load when the HTML was viewed in Firebug.

2. the noConflict needs to be run after the extra library is loaded and before the Apex internal jQuery is loaded.

3. I saw in the Apex forum that Apex used noConflict to create the namespace (shortcut/longcut) allowing the internal jQuery to be called with apex.jQuery or $. I did not see that in the HTML using Firebug.

I came from a land far, far away, where PICK was king and all was well. Ok, are you done laughing? Then, by all means continue.

Years later, I am in Oracle land.

I arrays in memory.

They are called Collections... there is a reason why they are not arrays.

In my other life it was

Dim Array1(x,y,z,..n)

Array1(x,y,z,..n) = Whatever
or
Whatever = Array1(x,y,z)

Not so in Oracle

In Oracle it is;
Fixed or variable array (varray v. nested table)
Single dimension (Array1(i))
and there are actions that can and need to be done to make em work

1. Declare
2. Intialize
3. Load if the initialize didn't do that
4. Work with it

I needed a two dimensional array. To make a long story short.

Create a record type
Create a table type based on the record
Initialize the table

Initializing requires

1. Extending
2. Loading directly using the v_table_type.fieldname (The names given in the record type creation)

Now it is ready for use.

This was hard for me because the setup and loading is not intuitive coming from the rest of the world. Collections are all about a table view of the data. I think of them as a subset of true arrays.

All that said, and it wasn't much, I may have missed the bigger picture if I needed an array with more than two dimensions.

Finally, in PICK. The language was built from the ground up to denormalize data using true nested tables. I see a lot of examples around books, beers, and employees but they use a column in a hetrogenous way (unlike data stored as multiple columns in a single column). Trun non-fomral form (which could be a form of mormalization as the tuples could contain unique, though many to one data in a column. PICK also had the report and manipulation commands built into the language so there was no additional effort to 'explode' a nested record.

A short example to better illustrate my point, would be an invoice

There is header data and detail or child records. In RDBMs that would be two tables. In PICK you had that option, but you could also create a record like this

Invoice_Table
Header fields:
--------------------
Invoice_Number
Customer
Ship_to
Bill_to'
Total_amount
Due_Date
Terms

Detail fields: (each filed containes multiple lines that are part of the detail set, row separators shown as ^)
----------------------
Line_number:    1^2^3^4
Item_number    12345^ABC123^AJ-B45CET^ND1
Cost                 25.23^30.00^12.95^0
Quantity            1^1^1^1

And PICK could had a true data dictionary that was more Business Objects like.

Don't get me wrong. I like Oracle and it has many great features, but tgrue nested tables are not one of them. They could be if the language was fully extended to create the required environment for clean SQL access.

Apex, PL/SQL and Javascript

Thursday, May 19, 2011

Add jQuery to the list

Thursday, January 13, 2011

Eureka! Collections are not arrays, they are tables!