December 22nd, 2008 Matt
I was reviewing some code for an Oracle colleague of mine the other day before it was released to the general public, and one of my comments was that I preferred to use the ANSI syntax for joins that came into the database with release 9i.
The original developer then asked what the point was, since the code was always doing a full join anyway. One of the simple reasons that I like the ANSI syntax is because it is immediately obvious which parts of the WHERE clause are there to join tables together, and which parts are the “real” data selection criteria.
The place that I found best for explaining the syntax and what each means is the O’Reilly site here, which gives a great overview of what each type of join offers.
Posted in Oracle, Technical | No Comments »
December 18th, 2008 Matt
A thread on OTN recently (which I missed while I was ill) asked about displaying the history of a notification, and when I got round to it, I recalled the same thing being asked in September on Metalink here.
However, I knew that I’d addressed the problem somewhere, but didn’t know whether it was on OTN, WorkflowFAQ or on Metalink. So, to make sure that I can find it again, here’s the contents of the post from Metalink:
From: Dmitry Lidvansky 18-Sep-08 09:29
Subject: how to select notification history (?)
I need a select statement that returns notification history as it shown in notification:
http://www.4freeimagehost.com/show.php?i=b2d4948a2dbf.png (see image)
please help
it seems that WF_ITEM_ACTIVITY_STATUSES/WF_ITEM_ACTIVITY_STATUSES_H does not contain all neccessary information for example, Delegate action
From: Matthew Searle 18-Sep-08 10:39
Subject: Re : how to select notification history (?)
Hi,
This should do the trick:
1 CREATE OR REPLACE FUNCTION get_hist ( p_nid IN PLS_INTEGER
2 , p_disp_type IN VARCHAR2 DEFAULT WF_NOTIFICATION.doc_text ) RETURN VARCHAR2 AS
3 v_hist VARCHAR2(32767);
4 BEGIN
5 Wf_Notification.GetComments2 ( p_nid => p_nid
6 , p_display_type => NVL(p_disp_type,WF_NOTIFICATION.doc_text)
7 , p_hide_reassign => 'Y'
8 , p_hide_requestinfo => 'Y'
9 , p_action_history => v_hist );
10
11 RETURN v_hist;
12
13 END get_hist;
14*
APPS@SOLDEV2 on 18-SEP-2008 10:43:34> select get_hist(1574024) from dual;
GET_HIST(1574024)
————————————————————————————————————————
Action History
Num : Action Date : Action : From : To : Details
1 : 17-JUL-2008 12:03:45 : Submit : SYSADMIN : Searle, Matthew :
2 : 18-SEP-2008 10:21:00 : Request Information : Searle, Matthew : SEARLEM4B : Hello
From: Dmitry Lidvansky 19-Sep-08 10:10
Subject: Re : Re : how to select notification history (?)
3 v_hist VARCHAR2(32767);
that means notification history cannot be longer than 32767 ?
what if it will be so ?
From: Matthew Searle 22-Sep-08 11:50
Subject: Re : Re : Re : how to select notification history (?)
If it’s over 32767, then it will error 
The API returns a VARCHAR2, so that’s the limit – I’ve not tested it to see what happens if it’s too long.
If you want something that will cater for something bigger than 32K then you will need to look at the underlying code and pick out the cursor(s) it uses, then use that instead.
So, if you need to know how to display the full history of a notification from the notification ID, that’s how to do it.
Posted in Oracle, Technical | No Comments »
December 14th, 2008 Matt
Generally, there is no benefit / possibility of spell checking code – I’ve included a number of code samples in my documents and then been frustrated that the spell checker keeps telling me that VARCHAR2 isn’t a word.
However, there are some places where it’s important to ensure that what you type is what you mean. For example, if you have code that references a business event, then you need to make sure that the event name is correct before you start including it in the code. It would be really bad, for example, if you spellt, say, the name of your company badly. Particularly if it was a BIG company that people may need to trust can type properly.
Oh well, at least (as this image shows from my 11.5.10 system) that it doesn’t happen to any large software providers, eh?
"Oracel" event
Posted in General Computing, Technical | No Comments »
December 10th, 2008 Matt
I’ve recently installed a new instance of eBS 11.5.10 on my laptop, and everything seemed to be working fine. It took me AGES to get the thing installed and running, so I was really pleased that it was up and running quickly – I can now do a lot more when I’m mobile than I could before, since up until now I only had a laptop with enough power to run the database and Workflow server standalone.
BUT – as I got into the system, I eventually realised that none of my concurrent requests were completing, so I started to look into the log files a bit more. Eventually, I found that the log file for the concurrent manager was reporting
_ 1 _
Concurrent Manager cannot find error description for CONC-System Node
Name not Registered
Contact your support representative.
Now that wasn’t too much help to me – particularly since I am my own support representative – so I started to look into the problem in more detail. I ran a script called cmclean.sql which I had to download from Metalink , but that didn’t do the trick.
Eventually, I found this thread on OTN which gave me the answer – change the value of the system profile "Concurrent: GSM Enabled" from "Y" to "N".
Dead easy – and hopefully that may help you if you hit the same problem.
Posted in Oracle, Technical | 2 Comments »
December 4th, 2008 Matt
I’m sure that this has been done to death on a number of fora, but I never know where to look – so I’ll post it here.
I recently needed to remove spurious ^M characters from files in UNIX, which had been caused by FTPing the file in the wrong mode. There are a number of different ways that this can be done – here’s four of them:
1 – In vi, type :g/(ctrl-V)(ctrl-M)/s///
2 – In vi, type :1,$s/(ctrl-V)(ctrl-M)//
3 – In UNIX, run “tr -d ‘\r’ < oldfilename > newfilename
4 – In UNIX run “tr -d ‘\015\032′ < oldfilename > newfilename
The last one will also remove the DOS EOF character.
There are plenty of different ways to do it, but I tend to use numbers 1 and 3 of these.
Enjoy!
Posted in General Computing, Technical | 1 Comment »
