web stats
Mirth Community - View Single Post - Oracle stored procedure call does not work
View Single Post
  #6  
Old 08-31-2018, 11:27 AM
nicovn nicovn is offline
OBX.2 Kenobi
 
Join Date: Oct 2006
Location: Belgium
Posts: 64
nicovn
Default

Hope this helps ...

Example Oracle function:

CREATE OR REPLACE FUNCTION GETPATIENTNAMEFORID
(
P_PATIENTID IN VARCHAR2
) RETURN VARCHAR2
IS
cursor V_C_PATIENT IS select PAT_LAST_NAME || ' ' || PAT_FIRST_NAME from PATIENT where PAT_PRIMARY_ID = P_PATIENTID;
V_PATIENTNAME VARCHAR2(1000);
BEGIN
open V_C_PATIENT;
fetch V_C_PATIENT into V_PATIENTNAME;

if V_C_PATIENT%notfound then
V_PATIENTNAME := 'Unknown';
end if;

close V_C_PATIENT;
RETURN V_PATIENTNAME;
END GETPATIENTNAMEFORID;



// Example javascript transformer code


var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver',
'jdbcracle:thin:@192.168.2.29:1521:XE', 'adt', 'adt');

var jdbcNativeConn = dbConn.getConnection();

var spstmt = jdbcNativeConn.prepareCall("{? = call GETPATIENTNAMEFORID (?) }");
spstmt.registerOutParameter (1, java.sql.Types.VARCHAR);
// 0000324550 is a sample value
spstmt.setString(2, '0000324550');

spstmt.execute();

var patientFullName = spstmt.getString(1);

logger.info("PatientName = " + patientFullName);

dbConn.close();
__________________
Nico Vannieuwenhuyze

Amaron.be
Reply With Quote