web stats
Oracle stored procedure call does not work - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2018, 02:44 AM
shuhaibk54 shuhaibk54 is offline
Mirth Newb
 
Join Date: Jan 2018
Location: Abudhabi
Posts: 11
shuhaibk54 is on a distinguished road
Send a message via AIM to shuhaibk54 Send a message via Skype™ to shuhaibk54
Default 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','jdbcracle: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
Reply With Quote
  #2  
Old 08-12-2018, 09:53 PM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 777
siddharth is on a distinguished road
Default

try

Code:
var sql = 'exec  <schema-name>.MIRTH_TEST_PROCEDURE';
__________________
HL7v2.7 Certified Control Specialist!
Reply With Quote
  #3  
Old Today, 05:23 AM
shuhaibk54 shuhaibk54 is offline
Mirth Newb
 
Join Date: Jan 2018
Location: Abudhabi
Posts: 11
shuhaibk54 is on a distinguished road
Send a message via AIM to shuhaibk54 Send a message via Skype™ to shuhaibk54
Default 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','jdbcracle: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();
----------------------------------------------------------

Last edited by shuhaibk54; Today at 05:58 AM.
Reply With Quote
  #4  
Old Today, 06:28 AM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 777
siddharth is on a distinguished road
Default

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.
__________________
HL7v2.7 Certified Control Specialist!
Reply With Quote
Reply

Tags
database error, oracle, stored procedure

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 05:48 PM.


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