Against All Odds - HL7

Simple HL7 segment viewer with Coldfusion

Sat, 18 Jun 2011 02:26:00 -0700

In these days, I feel like myself to write simple HL7 segment view for myself. Because we need to check manually HL7 message text file with HL7 documentation whether the formation of our HL7 message file is correct or not. Another one is we gotta check the whether correct information is added into the correct segment sequence or not. That's why I need to write viewer program for saving our time to check like that. Here is the code for HL7 message viewer



	
	
				
				
				
			
			
					
				
			
		
		



	Paste HL7 message:

The outcome will be as follow HERE IS DEMO If you found any bugs, please leave message as comment.

MAX function is kinda slow in Oracle

Tue, 07 Jun 2011 21:06:00 -0700

In these days, I'm on HL7 project for my company. In this project, most of processes are created within Oracle Stored Procedure. Most of Oracle stored procedure, we need to retrieve the last record from table. At that time, we sometimes use "MAX" built-in function of Oracle. Using "MAX" function in Oracle is very simple and cannot write complicated query to get last record from table like query after query method with RomNUM. But the one I haven't noticed is the performance of retrieving data. That's why I've wrote two query statements and test in PLSQL developer. Using MAX function


SELECT MAX(PATIENT_ID) FROM NH_PATIENT


0.047 seconds

Using query and query Using MAX function


SELECT PATIENT_ID FROM
    (SELECT PATIENT_ID FROM NH_PATIENT
    ORDER BY PATIENT_ID DESC) CLONE_USER
WHERE ROWNUM = 1


0.031 seconds

So, we should avoid using "MAX" function if we really don't need to use.

Repeat String Function in Oracle

Tue, 22 Mar 2011 18:51:00 -0700

Now, I'm fixing the coding of HL7 interface project and clean any unnecessary coding and function from Coldfusion and Oracle Stored Procedure for performance. Among them, I've found that I've used looping in Oracle Stored Procedure for concatenating string as like follow.

         
V_FIRST_STRING := 'PPSHEIN';
V_LOOP := 10;
FOR LCOUNT IN 1.. V_LOOP
LOOP
	V_ZERO := V_ZERO || '0';
END LOOP;
V_FINAL_STRING := V_FIRST_STRING  || V_ZERO;

When I developed this coding, I didn't know there is RepeatString built-in function like Coldfusion in Oracle. My senior told me that there is RepeatString in Oracle but he forgot it. In this time, I was in rush and I decided to try with Looping. Now, it's time for me to consider the performance of my HL7 interface project. In Coldfusion, Every CF developers know repeat string in Coldfusion is RepeatString function. In Oracle, RepeatString build-in function is what? So, I've kept searching around Oracle forums and documentations. Finally, I found there is RPAD function in Oracle acts like RepeatString in Coldfusion. Usage is slightly like Coldfusion. [More]

Display the latest record with descending in Oracle

Tue, 22 Feb 2011 03:11:00 -0700

This week is very busy time for me. Because I have to implement HL7 segment with Oracle Stored Procedure. As we all know, Oracle SQL command isn't that same with Microsoft SQL. That's why some of command are very complicated for me to implement. Today, I need to retrieve the only one latest record from table with Oracle. In Coldfusion, it's very easy. To put maxrows = "1" and descending in query as follow:



	SELECT U_ID FROM TBL_USER
	ORDER BY U_ID DESC

In Microsoft, it's very simple. To put TOP command and descending in query as follow:


SELECT TOP U_ID FROM TBL_USER
ORDER BY U_ID DESC

In Oracle, it's not that easy. If we put ROWNUM and descending in query, the first record will be returned. Because ROWNUM doesn't work with Order By descending in query. That's why we need to do with query with query formula in Oracle as follow:


SELECT U_ID FROM
	(SELECT U_ID FROM TBL_USER
	ORDER BY U_ID DESC) CLONE_USER
WHERE ROWNUM = 1

Phweee...!!! Oracle crack my brain again. :)

Cusor loop and for loop in Oracle

Wed, 10 Nov 2010 23:57:00 -0700

Today I've wrote new complex stored procedure in Oracle for HL7 interface. As I mention in above post, I'm very new to Oracle and haven't known yet all of Oracle's functions and commands. But I need to create new Oracle stored procedure for existing project. When I creating Stored Procedure, I've found two different looping of Cursor. There are Open Cursor and For Loop cursor. Both of them are equal to retrieve data from table but different performance. As far as I know, Open cursor stands for limited data amount handling and will get slow when large data amount in it. For loop cursor can handle large data amount. [More]

Create Function in Oracle with Cursor Loop

Thu, 04 Nov 2010 00:54:00 -0700

In these days, I'm on HL7 project and need to create functions and store procedures in Oracle for message sending. In our HL7 standard, all of transaction will be manipulated by Oracle and don't make busy to Coldfusion due to we use less coding inside in CFML file and hope Oracle can manipulate the large data amount than CFML. Honestly, I'm very new to Oracle and still need self-learning Oracle's functions and commands for developing Functions and Store Procedures. First of all, I've recently finished reading Cursor Loop in Oracle. Because I need to concatenate the data come from table. [More]

HL7 interface and me

Sun, 24 Oct 2010 19:25:00 -0700

Well, I'm very new to involve at any interfaces in my life. Since August 2010, I have a chance to learn HL7 interface for my company which are going to integrate with another hospital software. When I need to send our information to another system, I always generate XML file and send them. When receive their information from them, receive XML file either and install our system. Once I heard HL7 interface, I thought they will also use integrated file as XML (because XML is standardize file system for integrated two systems). Actually, they use txt file as their integrated file. HL7 means Health Level Seven which dedicated to share information between two hospitals (maybe, to others as well). In HL7, there are so many standardize events and segments (which has one to many relationships means one event has so many segments). For example, A01 event called Admit Patient which has MSH, EVN, PID, PV1 and so on. [More]