Friday, 9 December 2016

A Simple Guide to Oracle Data Pump

Introduction

In order to create a test system for one of our customers I needed to copy some schemas from the live oracle database. As the main schema contained a lot of objects, I realised that the best thing to use was Data Pump. Something I'd never used it before.

Oracle Data Pump first appeared in 10g and provided a newer and more flexible alternative to the 'exp' and 'imp' utilities from previous Oracle versions. It's a server based technology so you'll need access to the file directory structure on the database server. That means you'll need to be able to remote connect to both source and target servers.

Running an Export

We're going to start by running a schema export, it's quite straight forward, but we need to ensure that we have a directory object configured in Oracle. You could go ahead and just add one, but it's worth looking to see if there's one already set up that you can use. Log into the source server (in my case Live) and type the following:-

SQL> select directory_name, directory_path from all_directories;

The results should give you a column listing the object name, and a second stating it's actual directory path on disk. It makes sense to choose one where the directory actually exists, and where you have file creations rights, but that should go without saying!

If nothing suitable exists then go ahead and create one, and then grant yourself read and write on it.

You run the export from the operating systems command prompt, here's what I used:-

expdp <my user>/<my password> directory=<my directory object> dumpfile=<my export file>.dmp schemas=<schema to export>

When it runs it will output process to the screen and may take a number of minutes to complete (depending on number of objects and size of tables). If you'd prefer the progress can be sent to a file just by including the following paramater:-

  logfile=<export log>.log

Now if you go to the directory listed in the directory_path you should see your DMP file waiting for you. They can be quite large but normally they zip quite well to make file transfer quicker.

Running an Import

The obvious next step was to copy the dump file over to the target server (in my case the new test system), but don't worry about where to put it just yet. What we need to do again is find an Oracle directory object to use for the import process.

I used the same query as before:-

SQL> select directory_name, directory_path from all_directories;

If a directory object exists then move your dump file into it, or (as before) create your own directory object in Oracle.

Before running the import we need to connect as sysdba and create the empty schema in the test system.


SQL> create user newschema identified by pwd4newschema;
 

(NB. The "Identified By" parameter is the password.)

Finally at the command prompt run the import command:-

impdp <my user>/<my password> DIRECTORY=<my directory object> DUMPFILE=<my export file>.dmp

Again the progress will be reported to the screen. Scan through it and check you don't get any errors. I had some dependancy issues because two other schemas referenced in the packaged functions where missing. If this happens to you, copy the missing references and recompile your packages.

Wednesday, 7 September 2016

QR Code Treasure Hunt for kids

Introduction

Like many, I found the introduction of QR codes quite an intriguing idea. A simple pattern of squares which could be scanned with your smartphone and take you to a webpage or display a block of text.

It wasn't long before I started seeing these alongside museum exhibitions enabling visitors to see additional information, and this in turn has tended to drive public wifi access.

Anyway, my kids think these things are magical.


QR Code with a URL (Area 5.1 Cartoon)


Let's have a Treasure Hunt

One rainy day after the kids had been scanning QR codes on the back of the MagPi magazine I told them about my idea for using QR codes for a treasure hunt. They'd heard of this activity (although not with QR Codes) and were keen to try it, but getting decent clues seemed to be a bit of a problem for them. We googled a few websites that gave some examples, but we weren't impressed. I was sure I could do better.

So I hatched the idea to write my own clues and run a treasure hunt for them about the house and garden.


Building the Clues

There's various ways you can go about creating clues, but care should be taken to ensure you pitch the difficulty level right. You don't want them getting it too quickly, but it should force them to reason it out with a bit of thought. I decided to make mine rhyme and I used the http://www.rhymezone.com/ site to help me.

Here's a few of my examples:-
  • Mirrored Bathroom Cabinet: I’m a cupboard shiny and white, I reflect the world from this clean height. 
  • Door Mat: Stand on me, I won’t get sore, you’ll often find me by the door. 
  • Bike Shed: I’m never too sleepy, but I’m always two tired. In my wooden house your clue can be acquired. 
  • Nut Basket: At christmas time just get snacking, a basket of these will get you cracking. 

Try to include objects from all over the house and garden, including things from their rooms as well as everyday objects. Aim to have at least a dozen, and if you think your clue might be too hard, have a 2nd clue handy. (Before you go much further, review these with another person to ensure you have gauged the level correctly)

Generating QR Codes

Now you have your clues ready, the next step is to convert them into codes. The easiest way to do this is using one of the online webservices. (I used http://www.qr-code-generator.com/ which worked quite well, but it added a bit of a delay between downloads. This could be reduced by refreshing the page every half a dozen or so.)

The one thing I did notice is that longer clues increased the density of the pattern.

OK, once you've finished you should have a load of image files (make sure you unzip them if your chosen service compresses them). Next I added mine into iPhoto (because I use a Mac) and this enabled me to produce a contact sheet (you select it from the printing page). This was just an easy way of getting multiple QR images onto one sheet of paper so they didnt turn out huge when printed. You could manually add them into a word document, or similar if you want.


My QR Code contact sheet
Once printed, cut them into individual squares. Here's where it gets tricky, you'll want your phone handy with your favourite bar code reader (I was using RedLaser on the iphone).


Setting up the Treasure Hunt

I got into a real mess here and ended up with clues leading round in circles, so do yourself a favour and work out the route beforehand. Work through systematically using your phone to read each code. It took me about half an hour to set up 14 clues, so make sure your kids are out of the way, or busy. (Mine were playing Minecraft)

Then I left the first clue lying around where they could find it, and they took the bait. It was great fun watching them running about, trying different ideas and occasionally even working together. An hour later they reached the end where I'd left them each a packet of smarties.


Thursday, 25 February 2016

Oracle APEX - Updating data in a report using AJAX

Overview

I have a simple report showing hundreds of rows of config type information which I wanted to be able to alter quickly. I don't want to open each row in a form, or have to submit a tabular form to update any changes,.. it's all too slow.

What I want instead is a checkbox type column in the report which when clicked causes an AJAX request will be fired off to the server, which in turn runs an update query.

Sounds simple enough!

Setting up the Report

Let's start by looking at the report..
 


I've added a column called 'Ignore' which looks like a radio field/item. When clicked this will run an update to toggle the value in the database table.

Here's SQL that creates this in the report (notice I'm using a graphic of a radio button instead or a HTML form elements).

CASE WHEN sm.method = '3' THEN
         '<img name='||sd.personkey||' src="#IMAGE_PREFIX#pkt_rb_chk.gif" title="Ignored, click to remove" onclick="clicked(this)">'
       ELSE
         '<img name='||sd.personkey||' src="#IMAGE_PREFIX#pkt_rb.gif" title="Click to ignore" onclick="clicked(this)">'
       END ignore,

My database table stores an ignore as a '3' in the 'method' column. And, the reason why I'm using images is because I wanted to store the record key against the images using its name attribute. (It simplifies the javascript.)

Adding the Javascript

Next I added the following code to the Function and Global Variable Declaration on the report page. It uses some handy JQuery functions to make the code easier.

function clicked(obj){
  var personkey = obj.name;
  var img = obj.src.replace(/http:+\/.+\/+/,'');
  var checked;
  if (img == 'pkt_rb.gif') {
    checked = 1;
    var match = $(obj).closest('td').prev('td').text();
    if (match == 'Automatic' || match == 'Manual'){
      // Prompt for confirmation..
      var msg=confirm("This action will remove the current match, continue?");
      if (msg!=true){
        return;
      }
    }
  }
  else { checked = 0; }
  var ajax = new htmldb_Get(null,$v('pFlowId'),
            'APPLICATION_PROCESS=ToggleIgnore',0);
  ajax.addParam('x01',personkey);
  ajax.addParam('x02',checked);
  ajax.get();
  ajax = null;
  $(obj).closest('td').next('td').text('');
  if (checked == 1){
    $(obj).closest('td').prev('td').text('Ignore');
    obj.src = obj.src.replace(/.gif$/,'') + '_chk.gif';
  }
  else {
    $(obj).closest('td').prev('td').text('-');
    obj.src = obj.src.replace(/_chk.gif$/,'') + '.gif';
  }
}

The function takes the image object and uses a regular expression to find out the image name, and thus whether it's selected or not. (This gives us the initial 'ignore' status) We throw up a confirmation box if ignore is being set and then we start the AJAX call.

The AJAX call uses an APEX standard function called html_Get. This handles the request, the parameters you need to pass, and any browser variations. In our example we don't have any values being returned to the browser, but it would be easy to handle this by altering the 'get' command.

var gReturn = ajax.get();
// then do something with this value 

The final stage is to alter the radio image, and its hover-over text. Again we use a regular expression to alter the image name.

Next we need the back-end code that updates the table.

Adding an Application Process

You possibly noticed the AJAX function above is calling an Application Process called 'ToggleIgnore'.

So I created this Application Process using the name 'ToggleIgnore' and used the following anonymous block of PL/SQL code.

DECLARE
  l_person_key varchar2(20);
  l_checkbox varchar2(1);
BEGIN
  wwv_flow_api.set_security_group_id;
  l_person_key := apex_application.g_x01;
  l_checkbox := apex_application.g_x02;
  // Update the table
  switchboard_pkg.set_ignore(
    p_personkey => l_person_key,
    p_checked   => l_checkbox
  );
END;

The parameters are passed into p_personkey and p_checked, and in my example I've stored the update code in a packaged procedure. (It simply runs an update, but you shouldn't need me to go through that part).

Hopefully that's enough to help you get started building your own custom AJAX update methods.

Monday, 21 December 2015

TrendNet TV-IP562WI IP Connected Wifi Camera

First Impressions

I wanted a camera for inside the garage, so after a bit of research I picked up a TRENDnet TV-IP562WI. The specs are quite impressive for something less than £70:-
  • 2 way audio
  • Night vision up to 5 metres
  • Receive motion & sound detection video via email or FTP
  • Record to MicroSD card or network storage device (samba server)
  • Free mobile apps
  • Wireless N networking
  • Built in mic + external speaker socket
  • Dynamic DNS (support for NO-IP.com or Dyn.com)
  • Max 20 user accounts.
  • Video Codecs/Resolution 
Video resolution is..
  • H.264: 1280 x 720 up to 30 fps 
  • MPEG4: 1280 x 720 up to 30 fps 
  • JPEG: 1280 x 720 up to 5 fps (or 640 x 480 up to 30 fps)


TrendNet TV-IP562WI - Wifi Camera

It looks pretty good, small enough to easily fit in my hand, and it comes with a solid metal stand that doubles as a mounting bracket. Also in the box was a small 5v PSU, a cat5 cable for initial config, a software CD and assorted fittings for the stand.
 
I ran the software from the CD and had the camera up and running in less than 10 minutes. All you need to do is set the admin password and configure your wireless settings, then once it restarts you continue using a web browser. But that's where my problems started.


Mac Problems

Yeah I know, serves me right for owning a mac,.. but I did manage to solve the problems without resorting to a windows box. My newer Mac (running OSX 10.10) wouldn't display the login box, so it kept failing authentication. I tried using Firefox instead and updating Java, but looking online it appears it has problems with El Capitan.

When I switched over to my kid's Mac Mini, which was running 10.9, it worked perfectly. I was then able to continue config, renaming the device and setting it to run on port 82 rather than the standard 80. (This I did so that I could set up port forwarding on my router and see it on the web).

Mounting the Camera

My wifi signal is a bit weak in the garage, but mounting it high up on the wall got around any potential problems. The mount itself has a sturdy metal ring base with 3 mounting holes and a gimbal jointed adapter to a standard 1/4" thread. This gives you pretty much unlimited mounting options, but it's not overly robust.

I used a couple of ½ inch screws to fasten it to one of the wooden rafters, so it's out of reach, and unlikely to be tinkered with.


View from the installed camera using night vision mode

Updating the Firmware

Initially I added the App software to my iPhone and iPad and it worked without a problem. The app is pretty straight forward, search for devices and then enter the login details. Simple as that really,.. but it didn't work on my wife's iPhone. (I suspected I need to update the firmware.)

You update the firmware by downloading the image from TrendNet's (slow) website and use the camera's mini website to apply it. After a few failed attempts in Safari I switched to Firefox, but it still didn't complete properly. Instead of automatically restarting after the file uploaded I had to use the reset to factory defaults before it reported the new firmware number. After that it worked properly on my Mac using El Capitan.

Solving that iPhone Problem

I tried my wife's iPhone again, but it still didn't work. It was an identical phone to mine, running the same version of iOS. It had to be something else.

I compared the network settings of both devices and the only difference was one was using a web proxy. I disabled this and the problem went away.

Thursday, 3 December 2015

Copy & Paste Select Options from a Web Page

Introduction

Something that's always bugged me when I've looked after web based systems is struggling to grab a list of options from drop-down boxes on a web page. Sometimes you just want to be able to select them all, press control+v and paste them into an email.

Until now, the only way I found to get the list of options was to view the web page source, locate the <option> tags from the <select> and then manually hack the code about until you are left with a simple list.

... it's not exactly easy.

I've put up with this method for years for the odd quick request, but today I needed to get lists from 42 select boxes, and I didnt want to spend all day doing it.

There had to be a better way!!!

The Solution

I use Firefox for most of my web development and queries, mainly because of firebug, and I found this great add-on called SelectBoxTools which among other things allows you to copy options into a comma separated list.

Right-click from within your select box, and when the menu appears go down to SelectBoxTools > Copy as comma-separated text.

SelectBoxTools options.

Then when pasted into a text editor you'll get the visible name and it's value as a list. eg:-

 PK_ID,Pk Id
 OS,Os
 AVAILABILITY,Availability
 SUPPORT_DATE,Support Date
 RISK_LEVEL,Risk Level
 RATIONALE,Rationale
 NOTES,Notes


If you want to use the SelectBoxTools > Selection option, you'll need to select one of the picklist options on your web form first or you'll get the error:-

  TypeError:this.focused.options[this.focused.selectedIndex] is undefined

 Simple, effective and fast.

Monday, 12 October 2015

Running MAME on a Raspberry Pi


Wocka-wocka....MAME on a Raspberry Pi

Introduction

The other weekend we stopped off for a pub lunch and my boys spotted a coin operated video game table. They'd been given a coin to play after their lunch and the food just couldn't arrive quick enough. But it wasn't that long before they got their first tastes of Pac Man, Galaxian and Centipede.... games of my youth were surprisingly just as amazing to my own kids.

The MAME table we found in the pub.
Right there I hatched an idea to look into running MAME on our Raspberry Pi and maybe even built a gaming table that was something like it. I've seen joystick/control kits on ebay, and read various blogs where impressive cabinets have been built around the diminutive computer, so I figured there'd be plenty of help out there. (I wasn't wrong)


I did some research this weekend; there's a few software projects providing more or less the same thing, with PiPlay (formerly PiMAME) being one of the common choices. I decided to look for something that needed the least amount of effort and I found a project called Raspicade which supports USB controllers or direct wired connections to the GPIO.

Installing Raspicade

Download the card image from the Files page (I chose raspicade-samba-gamepads-keyboard-pikeyd_ready-B-B B-GPIOready-ES-20150513.img.gz), unzip the file and then use DD to write the contents to an empty MicroSD card.

On my Mac the command would be something like..


sudo dd if=raspicade.img of=/dev/disk4

(nb. I shortened the img file name, and ensure you find out what disk number the card is mounted on - use "diskutil list" if you need to find out.)

I did try using diskutility to do this, but it can only handle a single partition. The image has two partitions, one dos and the other linux.

it's a 2.5Gb image, it took a long time!.. but after that it was a case of inserting the flash card into the Pi and booting. After the usual linux boot messages you are asked some questions about sound, input type and which front-end to use. Then you get an amazing looking start-up screen and a simple, easy to interface.

Update - 13/10/15
Before you go much further if you are using a card bigger than 4Gb it's a good idea to resize the linux partition to fill your card. I used gparted to do this. It has a nice easy to use GUI and only takes a few minutes.


My first issue was how do I install ROM files?... even though Samba and ftp should be included, it had failed to correctly set my wireless network up (which isn't surprising). I had to resort to shutting it down and mounting the file on a Linux system (I used my Raspbian install) and copy the ROMs over via USB.


The ROM files are NOT freely available, I guess the content owners (from 30 years ago) haven't released the copyright because there's been a lot of reboot type stuff available on android/ios. So you have no choice but to find a torrent.


I downloaded the MAME 0.37b11 mame4all ROMs and installed them in the following folder..

/home/pi/mame4all-pi/roms/

I also copied the artwork and samples to the following directory:-

/home/pi/mame4all-pi/

Interestingly there's a folder in roms for SCUMMVM too,.. something to look into, as the boys have been known to play Day of The Tentacle from time to time. But I couldn't see an option in the menus in which to load them.

After replacing the card into the Pi we powered on and found all of our old arcade games ready to play. Most seemed to work (the odd one refused to start) but it was apparent that we had no sound. There was no obvious setting (other than the questions on boot-up) so I searched the forums.

Fixing the Sound

Initially I checked to see if the sound was being routed to the 3.5mm jack socket, but nothing there either. There had to be a setting fix because it works fine in Raspbian.

So you can press escape to exit to the console, and the Raspicade forum suggests two things that might fix the sound:-

1) Set the output source on the sound mixer to 2 (HDMI)..
  

sudo amixer cset numid=3 2

2) Then enable HDMI sound..

sudo nano /boot/config.txt

Find and uncomment the line..

#hdmi_drive=2

After I'd saved and then rebooted, the splash screen burst into life with glorious sound, and moments later our selected game responded with glorious 8 bit retro tones.

Big Smiles All Round :o)

Wednesday, 30 September 2015

Background Use of Mobile Network on iPhone

What's with WIFI on the iPhone?

After reviewing my Apache logs to resolve a Davical problem on my wife's phone I discovered something very interesting. I put together a PHP script to enable the caldav messages to be monitored more easily (I based it on my Minecraft log script), and it gave me log messages like this...

86.152.117.242 - rick [29/Sep/2015:00:16:00 +0100] "PROPFIND /davical/caldav.php/private/ HTTP/1.1" 207 2180 "-" "iOS/8.3 (12F70) dataaccessd/1.0"
86.152.117.242 - rick [29/Sep/2015:00:16:01 +0100] "PROPFIND /davical/caldav.php/pingucal%3A8800/.well-known/carddav HTTP/1.1" 403 670 "-" "iOS/8.3 (12F70) dataaccessd/1.0"
86.152.117.242 - rick [29/Sep/2015:00:16:01 +0100] "PROPFIND /davical/caldav.php/pingucal%3A8080/.well-known/carddav HTTP/1.1" 403 670 "-" "iOS/8.3 (12F70) dataaccessd/1.0" 


My phone was in fact connected to my home Wifi with an IP address of 192.168.1.157, but it was clearly using the Mobile network connection to talk to the Davical service because the log showed my phone was sending requests from 86.152.117.242.

It won't fix!!

I tried lots of different things, resetting network settings, disabling various wifi options, and even turning off mobile networking. But when I viewed the Apache log it was still (as if by magic) logging connections from the mobile network IP address.

I was using iOS8.3 and couldn't find any listed problems with it, so I took the plunge and upgraded to iOS9. Twenty minutes and 1 gigabyte download later I was upgraded, but the problem remained. I gave up and went to bed with Mobile networks clearly OFF.

I viewed the logs the next day - every hour the phone accessed the Davical service from 86.152.117.242

86.152.117.242 - rick [30/Sep/2015:06:32:09 +0100] "PROPFIND /davical/caldav.php/pingucal%3A8008/principals/ HTTP/1.1" 403 664 "-" "iOS/9.0.1 (13A404) dataaccessd/1.0"
86.152.117.242 - rick [30/Sep/2015:06:32:09 +0100] "PROPFIND /davical/caldav.php/pingucal%3A8800/principals/ HTTP/1.1" 403 664 "-" "iOS/9.0.1 (13A404) dataaccessd/1.0"
86.152.117.242 - rick [30/Sep/2015:06:32:09 +0100] "PROPFIND /davical/caldav.php/pingucal%3A8080/principals/ HTTP/1.1" 403 664 "-" "iOS/9.0.1 (13A404) dataaccessd/1.0" 


Now that's surprising!!.. just what does that OFF button do then?


It's all about the Cache

While grabbing log entries for this blog tonight I discovered something quite baffling... it now appears to be using Wifi correctly. My phone continued to connect via 4G from work (using another IP address)
86.152.117.242 - rick [30/Sep/2015:14:13:24 +0100] "PROPFIND /davical/caldav.php/pingucal/ HTTP/1.1" 207 2525 "-" "iOS/8.3 (12F69) dataaccessd/1.0"
213.205.199.17 - rick [30/Sep/2015:14:17:03 +0100] "OPTIONS /davical/caldav.php/paulcal/ HTTP/1.1" 200 613 "-" "iOS/9.0.1 (13A404) dataaccessd/1.0" 


Here half way through the afternoon we can also see an iOS/8.3 device, which is my iPad. It doesn't have a cellular networking radio, so it doesn't make sense that it should have that IP. Clearly there's some sort of caching going on with the Apache logging. My phone is showing as the iOS/9.0.1 device.

Once my iPhone connected to the Wifi it started showing correctly..

192.168.1.157 - rick [30/Sep/2015:19:04:32 +0100] "OPTIONS /davical/caldav.php/lehrlecal/ HTTP/1.1" 200 613 "-" "iOS/9.0.1 (13A404) dataaccessd/1.0"
192.168.1.157 - rick [30/Sep/2015:19:04:32 +0100] "PROPFIND /davical/caldav.php/lehrlecal/ HTTP/1.1" 207 2666 "-" "iOS/9.0.1 (13A404) dataaccessd/1.0"


So I can't trust the IP addresses in the Apache2 log. If you reset the Mobile Data statistics you can confirm for yourself that it isn't actually using Mobile data IF it's turned off. My faith in Apple has almost been restored,.. I'm sorry I ever doubted you!
BUT, with Mobile data turned on, the background process is showing it's being used instead of the Wifi connection. There might be a good reason for this, but as is often the case these days, as a consumer I have little or no control over it without turning the phone off or disabling Mobile data every evening.

Now I'm thinking I need to jailbreak my phone again!