Uploading SQL files to WordPress

As part of my recent search through my SQL archive in my last two posts, I wanted to add the actual SQL files to the post, but WordPress didn’t recognize the file format so it wouldn’t accept it – I kept hitting the error “File Type does not meet security guidelines” 🙁

I’ve since made some changes to the WordPress code which should allow me to attach the files, but have struggled to make it so that my changes won’t get wiped out when I upgrade the blog next time.

The way that I’ve got round this is to make a quick change to the file functions.php within the theme that I’m using.  By adding the code below to the existing PHP that was already in the functions.php file, my blog now allows me to upload text files with different extensions.  This can be extended further (if I need / want to) by adding different extensions and different MIME types to the code:

add_filter('upload_mimes', 'addExtraMimes');
function addExtraMimes($mimes)
  $mimes = array_merge($mimes, array('sql|pkg|pks|pkb|pkh|wft' => 'plain/text'));

  return $mimes;

Hope you find that useful – at least I know where to come back to if I need this on my other blogs!

Finding non-numerics in a column

A while back, I was working with a client where the configuration for eBS hadn’t quite been documented correctly, and two developers were using the same descriptive flexfield for different purposes.  The main problem with this, is that one developer was looking for numeric data, and the other was inserting a text string.  Since the column definition is a VARCHAR2, both values were allowed, but when the first developer selected data with a TO_NUMBER function, it failed.

This is only to be expected, and once we realized what was going on, it was fairly straightforward to determine the problem – the issue was how to identify which records contained the non-numeric data, and which did not.  Trying to select all records would fail and return an ORA-01722 error (invalid number) – so I knocked up the following function to identify the dodgy records:

v_num     NUMBER;
v_num := p_string;
END find_dodgy;

Running the following SQL statement finally identified the records that we were interested in:

FROM   oe_order_lines
WHERE  find_dodgy(ATTRIBUTE9) = 0;

From there, it was just a case of fixing the data – and updating the configuration documents to show what each flexfield meant!!

Raising Date Event Parameters

I recently hit an intermittent error when launching a Workflow process from an event subscription, which took me a while to resolve. Whilst passing a series of event parameters (in the standard WF_PARAMETER_LIST_T ADT), every so often, one workflow would not start. Of the twenty that I tried to start, one always failed – at least it was consistent, but it wasn’t immediately clear why this particular record failed to raise the event correctly.

Firstly, there was no sign of anything happening at all for the record, which took me a while to get to the bottom of. Eventually, I realized that there was no listener running for the WF_ERROR agent. I should have found it earlier, but it slipped my mind completely that the listener may not be running – eventually I looked at the number of records on the queue and found that there were at least 40 messages waiting to be processed. (Aside – the reason that the listener was not scheduled correctly will be in a future post).

When I ran the agent listener, there were a string of messages which identified what the problem was – part of the error stack reported:

Date error message

So there’s the start point to the problem – when setting the ORIGINAL_HIRE_DATE item attribute to ’21-NOV-1983′, the code failed.  The attribute was setup as a date type, with no format mask, so there is obviously a problem converting between the date formats.

The value of the parameter was being derived from a DATE field in the database, converted to a VARCHAR2 (to become an event parameter, which only accepts text values), and then converted back to a date to be stored as an item attribute – no wonder there were issues in the conversion process.

Delving into the code for the SetEventItemAttr procedure shows how the engine does the translation from text to date.  If the date attribute definition has a format mask, then this mask is used for the conversion from text to date.  If the item attribute does not have a date format mask, then the value that is used is the constant WF_CORE.CANONICAL_DATE_MASK – ‘YYYY/MM/DD HH24:MI:SS’.  If you try to convert a date from the DD-MON-YYYY format into this format, any date from the year 2000 onwards works (in that there is no error produced!  The date converted is still incorrect, but there is no error).  Dates earlier than the year 2000 will result in an error being raised – exactly what I was seeing in my test cases.

So – how do you get round the problem?  There are two ways that you can do this.  Firstly, change the code that builds the parameter list, so that the dates are passed to the event parameter list in the Workflow Canonical date format.  Alternatively, put a format mast on the attribute in the Workflow.  This is the mask that will be used instead of the value from WF_CORE, but make sure that it matched the format mask that is being used in the code which builds the parameter list.  I’ve adopted the second of these approaches, but that’s purely my preference – indeed you could make changes to both the Workflow item attribute definition and the PL/SQL.  As long as the date format masks match, that is the important thing – without them matching, either the dates will not be passed correctly, or the conversion will error.

What would have been nice would be for the WF_ENGINE conversion code to accept pretty much any date format and then store that in the database correctly. I saw code by Steven Feuerstein in 1998 that did this, so it would be a nice to have.  At least I (and you) know the reason why the date conversion errored and what to do about it.

Workflow Notification Fails When Action History Becomes Too Long

This was something that was flagged in the forum a while back by Joe T, but I’ve been working with a client on 11.5.9 who has just hit the same problem.

There is a bug in 11.5.9 that means that occasionally a workflow notification will error with the following error
Error - ORA-06502:
PL/SQL: numeric or value error ORA-06512: at "APPS.WF_NOTIFICATION", line 4995
ORA-06512: at line 5

This seems to happen when the action history for an item key exceeds a given limit – the breakpoint seems to be about 30 items, but it could be more or less (Oracle are no more or less specific on the threshold!). The error message appears instead of the actual notification – not something that you want your users to see!

Oracle have identified the bug that causes this as bug 4059291 – which can be resolved by applying patch 4946007. If you can’t apply the patch, then there is this workaround:

– Add an attribute called #HISTORY to the message causing the problem, and add &#HISTORY to the message body.

– If you want to view the history, then you can modify the file apwxwkfb.pls and change PROCEDURE GenerateExpClobLines:
l_line_accounting_enabled VARCHAR2(1);
l_line_accounting_enabled VARCHAR2(30);

Once you have made the change, bounce Apache and then re-test it.

This fix is approved by Oracle, and is documented in this note on Metalink.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.