Category: Oracle

Categories
Hints and Tips One-liners Oracle SQL

REGEXP Problem

A good friend asked me how to replace the last full stop (period) in a field with another character using a regular expression e.g. a comma. When I asked “what language?” it turned out he wanted to do this using REGEXP_REPLACE in Oracle SQL.

It’s a problem with a couple of solutions that I could immediately think of – one more “Oracle”, the other more “regular expression”.

First the Oracle solution:

REGEXP_REPLACE({FIELD},'\.',',',INSTR({FIELD},'.',-1))

This uses backward search capability of INSTR() to find position of the last ‘.’ in the field and return it so that the REGEXP_REPLACE only starts the replace from that point on-wards.

Now a “pure” regular expression version:

REGEXP_REPLACE({FIELD},'(\.)([^.]*$)',',\2')

This one is a bit more involved as it uses grouping and a negated character class ([^.] = “not a period”) as well as a back reference (\2).

 

Categories
Administration Humour Oracle Peoplesoft PeopleTools Performance Tuning

A Conversation with a PeopleSoft “Developer” – Part 2

Another amusing conversation and somewhat shocking “discovery”.

More poor performing SQL – obviously missing any sort of suitable index (on Oracle).

Me (to developer): Could you add a suitable composite (multi-column) index to table PS_xxxx please for columns C,B and A – there seems to be frequent access using these three columns that would be vastly improved by adding an index.

Developer: OK.

After a few days I notice new “alternate search key” indexes in the DEV environment. One for each of the columns A, B and C.

Me: Did you add that index?

Developer: Yes, but in the testing I did it wasn’t very much faster.

Continue reading “A Conversation with a PeopleSoft “Developer” – Part 2″
Categories
App Engine Hints and Tips Oracle PeopleTools PSVERSION Tools VERSION

VERSION 3.0 Application Engine!

If you are running a pre-8.53 version of PeopleTools you will (hopefully) be aware of the issues and requirements of using the VERSION Application Engine to correct problems with object versioning in PeopleSoft.

To summarise, the older VERSION App Engine just resets all the counter to 1! This is known as “Classic Mode” in the later release of the VERSION App Engine and is to be avoided at all costs really. You need everyone out of PeopleSoft, have to take all Application and Process Schedulers down, clear cache and run the App Engine from the command line to be 100% safe using it. Ugh.

Oracle recommends downloading and installing the most current VERSION Application Engine on all systems running PeopleTools >= 8.44 up to the latest release. Why? Because the later release is safer and better in all respects.

It has three execution modes:

  • Report Only Mode – Prefix your Run Control Id with the string “REPORTONLY” and you will get a report of the problems you have (if any) with versioning.
  • Classic Mode – Prefix your Run Control Id with the string “RESETVERSIONS” and the App Engine will run the “old way” – with all of it’s limitations (servers down, everybody out, cache cleared and run from command line). DO NOT USE THIS!
  • Enhanced Mode – Use and other Run Control prefix and the App Engine will just fix the versions that are wrong. It won’t reset back to 1 – just make everything line up correctly. You can even run this via Process Scheduler and with the servers up. What’s not to like about that?

Oracle Support Document 611565.1 (E-AS: Instructions Regarding the Use of the VERSION Application Engine Program) can be found at: Oracle Support Document 611565.1

Categories
Hints and Tips Oracle PeopleTools

PeopleSoft Date Fields Tip

Remember that you can put the letter t (or T) into a date field in PeopleSoft and the system will replace it with (t)oday’s date when you tab out of the field.

Actually, back in pre-web/pre-PIA versions of PeopleTools (when the application was a Windows 32-bit executable), there were other useful values you could use in a date field – from memory I believe “y”/”Y” gave you yesterday’s date. There may have been others too but as far as I am aware only “t”/”T” still exists in the web versions of PeopleTools.

Categories
Oracle Peoplesoft

Sean O’Byrne 22/12/1961 -> 02/10/2015

Sean O’Byrne was the project manager on my contract at ING Insurance in Prague, Budapest and Bucharest during 2012 and 2013. Sadly he passed away from cancer on 2nd October 2015 at 2 am. Sean was a larger than life character and although I knew him only for a short time he became a good friend.

RIP Sean – it was a pleasure knowing you and working with you – not to mention sharing a fair few Staropramen!

Categories
Hints and Tips Oracle SQL Server Tuning

Oracle vs SQL Server Terminology

Full Table Scan == Clustered Index Scan

In Oracle we are generally taught that a Full Table Scan is a bad thing. Whilst this isn’t always true (it depends on so many factors), the terminology used in Oracle makes it sound bad. With SQL server we have the concept of a Clustered Index (which is essentially just the table itself) but the equivalent terminology we see in the query plan is “Clustered Index Scan”. This sounds almost like a good thing to those from an Oracle background. But it isn’t. It’s a full table scan. 🙂