Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Support (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=6)
-   -   Oracle stored procedure call does not work (http://www.mirthcorp.com/community/forums/showthread.php?t=218496)

shuhaibk54 08-10-2018 02:44 AM

Oracle stored procedure call does not work
 
I am trying to fetch value from oracle database. it always give me an error . some body will guide me on right . Is that some thing related to out parameter ,if yes how we can handle it . Even i tried diffrent link in mirth forum like ..
--https://www.mirthcorp.com/community/forums/showthread.php?t=1677
--http://www.mirthproject.org/community/forums/showthread.php?t=4075

---------------------------------------------------------------------------------
var result=0;
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbc:oracle:th in:@192.168.0.226:1521:dbdb25','Dbname','XXX');
var sql = 'exec MIRTH_TEST_PROCEDURE';
var str = '';

try {
objresult = dbConn.executeCachedQuery(sql);
while (objresult.next())
{
str = objresult.getString('Channename');
}
}

catch (e) {
logger.info('call to db failed: ' + e);
}
---------------------------------------------------------------------------------


My oracle stored procedure ---


create or replace PROCEDURE MIRTH_TEST_PROCEDURE (
--p_MODE in number,
P_CURSOR OUT SYS_REFCURSOR)

AS
BEGIN
OPEN p_CURSOR FOR
select * from HL7_CLIENT_CHANNEL;

END;

-------------- Error ---------------------------------------------
[2018-08-10 12:06:54,020] INFO (js-connector:?): call to db failed: JavaException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

siddharth 08-12-2018 09:53 PM

try

Code:

var sql = 'exec  <schema-name>.MIRTH_TEST_PROCEDURE';

shuhaibk54 08-14-2018 05:23 AM

ORA-00900: invalid SQL statement and tried different way
 
I am getting same error even if edit the script .

[2018-08-14 18:44:45,050] INFO (js-connector:?): call to db failed: JavaException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

and I tried different way . it returns false no error
--------------------------------------------------------
importPackage(Packages.ca.uhn.hl7v2.model.primitiv e);
importPackage(Packages.oracle.jdbc.driver);
importPackage(java.sql);
java.sql.DriverManager.registerDriver (new OracleDriver());

// Change connection info here !!!
// jdbcracle:thin:@<host>/<service_name>
// jdbcracle:thin:@<host>:<sid>
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbc:oracle:th in:@192.168.0.226:1521:XXX','CCCCC','XXX');
conn = dbConn.getConnection();
//var spstmt = dbConn.prepareCall("{ call store_adt_event(?, ?, ?, ?, ?, ?, ?, ?) }");
var spstmt = conn.prepareCall("{ call MIRTH_TEST_PROCEDURE(?) }");

spstmt.registerOutParameter(1, OracleTypes.CURSOR);
logger.debug(spstmt.execute()); /// it returns false.

//var result=spstmt.executeQuery();

//var result = spstmt.getResultSet();//getInt("pr_key");
//logger.debug("size=="+result.size());

//logger.info("new key=" + newnewKey);


dbConn.close();
----------------------------------------------------------

siddharth 08-14-2018 06:28 AM

You need to specify the schema name where the SP is located for oracle. something like dbo.storedProcedure where dbo is the schema.

I don't see that anywhere on your posted code. BTW, You should wrap your code in code tags.

shuhaibk54 08-30-2018 09:09 PM

I tired as you advised . Does anybody has a reference for oracle stored procedure call . I Searched entire forum and internet . Found Sql server related and it works well . Oracle related nothing found .

nicovn 08-31-2018 10:27 AM

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',
'jdbc:oracle: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();

shuhaibk54 09-03-2018 10:30 PM

Thanks for advice . as per your advice function works well. Similarly I tried calling stored procedure . Yous simple push may help to finish this task .

I tried stored procedure for return entire table as result data .It return null .

below is sample stored procedure which I can fetch from c# code .
-------------------------------------------Stored procedure -----------
create or replace PROCEDURE MIRTH_TEST_PROCEDURE (
--p_MODE in number,
P_CURSOR OUT SYS_REFCURSOR)

AS
BEGIN
OPEN p_CURSOR FOR
select * from HL7_CLIENT_CHANNEL;

END;


-------------------------------------------------Mirth code ---------------
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbc:oracle:th in:@fun-host:1521:dbtest','hell','pwd');
conn = dbConn.getConnection();
var spstmt = conn.prepareCall("{call MIRTH_TEST_PROCEDURE(?) }");
spstmt.registerOutParameter(1, OracleTypes.CURSOR);
spstmt.execute();
var result = spstmt.getResultSet();
logger.debug("size=="+result.size()) // Error it return null : TypeError: Cannot call method "size" of null
-------------------------------------------------------------------------------
Error------------------
// Error it return null : TypeError: Cannot call method "size" of null

// OrcaleDBtestCONNECTOR:
SourceSOURCE CODE:
135: //logger.info("Channelname = " + Channelname);136: //var result=spstmt.executeUpdate();137: //var result=spstmt.executeQuery(); 138: 139: var result = spstmt.getResultSet();//getInt("pr_key");140: logger.debug("size=="+result.size());141: 142: //logger.info("new key=" + newnewKey);143: 144: LINE NUMBER:
140DETAILS:
TypeError: Cannot call method "size" of null

---------------------------------------------------------------------------

nicovn 09-07-2018 09:44 AM

This should do the trick ...

// database reader javascript source code starts here
var dbConn;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver',
'jdbc:oracle:thin:@oracleserver:1521:XE', 'amaron', 'proctest');

var jdbcNativeConn = dbConn.getConnection();

var spstmt = jdbcNativeConn.prepareCall("{ call MIRTH_TEST_PROCEDURE (?) }");
spstmt.registerOutParameter (1, Packages.oracle.jdbc.OracleTypes.CURSOR);

// call the stored procedure
spstmt.execute();

// get the resultset object from the output parameter
var result = spstmt.getObject(1, java.sql.ResultSet);

// copy the resultset into a cachedrowset (required for mirth)
var cachedRowSet = new Packages.com.sun.rowset.CachedRowSetImpl();
cachedRowSet.populate(result);

// close the original resultset
result.close();

logger.debug("hoera! got the records");

return cachedRowSet;

} finally {
if (dbConn) {
dbConn.close();
}
}

// end of code


Best Regards

Nico


All times are GMT -8. The time now is 01:06 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation