| Author |
Message
|
| iib |
 Posted: Tue Sep 08, 2015 5:38 pm Post subject: ERROR CALL SP |
 |
|
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 |
|
 |
| akil |
| Posted: Tue Sep 08, 2015 7:49 pm Post subject: |
|
|
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 |
|
|
| maurito |
| Posted: Tue Sep 08, 2015 11:02 pm Post subject: Re: ERROR CALL SP |
|
|
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 |
|
|
| iib |
| Posted: Wed Sep 09, 2015 5:36 am Post subject: |
|
|
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 |
|
|
| maurito |
| Posted: Wed Sep 09, 2015 5:41 am Post subject: |
|
|
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 |
|
|
| iib |
| Posted: Wed Sep 09, 2015 6:19 am Post subject: |
|
|
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 |
|
|
| iib |
| Posted: Wed Sep 09, 2015 6:28 am Post subject: |
|
|
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 |
|
|
| maurito |
| Posted: Wed Sep 09, 2015 6:38 am Post subject: |
|
|
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 |
|
|
| iib |
| Posted: Wed Sep 09, 2015 7:52 am Post subject: |
|
|
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 |
|
|
| mqjeff |
| Posted: Wed Sep 09, 2015 7:56 am Post subject: |
|
|
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 |
|
|
| maurito |
| Posted: Wed Sep 09, 2015 11:12 am Post subject: |
|
|
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 |
|
|
| nelson |
| Posted: Wed Sep 09, 2015 11:15 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
Try to re-compile the SP in the database...  |
|
| Back to top |
|
|
| maurito |
| Posted: Wed Sep 09, 2015 11:18 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| nelson wrote: |
| Try to re-compile the SP in the database... |
??????  |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Sep 09, 2015 12:06 pm Post subject: |
|
|
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 |
|
|
| maurito |
| Posted: Wed Sep 09, 2015 12:12 pm Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
NOT needed if CREATE or REPLACE. |
|
| Back to top |
|
|
|
|
