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:

CREATE OR REPLACE FUNCTION find_dodgy ( p_string IN VARCHAR2 ) AS
v_num     NUMBER;
BEGIN
v_num := p_string;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END find_dodgy;

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

SELECT *
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.

Portable Applications

I’ve been working on a client site recently, where there was a massive restriction on what could be installed on the computers, and the registry was locked down.

Now I think this is fine – security is important and all, and obviously this is something that the client has enforced.  But, I like to use Firefox as a browser instead of Internet Explorer, and always use the wonderful UltraEdit as my text editor.

Client restrictions allow me to use a USB stick, but not to install anything on the machines, so I started to look at whether you can run some applications from a USB drive rather than installing them on the client.

Firstly,I emailed the guys at IDM Solutions, who make UltraEdit.  The answer was that you can’t install it onto a USB drive, but if you install it onto a hard drive and then copy the files onto USB, it works.  Hey presto – the first program was working for me!

Then I started searching for more portable applications, and found this site.  There is a whole range of applications that you can download and install onto USB, plus a handy little menu program (with backup software) to manage them all.  I think that performance is slower than from a hard drive (since it’s reading from the USB), but I’m using such a slow network that it’s not really an issue!  I’m just glad to have Firefox back working, which is what I’m using now.

One final thing to add – Portable Apps is completely free as well!  Now that really is a result – so good that I’m off to play Portable Sudoku now…

Hacking Access to Oracle e-Business Suite via Workflow

I recently worked with a client who did not like to give out additional EBS responsibilities without going through a convoluted approvals process.  Whilst I commend them for this approach to system security, they were let down in two key ways.

Firstly, this was a development system.  I appreciate that getting into practice with your development system is important, BUT employing a consultant on a daily rate, and then having them wait for four days before giving them access doesn’t make much sense to me!

Secondly, and most importantly, they gave me the APPS password for the database in ALL the environments! So, I had a database login for all the environments, but a query only
user for EBS access – quite how they intended for me to access the concurrent requests to change them was beyond me!

So, I started to look at how the responsibilities were assigned to the users in the database, which is defined within the Workflow directory service, specifically in WF_LOCAL_USER_ROLES.  So, on my own system (I can’t emphasize that enough – I would NEVER advocate running this on a system you shouldn’t), I put together an experiment to see if I could give any user ‘System Administrator’ responsibility.  The result of my experiment can be found in the attached files with this post.

Based on an inbound user ID (and the user needs to be setup within the Workflow Directory Service first), the code derives the originating system and system ID for the record (user, person or party) that you want to grant access.  The code then inserts a new record in the local user roles table for the system administrator role.  Checking the contents of the table afterwards shows that the user now has the appropriate responsibility.

Once the code has run, you will need to commit the transaction before continuing.  Logging into the system as that user will now show that they have System Administrator responsibility.

As mentioned above, NEVER run this in an environment that you shouldn’t.  I’m a little wary about releasing the code here, but not only does it highlight how to give yourself responsibilities through the database, I’m hoping that it will also help (or at least flag a warning) to DBAs and other system administrators – by giving out a database password, it opens up the whole system in this way.  If you are a DBA or responsible for an EBS implementation, it’s probably worthwhile checking on a regular basis who is a system administrator, and what other roles are being given out within your system.

As ever, comments always welcome!

SQL script

Setting the Timestamp on UNIX files

I recently removed a file in UNIX accidentally – I was running commands from my command history and thought I was after the second most recent command, rather than the third most recent command.

Luckily, I’d kept a copy of the file on my Windows system, so all I wanted to do was to put the file back. As expected, though, when I put the new file over, it took the UNIX timestamp rather than the Windows one. One of the first things that I check for when comparing a development and production environment, if something works in one environment and not in the other, is the timestamps on the two files – if they are the same then the chances are that the files are identical. This isn’t always true, but I’ve always found it a good starter for 10.

So, now I have a file in UNIX that is dated today, when it should have been date three months ago, and I wanted to change the timestamp. In Windows, I’ve got a utility that I can enter the new timestamp, and it updates the file information – in UNIX there is a standard command to do it!

If you run the touch command, you can provide a timestamp that it puts against the file, so all I needed was to run

touch -t 200703062257 [filename]

and the system set the timestamp on the file to 06-MAR-2007 at 22:57 – exactly what I wanted to hide my error in deleting files!

This is probably old-hat to someone who knows UNIX well, but it saved me having to remember the dates that I messed up files, and at least now I have somewhere I can always search to remind me how it’s done!!

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.

Close