All posts by Alistair Lattimore

About Alistair Lattimore

My name is Alistair Lattimore, I'm in my very early 30's and live on the sunny Gold Coast in Australia. I married my high school sweet heart & we've been together for longer than I can remember. Claire and I started our family in September 2008 when Hugo was born and added a gorgeous little girl named Evie in May 2010. You can find me online in the typical hangouts, Google+, Twitter & facebook. .

ORA-06502: PL/SQL: numeric or value error

Database, Programming

Today I came up against a very frustrating problem when writing some Oracle PL/SQL stored procedures and functions:

  1. ORA-06502: PL/SQL: numeric or value error

When I first wrote the stored function in question, I was using VARCHAR2 types for storage since it was the data type returned by an Oracle provided package. The function signature looked something like the following example:

  1. FUNCTION MyFunction
  2. (pData IN VARCHAR2)
  3. RETURN VARCHAR2;

The stored procedures and functions in question were being used with a lot of character information. Whilst running small sets of test data through the functions, everything was acting as expected. Unfortunately, as the test data sets increased in size I began to receive the ORA-06502 error.

As you may or may not be aware, within PL/SQL a VARCHAR2 type can store a maximum of 32767 bytes of information. When the ORA-06502 exceptions where taking place, this limit was being exceeded.

The thing that was so frustrating about the error, was that it wasn’t helping me identify the problem. In this particular instance, I had refactored a significant amount of PL/SQL and during that process changed some variables from VARCHAR2 into CLOB data types.

Oracle will allow you to pass a CLOB variable into a function that accepts a VARCHAR2, so long as the length of the CLOB is less than the maximum byte limit of the VARCHAR2. Since that wasn’t throwing a type conversion error in normal circumstances, it wasn’t something that I went looking into immediately as a possible problem when the Oracle ORA-06502 errors were thrown.

The solution to this particular problem is what you would expect, change the data types of all associated functions and procedures to use the CLOB data type:

  1. FUNCTION MyFunction
  2. (pData IN CLOB)
  3. RETURN CLOB;

After looking into the error in more depth, it can be thrown for virtually any generic constraint violation. The following simple examples would produce this error code:

  • assign a NULL value to a variable defined as NOT NULL
  • assign a value greater than 99 to a variable defined as NUMBER(2)
  • assign a CLOB with a length greater than 32767 bytes to a VARCHAR2 variable

I think it would have been a little more useful to a developer for Oracle to throw some sort of a type conversion error in this instance.

The ConTest, Anticlimactic Crap

TV

Channel Ten launched their latest game show offering named The ConTest last week. Like all new game shows, The ConTest was hyped as the single greatest thing since sliced bread; unfortunately for Channel 10 – their latest TV show just didn’t cut the mustard.

With a name like The ConTest, you’d be inclined to think that the game show has something to do with conning your opponent. Channel Ten advertised it as the game show where having street smarts was far more important than being smart and that you could win the top prize without answering a single question correctly. The ConTest delivered on the last bit, you could win it without answering a single question right – however it really didn’t deliver anywhere near enough ummpphh on the con part.

At the start of the show, all of the contestants get the opportunity to tell their opponents about themselves whilst the opponents get a chance to ask some questions of them. The idea is the the opponents get a chance to suss out their fellow competitors and get a feel for each others personalities and more importantly if they are bluffing or not.

During the show, Andrew G asks a series of questions to the contestants. Unlike a traditional game show though, the contestants do not know how each other are going; they are flying blind. At the end of a round, without knowing how many questions their opponents got right – they have to decide if they are going to continue on or fold. If they fold, they get to leave the game with the money they have accrued so far. If no one folds for the round though, the person with the lowest score is automatically eliminated from the game.

After the first round, I was immediately thinking that it wasn’t all that exciting or filled with suspense. When the contestants went up to the next podium to decide if they were going to fold or not, I was expecting the other contestants to challenge each other about their scores. Unfortunately, that really didn’t happen and just like that you’re thrown back into another series of rounds, just like the first – boring.

When I first saw the game show advertised, I immediately expected it was going to be a game show version of the card game Bullshit. In the card game, you have to throw down as many cards as you can, as fast as you can whilst bluffing your opponent into thinking you actually have/had the cards in your hand that you’ve just thrown down. If one of your opponents think you’re bluffing, they can call bullshit at which point they check what cards you threw down. If you get caught out, you have to take the cards back – if your opponent gets it wrong then they need to pick up your cards. Bullshit is a fun and fast paced card game because you get the opportunity to read your opponent and challenge them when you think they are lying through their teeth!

Unfortunately, The ConTest cannot live up to the lofty expectations set by the classic card game; instead its a bunch of people answering questions and it doesn’t matter if they are right or wrong. At the end of each round, someone goes home without a lot of fuss and after a few rounds someone wins some cash.

Sorry Channel Ten, it’s as boring as bat shit to watch.

Google Image Labeler Included In Google Webmasters Tools

Internet, Search

In September 2006, Google released a new utility in the form of a game named Google Image Labeler.

The game aspect of the Google Image Labeler involves a pair of people. The contestants are chosen at random to play against one another based on who is online at any point in time. Each game lasts for 90 seconds and the contestants are shown the same series of images which they have to tag or describe with words or phrases. The contestants gain points when they match words or phrases with their opponent.

By gaining points when you match words with your opponent, Google are assuming both contestants consider the image to reflect the same object. At some point, Google will end up using this information in Google Images to provide a better quality of service to their customers.

The service aspect of the Google Image Labeler is of course about providing a higher quality of service to the Google user base. At the moment, Google rely on webmasters providing context around any images that they use on their web sites. As a simple example, a webmaster might:

  • provide a meaningful name for the image
  • provide a useful alt attribute, which describes the image in text format
  • provide captions for the image, which might be a more in depth text description of the image
  • talk about the image in the main content on the web page

Whilst this mechanism is very useful and in most cases accurate, it can also be inaccurate or abused. By relying on random Google users to categorise the images, the chances of an image being misrepresented are vastly reduced.

Having humans categorise the images also lends itself to Google producing software that learns how to recognise images. Google could attempt to identify what the images are on their own and use the tags or labels provided by the Google user base to essentially compare or validate the results.

When logged into the Google Webmasters console, you are now able to select whether or not you want the images on your site to be visible to the Google Image Labeler service. At this stage, I’m not quite sure why you would opt out of it – however Google are giving webmasters the option should they choose to.

If Google do end up walking the learning machine path, it could be interesting times ahead for the image searching service.

Yahoo! Pipes, Your One Stop XML Retooling Shop

Websites

Yahoo! have just released an amazing new service named Pipes. The short explanation of Yahoo! Pipes is that it lets you combine multiple streams of XML data into a single XML stream with contextual filtering and other magic along the way.

The name of the new Yahoo! service is inspired by the Unix pipe command, which allows you to seamlessly send the output from one program in as the input of the next program in the command line. A simple example of using the pipe command in the shell might be:

  • ps -A | grep firefox

In the above example, the first command (ps -A) is asking for a list of all the running processes on the machine. The output of that command is then sent into the next command (grep firefox), which filters the input and only returns information relating to the string firefox; you could think of the grep utility as a crude search facility. The little vertical bar (|) in between those two commands is the pipe.

If you then apply that type of logic to the Yahoo! Pipes service, the name makes a lot of sense. Yahoo! Pipes allows you to combine various XML feeds into a newly created feed. As you add or combine each feed of information, you can perform all sorts of processing along the way (just like the grep example above).

To give you an idea of the processing you can do along the way, some of them include:

  • adding as many XML feeds into a single stream as you want
  • adding filters, so that only items with a particular criteria are passed through
  • stripping out duplicate items
  • sorting the output of the combined XML stream
  • looping over each item of a feed and replacing blocks of content with other content or adding new information to each item based on the content of each one.
  • combining XML feeds which you have previously filtered.

Other than this being a really great utility for the geek or power user among us; I can see some amazing applications to certain businesses. Imagine for a moment you want to publish breaking news from around the world about a particular topic on your web site. Through combining various XML streams from other pertinent sources, you could then apply specific filtering to give you exactly what you want. You’ve then got your own targeted news reporting service that you only hear from when it has something to report; what a great signal to noise ratio.

Google Webmaster Link Explorer

Internet, Search

The development group behind the Google Webmaster Central are in over drive, they’ve just pushed out another update.

The latest addition to the Google Webmaster Central is within the tools section and it appears under the Link tab at the top. What you’ll see is an enhanced version of the link: query you could have performed through the normal Google search engine. You’re probably thinking that isn’t all that impressive and you’d be dead wrong. The newly updated Google Webmaster tools provides you with a far more comprehensive view of the links that are involved with your domain.

At the moment, the Google Webmaster link explorer is broken into two sections:

Internal
An internal link is defined as any link which links to another resource within your domain from within any other page within your domain. It is important to note that Google are defining your domain as your base domain name; so for this site it would be lattimore.id.au. As such, if this site used subdomains; all links from all subdomains to another resource within the domain would be considered an internal link.
External
As you can imagine, an external link is essentially the direct opposite of an internal link. Any link originating from a web page not within your domain is considered an external link.

Using the webmasters link utility, you can now explore how your site is being linked around the internet. As an example, you can drill down into your site and select a particular page. Once you’ve done that, you’ll be able to see all of the other web pages around the internet which are linking to that page. Likewise, if you are viewing the internal links for your site; you’ll only see the links for that page which originated from within your domain.

Keeping in step with all of the existing Google Webmaster tools, you are also able to download the tables of information for analysis outside of the webmaster utilities. You can download any particular table of information, such as a list of 30 links starting at the 90th link or you could also download all linking data available to you.

After clicking around within the new Google link utility, one thing that became apparent is how important it is to have a link strategy for your site. As an example, permanent links in the main navigation and the footer are benefiting the most within this site with approximately 200 links into each of them. However, as soon as you move away from the major sections – you see a rapid drop off with the next most highly internally linked page sitting at approximately 120 and most normal pages within a 10-60 link range.

Now that we have a utility which provides webmasters with such an accurate view of their sites; it seems like a really good time to start evaluating and formulating new strategies for internal and external linking.