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

In an APEX application i’m creating, I’m storing some output files that the public needs to get access to on the XMLDB server. The problem is, when i’m moving my code to production, I need to update the location to send the users to.

Previously, I’d used global variables in a Global package to store this value, but a better way is:

1
2
3
4
5
6
7
8
9
10
11
12

--Get the database host name
--Using this method rather than sys.v_$instance to access the hostname means that
--no grants are required - makes sense  
SELECT sys_context('USERENV','SERVER_HOST')
INTO l_hostname
FROM dual;
 
--Get the http port
SELECT dbms_xdb.gethttpport() INTO l_port FROM dual;
 
--piece it all together
l_return := 'You can find your output files by visiting : http://'||l_hostname||':'||l_port||'/public/rict/'||p_seq;

Hope this helps someone

Recently had a need for sticky hide/show regions in Oracle Apex 4.1.

Here’s how I done it.

Add the jQuery cookie library to your page template

Added the following line to the hide and show region template

1

$.cookie('#REGION_STATIC_ID#',$('##REGION_STATIC_ID# .hide:first:visible').length);return false;

So my hide and show region template looks like this:

1
2
3
4
5
6
7
8
9
10
11

<div class="hide-show-region" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>
  <div class="hide-show-top">
    <div class="hide-show-title">
	<a href="#" onclick="hideShow('region#REGION_SEQUENCE_ID#','shIMG#REGION_SEQUENCE_ID#','#IMAGE_PREFIX#themes/theme_21/images/right_arrow.png','#IMAGE_PREFIX#themes/theme_21/images/down_arrow.png');$.cookie('#REGION_STATIC_ID#',$('##REGION_STATIC_ID# .hide:first:visible').length);return false;" class="t1HideandShowRegionLink">
	<img src="#IMAGE_PREFIX#themes/theme_21/images/right_arrow.png" 
  id="shIMG#REGION_SEQUENCE_ID#" alt="" />
  #TITLE#</a></div>
    <div class="hide-show-buttons">#CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#</div>
  </div>
<div class="hide" id="region#REGION_SEQUENCE_ID#" style="float:left;">#BODY#</div>
</div>

Added this on page ready function to the footer of page 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14

//Hide and show regions based on cookie
$(".hide-show-region").each(function(){
$this=$(this);
//get the ID of the hide show region
$id=$this.attr("id")
//Get any cookie previously set
$cookie = $.cookie($id)
//If the region was open last time it was clicked then show it
if($cookie==1){
//alert("opening "+$id);
$child = $(".hide:first",$this);
$child.show();
}
});

Works a treat

In Oracle 10g, network access was pretty easy. Just fire up a UTL_HTTP request and voila, you’re data appears.
In 11h, everything is locked down, here is where Access Control Lists (ACLs) come into play.

To allow a user to access external resources, i.e. web content, you must create an ACL, assign users or roles to it and add websites and port numbers.

Look at the following example for one of my other sites.


begin
dbms_network_acl_admin.create_acl (
acl => 'utl_http1.xml',
description => 'HTTP Access',
principal => 'RIVPACS',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);

dbms_network_acl_admin.assign_acl (
acl => 'utl_http1.xml',
host => 'www.rict.org.uk',
lower_port => 80,
upper_port => 80
);
commit;
end;

As you can see, i’m granting the rivpacs user access to rict.org.uk which is a site my software accesses to get user preferences from.
Pretty easy when you know how.

Spent ages trying to get Andoid SDK  installed and configured so that I could get to grips with Appcelerator mobile development tool working.

Kepy on saying I didnt have a SDK installed

Oh how annoying.

I tried:

• Installing an older version of JDK
• Installing JRE, JDK, SDK, ImOK…. nada
• Changing my PATH variable until Id went down ALL the paths I could.

Turns out that the Android SDK doesn’t like the 64bit version of JDK.

So, you can either.

Install the 32 bit version, and set your PATH variable to point to it.

Download the ZIP version of the Adroid SDK, then after extracting it, run the SDK manager.exe file and set the JDK path manually.

Woohoo, fully working Appcelerator, ready for me to download Aptana (which Appcelerator recently aquired) and get started with a few mobie apps that I’ve been playing around with (in my mind)

Will post some examples when I get going.

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>