I recently needed to provide mapping functionality in an APEX app I was developing.

In V3+ of Oracle apex, you can create interactive reports like this

Now when you click the cog, you geta map icon at the top:

I added a <li> element to the interactive report on-the-fly using jQuery (depending if it finds a div with an attribute of ‘gis’ on the page within the report).

I’ve written some jQuery for the onclick event of the map href to loop through the displayed rows, looking for a DIV with an invisible attribute called ‘gis’.  I then assemble these into a GEORSS file and store it in a public locations. The href from the map then calls a page showing the map and settings its source to the newly created file.

In reality, the file is actually a CLOB stored in the oracle database, and I use the ability of oracle to call a package from a URL to return the clob with the GEORSS file from my database table.

When clicked, the icon opens up an arcview  GIS map with the current interactive report data displayed.

I’ve used colorbox for jQuery to show the map in a lightbox style pop-up window.

I’ve been working a bit on Oracle APEX and jQuery intergration over the last couple of years.

here is a screenshot of the APEX home page.

And another of a sub page

..and one more for luck

Just today, I upgraded one of my Amazon cloud servers to Application Express 4.0.

Few wee problems along the way, but got there in the end.

Here is how I did it.

1. Download APEX 4.0 from here
2. Copy the .zip to an S3 bucket
3. Make the bucket public
4. Use PUTTY to log on to the AMAZON EC2 instance as root
5. su Oracle
6. cd /home/oracle
7. wget oracleapex4.0.zip (or whatever your S3 public URL for the zip file is)
8. Unzip *.zip
9. sqlplus sys/password
10. @apexins SYSAUX SYSAUX TEMP /i/ (takes around 40 mins)
11. @apxldimg.sql /home/oracle (takes around 10 mins)

Voila, job done. Not too bad.

Looking forward to getting my hands on all the new features. Especially, declaritive uploads, and my writing my own plugins

Mark

Have you ever wanted to pass data to an applicatino process but are fed uphaving to use a temporary global variable like GV_TEMP, or similar as a temporary placeholder for some data you want to pass from the page in javascript?

var req = new htmldb_Get( null, 123, ‘APPLICATION_PROCESS=ap_dosomething’, 0 );
req.addParam( ‘GV_TEMP_VARIABLE’,'The value’ );

urgh..

Well you can use a built in APEX item instead, like this:

req.addParam( ‘x01′, ‘The value’ );

and in your application process, you can reference this value like this:

l_temp := wwv_flow.g_x01;

The advantage of this approach is that you dont have to declare an indefinite set of globals to act as a go-between for javascript and plsql, and you dont have to declare anything up front.

It’s also more clear, I think, that the variable is transitory and reusable.

Question: How do I upload an oracle database schema file to s3 from linux command prompt

Answer: Download s3cmd

run ./s3cmd –configure

enter your access key and secret key when prompted

run your expdp command as oracle user

i.e. expdp sys/password schemas=rivpacs directory=DATA_PUMP_DIR dumpfile=rivpacs100309.dmp logfile=rivpacs100309.log

upload to your s3 bucket

i.e.

./s3cmd put rivpacs100309.dmp s://mybucketname

Voila. comments please..

Question: I’m testing an import for one customer and while importing the database dump file with Data Pump I’m getting the following error:

IMP-00017: following statement failed with ORACLE error 959:
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace ‘_$deleted$24$0′ does not exist

How do I remove the impdp message that the tablespace does not exist?

Answer: Data Pump impdp will return a ORA-00959 when a table definition specifies multiple tablespaces (i.e. a CLOB column stored in a separate tablespace. In these cases, the solution is to pre-create the table (punching the DDL with dbms_metadata) and use impdp with ignore=y.

By default, an export only exports the objects in a tablespace, not the tablespace definition itself.

In this case, the import is complaining because a tablespace with the same name as the export tablespace does not exist. You can fix the ORA-00959 error in several ways:

* Create the tablespace and use IGNORE=Y in the impdp syntax
* Import into another user ID with a different default tablespace name (fromuser touser)
* Use include=tablespace if you want to have the import pre-build the same tablespaces

Generally, in an Oracle migration, the schema and DDL are pre-created in the target database, and then the rows are imported into the new database using the Oracle imp utility with the IGNORE=Y parameter. The IGNORE=Y parameters tells Oracle to use the new DDL definitions instead of the DDL inside the import data file.

But in Oracle9i and beyond, you can use the new utility package called dbms_metadata that will easily display DDL and stored procedures directly from the data dictionary.

Been working with FLOT for a few hours in a carbon monitoring application i’m building over Oracle Apex.

It’s an excellent library that works well with jQuery, adn is very customisable.

here’s a screenshot of todays work:

The code behind is pretty messy right now.

Here’s the crux of it

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

<script id="source" language="javascript" type="text/javascript">
$(function () {
flotoptions = {
legend: {show: true, position: "nw"},
bars: {barWidth: 2250000000 },
points: {show:false },
lines: {show:false },
grid: { hoverable: true, clickable: true },
xaxis: { mode: "time",minTickSize: [1, "month"]}
};
flotplaceholder = $(".tabFlot");
 
//Click Handlers
$("#btn1").click(function(){eval("z.push(aa)");refreshPlot();return false;});
$("#btn2").click(function(){eval("z.push(bb)");refreshPlot();return false;});
$("#btn3").click(function(){eval("z.push(cc)");refreshPlot();return false;});
$("#btn4").click(function(){eval("z.push(dd)");refreshPlot();return false;});
$("#btn5").click(function(){eval("z.push(ee)");refreshPlot();return false;});
$("#btn6").click(function(){eval("z.push(ff)");refreshPlot();return false;});
 
function showTooltip(x, y, contents) {
$('&lt;div id="tooltip"&gt;' + contents + '&lt;/div&gt;').css( {
position: 'absolute',
display: 'none',
top: y + 5,
left: x + 5,
border: '1px solid #fdd',
padding: '2px',
'background-color': '#fee',
opacity: 0.80
}).appendTo("body").fadeIn(200);
}
$(".tabFlot").bind("plothover", function (event, pos, item) {
$("#x").text(pos.x.toFixed(2));
$("#y").text(pos.y.toFixed(2));
 
if (item) {
if (previousPoint != item.datapoint) {
previousPoint = item.datapoint;
 
$("#tooltip").remove();
var x = item.datapoint[0].toFixed(2),
y = item.datapoint[1].toFixed(2);
 
showTooltip(item.pageX, item.pageY,
"kg CO2 = " + y);
}
}
else {
$("#tooltip").remove();
previousPoint = null;
}
});
 
});
 
function refreshPlot(){
$.plot($(".tabFlot"), z, flotoptions);
};
 
</script>

You might occasionally get this error in Oracle Apex when submitting a page.
Alternatively, the page might not submit at all when you click a page button.

I must admit this had me stumped and i had to resort to disabling everything, then reenabling region items to find out what was wrong.

Turns out that APEX does not like the ‘name’ attribute of an element. Remove the name element and the problem goes away. Just use the ‘id’ attribute element instead.

1
2
3
4
5

before:
<input type="text" id="username" name="username" class="grey"/>
 
after:
<input type="text" id="username" class="grey"/>

Hope this little tip helps someone.

1. Vertical centering with line-height

1

line-height:24px;

When you have a container with fixed height you can use line-height property to vertically center the content.

2. Prevent oversized content to break fixed width floated layouts

1
2
3

#main{
	overflow:hidden;
}

When oversized content (i.e. wide image) is placed in fixed width floated container, it may break the layout. To prevent that use this trick. It will hide a part of the content but at least your layout structure will remain intact.

3. Prevent line breaks in links

1
2
3

a{
	white-space:nowrap;
}

This little trick will prevent line breaks on your links. I recommend using this with long text to avoid having links break into 2 lines.

4. Always show Firefox scrollbar

1
2
3

html{
	overflow:-moz-scrollbars-vertical;
}

Firefox hides vertical scrollbar by default. So, when you browse a site that have different page heights you notice a horizontal shift. This code will always display a scrollbar and prevent shifting.

5. Centering block elements horizontally

1

margin:0 auto;

For all modern browser this line of css is enough to horizontally center a block level element.

6. Remove vertical textarea scrollbar in IE

1
2
3

textarea{
	overflow:auto;
}

Textareas in IE have vertical scrollbar visible by default. If you want those removed (I know I do) use this line.

7. Force page breaks when printing your document

1
2
3

h2{
	page-break-before:always;
}

With this line of code you can control places where you want your pages to break when printing a document.

8. Remove active link borders

1
2
3

a:active, a:focus{
	outline:none;
}

Pipelined functions are an extremely useful feature of Oracle 9i and above. They allow you to present disperate nontable based data as a standard table whilst streaming the output so that processing can begin immediately.

I.e., they let you SELECT from a function much like you would SELECT from a table. Cool huh?
Why would you need something like this?

Let me go straight in with an example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE OR REPLACE function fn_ea_bio_file (p_path varchar2)
    return ea_bio_table
    pipelined
    is
      l_clob clob;
      l_n    pls_integer;
      l_line varchar2(1000);
      l_site varchar2(20);
      l_sortcode varchar2(10);
      l_abundance varchar2(5);
    begin
        SELECT XDBURIType(p_path).getClob()
        into l_clob FROM DUAL;
     if substr(l_clob,-1,1) != chr(10) then l_clob := l_clob || chr(10); end if;
     l_n := instr(l_clob,chr(10));
     l_line := substr(l_clob,1,l_n)||',';
     while l_n > 0 loop
        for x in 0 .. 5
        Loop
            --dbms_output.put_line('line:'||l_line);
            l_line := replace(l_line,chr(10),'');
            l_site := trim(substr(l_line, 1, 20));
            l_sortcode := substr(l_line, 21+(10*x),9);
            l_abundance := trim(substr(l_sortcode,9,1));
            l_sortcode := trim(substr(replace(l_sortcode,',',''),1,8));
            if l_sortcode is not null then
                pipe row(ea_bio(l_site,l_sortcode, l_abundance));
            end if;
            End Loop;
 
       l_clob := substr(l_clob,l_n+1);
       l_n := instr(l_clob,chr(10));
       l_line := substr(l_clob,1,l_n)||',';
     end loop;
   end;

Here. im reading a CSV file from the local XMLDB repository, but I could just as easily read a CSV from a local directory or a URL somewhere on the net.

I need to present the CSV data as a table so that I can query, group, sum count etc, basically manipulate and select as I like. Normally, I might read the entire CSV in, and store it in a temporary table. This approach can be very messy, i’d need to clean up afterwards, it would take time to read and process large files, i’d need to include some concurrent user functionality, so i’d have to store a user_id and a concept of a run (a sequence) number. I just want to select from a CSV file, is this so hard? Nope it isn’t. Read on.

In this code, i’m looping through a CSV file one line at a time.
Next, i’m setting some column values that I want to output.
Finally, i’m piping the columns out of the function.

I select from this function using:

1
2

Select * from table(fn_ea_bio_file(
        p_source_folder||'/'||p_source_filename))

What could be easier. The result look like they come from a table or view, and I can point the query to any source file I like (no setting up directories in advance or any nonsense like that).

I can join this CSV file to other table and basically treat it as I would any other internal data source.

The function returns rows as it processes them. Thats why it’s called a pipeline function. It will spit out data whilst it is still consuming and processing the remaining data. It streams it as it gets it.