Executing stored procedures in Oracle Database
Sandeep Rai
I am trying to execute an Oracle stored procedure from Ensemble using
a business operation. I have been able to find limited information
regarding this. One of that is from esql.pdf file under section 5.5.1
Has someone done this. I have created a Oracle SQL gateway using the
JDBC gateway that we use to make calls to the oracle I can run an
insert query to populate the table in oracle but cant seem to execute
a procedure.
Any assistance around this would be greatly appreciated.
Attached is my method in which I try to call the proceudre.
Class GL.GLRecordOperation Extends Ens.BusinessOperation
[ ProcedureBlock ]
{
Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Parameter INVOCATION = "Queue";
Method InsertRecordProcedure(pRequest As GL.CreateGLRecordRequest,
Output pResponse As GL.CreateGLRecordResponse) As %Status
Set tQuery="{ ?=call create_record(?) }"
Set tSC = ..Adapter.ExecuteProcedure
(tRTs,.tOutParms,tQuery,"io",,pRequest.Name,)
Set pResponse.Id = tOutParms
}
XData MessageMap
{
<MapItems>
<MapItem MessageType="GL.CreateGLRecordRequest">
<Method>InsertRecordProcedure</Method>
</MapItem>
</MapItems>
}
}
Regards
Sandeep
Sandeep Rai
Just and update on the code. We have cut down most of the things and
made it as simple as possible
I am trying to execute an Oracle stored procedure from Ensemble using
a business operation. I have been able to find limited information
regarding this. One of that is from esql.pdf file under section 5.5.1
Has someone done this. I have created a Oracle SQL gateway using the
JDBC gateway that we use to make calls to the oracle I can run an
insert query to populate the table in oracle but cant seem to execute
a procedure.
Any assistance around this would be greatly appreciated.
Attached is my method in which I try to call the proceudre.
Class GL.GLRecordOperation Extends Ens.BusinessOperation
[ ProcedureBlock ]
{
Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Parameter INVOCATION = "Queue";
Method InsertRecordProcedure(pRequest As GL.CreateGLRecordRequest,
Output pResponse As GL.CreateGLRecordResponse) As %Status
{
Set tSC = ..Adapter.ExecuteProcedure
(.tRTs,.tOutParms,tQuery,"io",pRequest.LEDGERID,)
Set pResponse.Id = tOutParms.GetData(1)
Arturo Henry Torres Zenteno
--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
Neerav Verma
Thank You,
Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------
Joan Crawford - "I, Joan Crawford, I believe in the dollar. Everything I earn, I spend."
Sandeep Rai
I final got it to work with the help of some intersystems consultants.
try this
Method InsertRecordProcedure(pRequest As
GL.Request.InterfaceCollection, Output pResponse As
GL.Response.Interface) As %Status
{
set tSC = $$$OK
kill pResponse
set pResponse = ##class(GL.Response.Interface).%New()
Set tQuery="{ call xx_gl_interface(?,?,?,?,?,?,?,?,?,?,?) }"
Set pRequestCount = pRequest.InterfaceCollectionGroup.Count()
Set Counter = 0
While Counter < pRequestCount {
Set Counter = Counter + 1
Set tSC = ..Adapter.ExecuteProcedure
(,.tOutParms,tQuery,"iiiiiiiiiio",
pRequest.InterfaceCollectionGroup.GetAt(Counter).STATUS,
pRequest.InterfaceCollectionGroup.GetAt(Counter).LEDGERID,
pRequest.InterfaceCollectionGroup.GetAt(Counter).ACCOUNTINGDATE,
pRequest.InterfaceCollectionGroup.GetAt(Counter).CURRENCYCODE,
pRequest.InterfaceCollectionGroup.GetAt(Counter).DATECREATED,
pRequest.InterfaceCollectionGroup.GetAt(Counter).CREATEDBY,
pRequest.InterfaceCollectionGroup.GetAt(Counter).ACTUALFLAG,
pRequest.InterfaceCollectionGroup.GetAt
(Counter).USERJECATEGORYNAME,
pRequest.InterfaceCollectionGroup.GetAt(Counter).USERJESOURCENAME,
"")
$$$LOGINFO("Response from procedure is "_tOutParms.GetAt(1))
}
if 'tSC set pResonse.Id = "Fault Occured"
Quit $$$OK
}
Also make sure you specify all the settings in the different
parameters in the management console for the business operation that
is making the call over JDBC to Oracle DB.
for example
Name:
Comment:
Schedule:
Category:
Class:
Description: N/A
Enabled:
Foreground:
Log Trace Events:
Inactivity Timeout:
Pool Size: Specific Settings
Adapter Class:
ReplyCodeActions:
Retry Interval:
Alert Retry Grace Period:
Failure Timeout:
Alert On Error: False True
Archive IO: False True
DSN: jdbc:oracle:thin:@//<DB server name>:<DB Port Number>/<DB
Serivce ID>
Credentials: apps (make sure this credential name is created under
credential section of ensemble with the oracle user name and password)
JDBCDriver: oracle.jdbc.driver.OracleDriver
JDBCClasspath: C:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar
ConnectionAttributes:
JGService: JGGateway ( This is the java gateway you have as a
business service in your managment console
Stay Connected:
IN your java gateway parameters make sure everything is the same apart
from this setting which points to your java home
JavaHome: C:\oracle\Middleware\jdk160_11
HTH.
Sandeep
--------------------------------------------------------------------------------
On Dec 11 2009, 6:07 am, Neerav Verma <vnee...@gmail.com> wrote:
> Why don't you guys contact WRC if you not getting an answer here
>
> And when you solve it, post it here for all of ours use in future if we do
> need one
>
> Thank You,
>
> Neerav Vermahttp://www.linkedin.com/in/vneerav
> ------------------------------------------------------
> Joan Crawford<http://www.brainyquote.com/quotes/authors/j/joan_crawford.html>
> - "I, Joan Crawford, I believe in the dollar. Everything I earn, I
> spend."
>
> On Thu, Dec 10, 2009 at 1:43 PM, Arturo Henry Torres Zenteno <
>
> arturo...@gmail.com> wrote:
> > Hi Sandeep, Solve your problem? I have a similar problem and I have not
> > solved.
> > Regards,
> > Arturo.
>