Oracle APEX Locked ADMIN account

I recently locked out the ADMIN account to the APEX INTERNAL workspace.

To change the password is relativly easy, however, there are a few considerations, that I feel should be highlighted

Firstly, SSH into your DB Instance

sudo su - oracle

to log in as Oracle user

cd $ORACLE_HOME/apex

here you will find all the APEX scripts that are used for administration and installation.

Now SQL Plus as sysdba

sqlplus / as sysdba

First problem!
You must log into the Pluggable Database.. if you don't, you'll be changing stuff in the container, and this won't do you much good. Nowadays, APEX is installed at Pluggable level.

alter session set container=pdb_name;

(Obviously rename pdb_name to your PDB)

You probably already know this.. to change / reset the ADMIN password, you must run @apxchpwd

BUT in my case, this did not have any effect. Why?
Because the version of the script in the /apex folder was accessing a different APEX schema to the actual one I am running. There are many reasons for this.. but it took me a few minutes to make the connection...

So...
(if still in sqlplus use ! to shell)

!cat apxchpwd.sql

Near the top, you will see a schema setting:

alter session set current_schema = APEX_050000;

For me, I'm actually running 5.1 of APEX
Use your favourite editor to change to the correct schema.. in my case APEX_050100

alter session set current_schema = APEX_050100;

Now, when I run

@apxchpwd 

All is well!

Using Home Assistant to control Daikin SkyFi Air Conditioner

Originally posted on Whirlpool.net.au here
Github repo : https://github.com/coggs/homeassistant

Using Home Assistant to control the (AU) Daikin SkyFi Aircon Unit

I've configured SkyFi (only temperature readings and On/Off) and also enabled the Alexa listener for on/off commands.

Despite the communication issues with SkyFi, it works most of the time, and best of all, if you expose Home Assistant externally, you can control from outside of your LAN.

Here are the Home Assistant configs segments I am using.
Obviously replace the IP address and Skyfi Password with your own!
The Alexa listener IP is the IP address of your Home Assistant Server

With this config I can use Home Assistant UI to turn Aircon on and off, as well as monitor state and internal/external temps. Also, with some work on the curl commands, you could render all of the other response codes documented above.

customize:
switch.aircon:
friendly_name: Daikin Aircon
emulated_hue_name: "aircon"
icon: mdi:fan

switch:
– platform: command_line
switches:
aircon:
command_on: "curl -k --silent \"http://1.2.3.4:2000/set.cgi?pass=0000&p=1\""
command_off: "curl -k --silent \"http://1.2.3.4:2000/set.cgi?pass=0000&p=0\""
command_state: "curl -k --silent \"http://1.2.3.4:2000/ac.cgi?pass=0000\" | awk -F'[=&]' '{print $2}'"


sensor:
- platform: command_line
name: Inside
unit_of_measurement: "°C" 
command: "curl -k --silent \"http://1.2.3.4:2000/ac.cgi?pass=0000\" | awk -F'[=&]' '{print $24}'"
– platform: command_line
name: Outside
unit_of_measurement: "°C" 
command: "curl -k --silent \"http://1.2.3.4:2000/ac.cgi?pass=0000\" | awk -F'[=&]' '{print $26}'"


emulated_hue:
host_ip: 1.1.1.1 
listen_port: 8300
off_maps_to_on_domains:
– script
– scene
expose_by_default: true
exposed_domains:
– switch

Anki Overdrive and Oracle IoT Demo

At Oracle we've built an Integrated Cloud service demo, specifically using Anki Overdrive, Raspberry Pi with Bluetooth Sniffers, and IoT Cloud Service.

I'll do a write up on this, although I have done one or two already. (AppsLab.com )

In the meantime, here's some links to the various videos that we have done locally, and also some of our colleagues worldwide!

• Anki Playlist
• Original IoT demo video. Anki Racing Cars, plus integrated cloud services
• Updated version with Augmented Reality and Real Drone footage
• Oracle Openworld 2016 Video of the team explaining what it's all about

If you want help getting this up and running for your own purposes, we've got some details on this website anki.opcau.com and one of my colleagues, John Graves, who is the technological genius behind this, has published some of his work on Github

Oracle APEX 5 Early Adopter Program

Just in case it went by and you missed it, you can request an APEX 5.0 Early Adopter workspace.

I've got a workspace, but not done too much up to yet. Certainly looks nice!

Here's the link https://apexea.oracle.com/i/index.html

I'll certainly be trying the Maps integration very soon

I'm doing the 90km Sydney to Wollongong Bike ride for MS Australia!

See how my training is going on Strava!

Coggs' Sydney to Wollongong 90KM Cycle Ride

I am taking part in the 2013 Gong Ride in order to raise funds for people affected by Multiple Sclerosis (MS). MS is the most common disease of the central nervous system and affects more than 23,000 Australians.

Did you know?

* The average age of diagnosis of MS typically between 20 and 40 years of age
* MS affects three times as many women as men

MS Australia aims to minimise the impact of multiple sclerosis on all individuals affected by the disease, as well as their families, carers and the community, by offering a wide range of services, equipment and support. MS Australia's goal is to assist everyone affected by MS to live life to their fullest potential and secure the care and support they need, until we ultimately find a cure.

Please DONATE by clicking on the link above and help MS Australia support all people affected by multiple sclerosis.

Cheers!

Netbeans and APEX Rest Services

I've recently been playing with the RESTful services that you can easily generate in APEX.

I've set up Apache and Glassfish along with APEX Listener to expose them for use in Apps. I could document that, but it's easy enough to achieve. It could always be improved, but for now, I can at least see my web services.

By the way, to test my REST services, I am using https://apigee.com/console/others. The console there makes it really easy to enter your URL and test with header or body attributes.

Anyway, I decided to try to use Netbeans (7.3.1) to create some simple html5 apps that will use my REST services.

I ran into a small problem though. When registering my webservice, 

....I got the following error:

Cannot determine if the service is of type WSDL or WADL

I couldn't find a single reference to WADL and Oracle APEX anywhere.
So, I looked up WADL and how to write one. Perhaps, a WADL could sit anywhere, and just reference my REST services.

Firstly, my (simplified) REST web service.

This one has one module (the blue world), two templates, each with its own handler.

On my webserver, I have created a WADL to describe the module, the templates and its handlers:

<application xmlns="http://wadl.dev.java.net/2009/02">
 <resources base="http://myresturl:port/apexworkspace/module">
  <resource path="template1">
    <method name="GET"/>
  </resource>
  <resource path="template2">
    <method name="POST"/>
  </resource>
 </resources>
</application>

The "base" is the url that points to my REST services (being served by APEX Listener)
There is a resource element for each template, and within that a method which references the handlers.

Save that as a WADL and deploy to your webserver.

Now, when you add the resource to Netbeans, simply add the URL of the WADL rather than the services themselves.

In Netbeans, you should see your services:

I've not used the services in an App yet. Once I've got that done, I'll add the solution here.

Oracle APEX & Google Maps Integration

I've read so many blogs about how to integrate Google Maps into Oracle Application Express, but most of them were very old and maybe needed updating for the current versions of both Apex and Maps.

With that in mind, this was using Oracle Apex v4.2.1 and Maps API version 3

It actually isn't too hard to achieve, but there's one or two points not clear in the sites I read.

Once you have Apex running, all you need then is an access key from Google API. In version 3 you do not need to put the key reference in, so I'm not sure how it knows it's your key! Perhaps through the referrer sites.

This example assumes many markers, on a single map, and the marker details are kept in a database table

Anyway, here goes....

1. SQL Workshop > Object Browser
2. Create Procedure
3. Give your Procedure a name "MAPIT"
4. You can uncheck Arguments, we don't need any for this example
5. Next we'll need to enter some PL/SQL code ..

cursor c_klt is

select  
("MARKERS"."LATITUDE" || ',' || "MARKERS"."LONGTITUDE") AS geoloc, 
"MARKERS"."MARKER_NAME" as "MARKER_NAME"
from "MARKERS";

l_t number(3) := 0;

begin
htp.print('
<script type="text/javascript">
 function initialize() {
 var mapOptions = { 
  center: new google.maps.LatLng(-33.771214,151.080967),
  zoom: 11,
  mapTypeId: google.maps.MapTypeId.ROADMAP
 };

var map = new google.maps.Map(document.getElementById("map-canvas"), mapOptions);
');

for r_klt in c_klt
loop
htp.print('
 var geocoder = new google.maps.LatLng('||r_klt.geoloc||''||');
 
 var marker = new google.maps.Marker(
  {position: geocoder,map: map,animation:google.maps.Animation.DROP,title:"Marker: '||r_klt.MARKER_NAME||''||'"});
');

l_t := l_t + 1;

end loop;

htp.print('};
</script>;

<body onload="initialize()" style="font-family: Arial;border: 0 none;">
 <div id="map-canvas" style=" height: 400px; width: 800px;"></div>
</body>;
');

end;

Couple of things to note: (i.e. the things I did wrong)


• The ; at the end of the SQL statement
• The canvas name, just make sure it's consistent
• The height and width of the DIV "map-canvas". Change to match your page


6. Now create your page, keep it blank for now
7. Edit Page Properties (Right Click Page name - first item under page rendering)
8. In the HTML Header Section, HTML Header input box, add the API link:

<script src="http://maps.googleapis.com/maps/api/js?sensor=false" type="text/javascript">
</script>

9. In the same section, the Page HTML Body-Attribute:

onload="initialize()"

10. Now, apply changes and go back to Page overview
11. Right Click Region, to create a new region
12. Choose PL/SQL Dynamic Content
13. Give it a name, and on the next page, enter the Procedure name we created above. Don't forget the ;

MAPIT;

That's it. Make sure you've got geo coded data in your tables. (There's plenty of blogs about geo-coding on the fly)

Good Luck