| Author |
Message
|
| sankritya |
 Posted: Fri Apr 24, 2015 2:43 am Post subject: Procedure call returns error |
 |
|
Centurion
Joined: 14 Feb 2008
Posts: 100
|
Hello All,
Unable to understand the reason of error for the following piece of code used for calling a stored procedure
| Code: |
CREATE PROCEDURE GetDocumentRecipient (IN P1 CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "RP.getDocumentRecipient";
CREATE COMPUTE MODULE StoredProcedure_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE LIS CHARACTER '803619' ;
CALL GetDocumentRecipient ( Environment.XML.Msg[], LIS ) ;
RETURN TRUE;
END;
END MODULE;
|
Oracle Procedure being called is
| Code: |
CREATE OR REPLACE PROCEDURE RP.getDocumentRecipient
(
p_recordset OUT SYS_REFCURSOR,
PlanId IN varchar2
) .. (further it is select * from some tables for the p_recordset)
|
Exception returned is
| Quote: |
ExceptionList
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbDataFlowNode.cpp
Line:INTEGER:1087
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_1
Label:CHARACTER:StoredProcedure.MQ Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_3
Label:CHARACTER:StoredProcedure.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp
Line:INTEGER:643
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_3
Label:CHARACTER:StoredProcedure.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.StoredProcedure_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:12.3
Insert
Type:INTEGER:5
Text:CHARACTER:GetDocumentRecipient(Environment.XML.Msg[ ], LIS);
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp
Line:INTEGER:1345
Function:CHARACTER:SqlRoutine::setupChildEnv
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_3
Label:CHARACTER:StoredProcedure.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2931
Text:CHARACTER:
Insert
Type:INTEGER:5
Text:CHARACTER:RESULT_SET_PARAM_0 |
Clearly I have not used this value RESULT_SET_PARAM any where in my code.
Checked the oracle driver also -- it is set to ProcedureRetResults = 1.
Procedure call is done as told in WMB documentation.
WMB Version - 7.0.0.4
Oracle - 11g |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Fri Apr 24, 2015 3:12 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Environment.XML.Msg[]
is what exactly?
Is it a CHAR?
Is it a Plane?
Is it Superman? 
I hope that this is a very old flow that you are changing. If not care to explain why you are using Environment.XML.??????
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| sankritya |
| Posted: Sat Apr 25, 2015 7:29 am Post subject: |
|
|
Centurion
Joined: 14 Feb 2008
Posts: 100
|
| It is just used for saving the response message which I plan to use later while preparing the response. Response is from refcurser so used []. It is not old flow so can use anything if it works. Use of XML is just for naming. I can change it to Environment.Msg[] also. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sat Apr 25, 2015 9:00 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
|
| Back to top |
|
|
| nelson |
| Posted: Sat Apr 25, 2015 10:41 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
How have you defined the PROCEDURE in broker?
Try something like this:
| Code: |
CREATE PROCEDURE GetDocumentRecipient (
IN PlanId CHAR
)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "yourschema.yourproc"; |
and call it this way:
| Code: |
DECLARE dbData ROW;
DECLARE PlanId CHARACTER '1213213212132';
CALL GetDocumentRecipient(PlanId,dbData.myRow[]); |
Hope that helps... |
|
| Back to top |
|
|
| sankritya |
| Posted: Mon Apr 27, 2015 6:11 am Post subject: |
|
|
Centurion
Joined: 14 Feb 2008
Posts: 100
|
Code changed .. exception changed.
| Code: |
CREATE PROCEDURE GetDocument (IN PlanId CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "RP.getDocument";
CREATE COMPUTE MODULE StoredProcedure_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE LISTItem ROW ;
DECLARE LIS CHARACTER '803619' ;
CALL GetDocument ( '803619', LISTItem.Msg[] ) ;
RETURN TRUE;
END;
END MODULE;
|
And the exception
| Quote: |
ExceptionList
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbDataFlowNode.cpp
Line:INTEGER:1087
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_1
Label:CHARACTER:StoredProcedure.MQ Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_3
Label:CHARACTER:StoredProcedure.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp
Line:INTEGER:643
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_3
Label:CHARACTER:StoredProcedure.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.StoredProcedure_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:8.3
Insert
Type:INTEGER:5
Text:CHARACTER:GetDocument('803619', LISTItem.Msg[ ]);
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp
Line:INTEGER:767
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:StoredProcedure#FCMComposite_1_3
Label:CHARACTER:StoredProcedure.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:GetDocument
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbDatabaseManager.cpp
Line:INTEGER:2670
Function:CHARACTER:ImbDatabaseManager::getDBProcedureParameterInformation
Type:CHARACTER:ComIbmDatabaseConnectionManager
Name:CHARACTER:ComIbmDatabaseConnectionManager
Label:CHARACTER:ComIbmDatabaseConnectionManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2921
Text:CHARACTER:The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
Insert
Type:INTEGER:5
Text:CHARACTER:SFG.RP.GETDOCUMENT
Insert
Type:INTEGER:2
Text:CHARACTER:1 |
And DB Procedure
| Quote: |
CREATE OR REPLACE PROCEDURE RP.getDocument
(
PlanId IN varchar2,
p_recordset OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN p_recordset FOR
select rownum, X.* from
(
SELECT distinct
****
) XYZ;
Exception
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND Error : '||sqlerrm);
RAISE;
WHEN OTHERS THEN
dbms_output.put_line('OTHERS Error : '||sqlerrm);
RAISE;
END;
/ |
I gues it is just one setting which is missed somewhere but do not know where  |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Apr 27, 2015 11:50 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Does the user defined in the ODBC connection have all the right access privs for the Stored Proc?
Have you tried accessing the DB using SQLDeveloper and the usename used in the ODBC connection and trying to call the SP?
If so does it work?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| sankritya |
| Posted: Mon May 11, 2015 2:35 am Post subject: |
|
|
Centurion
Joined: 14 Feb 2008
Posts: 100
|
| Thanks it was permission issue. WMB user did not had access over the database table. Strange though it was not returning any permission issue in exception tree. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon May 11, 2015 4:37 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| sankritya wrote: |
| Thanks it was permission issue. WMB user did not had access over the database table. Strange though it was not returning any permission issue in exception tree. |
Maybe because of the exception handling in your sql procedure ... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon May 11, 2015 4:40 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| fjb_saper wrote: |
| sankritya wrote: |
| Thanks it was permission issue. WMB user did not had access over the database table. Strange though it was not returning any permission issue in exception tree. |
Maybe because of the exception handling in your sql procedure ... |
maybe because the user didn't have access to the stored procedure and so WMB couldn't see it. |
|
| Back to top |
|
|
|
|
