ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ERROR CALL SP

Post new topic  Reply to topic Goto page 1, 2  Next
 ERROR CALL SP « View previous topic :: View next topic » 
Author Message
iib
PostPosted: Tue Sep 08, 2015 5:38 pm    Post subject: ERROR CALL SP Reply with quote

Apprentice

Joined: 26 Jun 2015
Posts: 27

Good evening,

I have the following problem I have not solved, call a stored procedimienot as follows:

DECLARE COD_ERROR INTEGER;

CALL PRUEBA_SP(1, COD_ERROR);

CREATE PROCEDURE PRUEBA_SP(IN VALOR INTEGER, OUT COD_ERROR INTEGER) LANGUAGE DATABASE EXTERNAL NAME "SPPRUEBA";

Database I have:

create or replace PROCEDURE PRUEBA_SP(VALOR IN INTEGER,COD_ERROR OUT INTEGER)
IS
BEGIN
COD_ERROR:=2;
end PRUEBA_SP;

It is a simple test, the point is that the procedure worked properly but now it appears this error:

The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.

BIP2921

Actually that's not the mistake because I've checked several times. Anyone know soon what happens?
Back to top
View user's profile Send private message
akil
PostPosted: Tue Sep 08, 2015 7:49 pm    Post subject: Reply with quote

Partisan

Joined: 27 May 2014
Posts: 338
Location: Mumbai

The following will work

re-define the Oracle procedure to use NUMBER, instead of INTEGER
re-defined the ESQL external procedure to use FLOAT, instead of INTEGER
_________________
Regards
Back to top
View user's profile Send private message Visit poster's website
maurito
PostPosted: Tue Sep 08, 2015 11:02 pm    Post subject: Re: ERROR CALL SP Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

iib wrote:
Good evening,

I have the following problem I have not solved, call a stored procedimienot as follows:

DECLARE COD_ERROR INTEGER;

CALL PRUEBA_SP(1, COD_ERROR);

CREATE PROCEDURE PRUEBA_SP(IN VALOR INTEGER, OUT COD_ERROR INTEGER) LANGUAGE DATABASE EXTERNAL NAME "SPPRUEBA";

Database I have:

create or replace PROCEDURE PRUEBA_SP(VALOR IN INTEGER,COD_ERROR OUT INTEGER)
IS
BEGIN
COD_ERROR:=2;
end PRUEBA_SP;

It is a simple test, the point is that the procedure worked properly but now it appears this error:

The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.

BIP2921

Actually that's not the mistake because I've checked several times. Anyone know soon what happens?


you probably need to specify a schema name in your call. If you are certain the store procedure exists in the database, see under what schema name, then use the same one in the esql signature.
Back to top
View user's profile Send private message
iib
PostPosted: Wed Sep 09, 2015 5:36 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jun 2015
Posts: 27

Hi akil,

re-define the Oracle:

create or replace PROCEDURE PRUEBA_SP(
VALOR IN NUMBER,
COD_ERROR OUT NUMBER)
IS
BEGIN
COD_ERROR:=2;
end PRUEBA_SP;

re-define the ESQL:
DECLARE COD_ERROR FLOAT;

CALL PRUEBA_SP(123.4e5, COD_ERROR);

CREATE PROCEDURE PRUEBA_SP(IN VALOR FLOAT, OUT COD_ERROR FLOAT ) LANGUAGE DATABASE EXTERNAL NAME "SPPRUEBA";

and still receive the same error.
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.

Hi maurito,

the problem is not the scheme, as this would return error stored procedure not found.
Back to top
View user's profile Send private message
maurito
PostPosted: Wed Sep 09, 2015 5:41 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

iib wrote:

re-define the Oracle:

create or replace PROCEDURE PRUEBA_SP(
VALOR IN NUMBER,
COD_ERROR OUT NUMBER)
IS
BEGIN
COD_ERROR:=2;
end PRUEBA_SP;

re-define the ESQL:
DECLARE COD_ERROR FLOAT;

CALL PRUEBA_SP(123.4e5, COD_ERROR);

CREATE PROCEDURE PRUEBA_SP(IN VALOR FLOAT, OUT COD_ERROR FLOAT ) LANGUAGE DATABASE EXTERNAL NAME "SPPRUEBA";

and still receive the same error.
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.

Hi maurito,

the problem is not the scheme, as this would return error stored procedure not found.


names do not match, so you need to look at the definition of SPRUEBA in the database. I would leave the definition as INTEGER in your ESQL, that should not make any difference.
Back to top
View user's profile Send private message
iib
PostPosted: Wed Sep 09, 2015 6:19 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jun 2015
Posts: 27

Copy had evil in the forum, but if the integration is correct, if the error that would be an error of stored procedure name.

re-define the Oracle:

create or replace PROCEDURE PRUEBA_SP(
VALOR IN NUMBER,
COD_ERROR OUT NUMBER)
IS
BEGIN
COD_ERROR:=2;
end PRUEBA_SP;

re-define the ESQL:
DECLARE COD_ERROR FLOAT;

CALL PRUEBA_SP(123.4e5, COD_ERROR);

CREATE PROCEDURE PRUEBA_SP(IN VALOR FLOAT, OUT COD_ERROR FLOAT ) LANGUAGE DATABASE EXTERNAL NAME "PRUEBA_SP";

and still receive the same error.
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
Back to top
View user's profile Send private message
iib
PostPosted: Wed Sep 09, 2015 6:28 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jun 2015
Posts: 27

It is curious that happens to me when I connect to a Data Source for VPN, but in my database properly flows to the stored procedure call, the problem may be a security issue permits or infrastructure or database?
Back to top
View user's profile Send private message
maurito
PostPosted: Wed Sep 09, 2015 6:38 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

iib wrote:
It is curious that happens to me when I connect to a Data Source for VPN, but in my database properly flows to the stored procedure call, the problem may be a security issue permits or infrastructure or database?

the VPN user id may be different to the one that is in the database. That is why I suggested to add schema name in the esql AND in the stored procedure.
you probably have more than one proc with the same name in the database, but with different schema name.
Back to top
View user's profile Send private message
iib
PostPosted: Wed Sep 09, 2015 7:52 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jun 2015
Posts: 27

The scheme was set up and continues to present the same situation:

CREATE PROCEDURE PRUEBA_SP(IN VALOR INTEGER, OUT COD_ERROR INTEGER) LANGUAGE DATABASE EXTERNAL NAME "IGOL.SPPRUEBA";

The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.

Errror message that is returning is not correct.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Sep 09, 2015 7:56 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Check to see what the ODBC trace on the broker side says and check to see what the database says.

Review your IN and OUT qualifiers - you may need to change one or both of them to INOUT.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
maurito
PostPosted: Wed Sep 09, 2015 11:12 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

iib wrote:
The scheme was set up and continues to present the same situation:

CREATE PROCEDURE PRUEBA_SP(IN VALOR INTEGER, OUT COD_ERROR INTEGER) LANGUAGE DATABASE EXTERNAL NAME "IGOL.SPPRUEBA";

The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.

Errror message that is returning is not correct.

and do you have an IGOL.SPRUEBA procedure ?
From an oracle command line ( sqlplus userid/password@servername ) run a
Code:
DESCRIBE IGOL.SPRUEBA;
and post the results here.
Back to top
View user's profile Send private message
nelson
PostPosted: Wed Sep 09, 2015 11:15 am    Post subject: Reply with quote

Partisan

Joined: 02 Oct 2012
Posts: 313

Try to re-compile the SP in the database...
Back to top
View user's profile Send private message
maurito
PostPosted: Wed Sep 09, 2015 11:18 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

nelson wrote:
Try to re-compile the SP in the database...

??????
Back to top
View user's profile Send private message
smdavies99
PostPosted: Wed Sep 09, 2015 12:06 pm    Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

maurito wrote:
nelson wrote:
Try to re-compile the SP in the database...

??????

http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2006.htm

But as has been said the PROCEDURE Definition may well need a schema/user definition added so that the correct version of the procedure is invoked.
_________________
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
View user's profile Send private message
maurito
PostPosted: Wed Sep 09, 2015 12:12 pm    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

smdavies99 wrote:
maurito wrote:
nelson wrote:
Try to re-compile the SP in the database...

??????

http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2006.htm

But as has been said the PROCEDURE Definition may well need a schema/user definition added so that the correct version of the procedure is invoked.

NOT needed if CREATE or REPLACE.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ERROR CALL SP
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.