| Author |
Message
|
| al000y |
 Posted: Sun Aug 03, 2014 10:03 pm Post subject: Unable to call an oracle stored procedure |
 |
|
Apprentice
Joined: 25 Dec 2013
Posts: 30
|
Hi All,
I'm trying to call an oracle stored procedure from my flow, I'm using websphere 5.1.
I'm getting this exception
[Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SBB.CRYPT_AUTH", line 4 ORA-06512: at line 1
This is my flow code:
| Code: |
CREATE COMPUTE MODULE TestCallProcedure_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();
DECLARE out2 CHARACTER 'sdd';
DECLARE a CHARACTER '32104569';
--DECLARE cursor REFERENCE TO OutputRoot.XMLNS.Test;
CALL DoSomething(a, out2);
RETURN TRUE;
END;
CREATE PROCEDURE DoSomething (IN in1 CHARACTER, OUT out2 CHARACTER)
EXTERNAL NAME "CRYPT_AUTH";
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
|
And this is the procedure code:
| Code: |
create or replace PROCEDURE CRYPT_AUTH
(CIF IN CHARACTER, OUT_HASH OUT CHARACTER)AS
BEGIN
OUT_HASH := 'sss';
END; |
Please help |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Aug 04, 2014 4:44 am Post subject: Re: Unable to call an oracle stored procedure |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| al000y wrote: |
| I'm using websphere 5.1. |
I really hope not. If you're really using Webshpere Message Broker 5.1 to run this ESQL then this is going to be the least of your problems.....!
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Aug 04, 2014 5:15 am Post subject: Re: Unable to call an oracle stored procedure |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Vitor wrote: |
| al000y wrote: |
| I'm using websphere 5.1. |
I really hope not. If you're really using Webshpere Message Broker 5.1 to run this ESQL then this is going to be the least of your problems.....! |
If my failing memory serves me right, there was never a V5.1 of Broker (or WMBIB) as it was called around V5 times.
Perhaps he means WMQ 5.1 which is really old.
Lets see what he says.
_________________
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 |
|
|
| mqjeff |
| Posted: Mon Aug 04, 2014 5:17 am Post subject: Re: Unable to call an oracle stored procedure |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smdavies99 wrote: |
| Vitor wrote: |
| al000y wrote: |
| I'm using websphere 5.1. |
I really hope not. If you're really using Webshpere Message Broker 5.1 to run this ESQL then this is going to be the least of your problems.....! |
If my failing memory serves me right, there was never a V5.1 of Broker (or WMBIB) as it was called around V5 times. |
The only .1 release that I know of is the 6.1 version. Nothing else had a .1 version. .0.0.1, sure.... |
|
| Back to top |
|
|
| al000y |
| Posted: Wed Aug 06, 2014 10:03 am Post subject: |
|
|
Apprentice
Joined: 25 Dec 2013
Posts: 30
|
ok it's and I cannot upgrade it 
Please help |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Aug 07, 2014 2:36 am Post subject: Re: Unable to call an oracle stored procedure |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| al000y wrote: |
[Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SBB.CRYPT_AUTH", line 4 ORA-06512: at line 1
And this is the procedure code:
| Code: |
create or replace PROCEDURE CRYPT_AUTH
(CIF IN CHARACTER, OUT_HASH OUT CHARACTER)AS
BEGIN
OUT_HASH := 'sss';
END; |
|
Try to invoke the stored procedure separately (from a tool like "SQLDeveloper").
If the error occurs there then it has nothing to do with Broker (and your ESQL) and you will likely be able to fix it "on the Oracle side".
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| al000y |
| Posted: Thu Aug 07, 2014 3:48 am Post subject: |
|
|
Apprentice
Joined: 25 Dec 2013
Posts: 30
|
| I tested the procedure in SQLDEveloper, it works fine. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Aug 07, 2014 4:03 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Can you try changing the OUT to INOUT on the procedure definition.
| Code: |
CREATE PROCEDURE DoSomething (IN in1 CHARACTER, INOUT out2 CHARACTER)
EXTERNAL NAME "CRYPT_AUTH";
|
_________________
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 |
|
|
| al000y |
| Posted: Thu Aug 07, 2014 5:08 am Post subject: |
|
|
Apprentice
Joined: 25 Dec 2013
Posts: 30
|
I defined the issue the input but I dont know the solution.
It is with mismatching data type. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 07, 2014 5:17 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What is the ODBC driver you are using?
Please be *exact* about the actual version of Broker you are using.
Pleaes be *exact* about showing the definition of ODBC DSN that the Broker flow is referencing. |
|
| Back to top |
|
|
| al000y |
| Posted: Thu Aug 07, 2014 5:19 am Post subject: |
|
|
Apprentice
Joined: 25 Dec 2013
Posts: 30
|
Broker Version: 5.3
ODBC Oracle version 11.02 |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 07, 2014 5:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| al000y wrote: |
Broker Version: 5.3
ODBC Oracle version 11.02 |
There is absolutely no version of Broker software labeled 5.3.
Please provide the full and complete output of the command "mqsiversion".
Please stop confusing the version of Broker with the version of MQ.
Please note that I did not ask for the version of Oracle.
I asked for the full and complete definition of the Oracle ODBC DSN.
I asked for you to be specific and exact about what *DRIVER* you are using.
If you do not understand these questions, then either my English is not very clear, or you need to ask the Broker administrator to provide you this information, or to start posting themselves.
This should not be any driver labelled "Oracle". It should be a driver labelled "DataDirect". |
|
| Back to top |
|
|
| al000y |
| Posted: Thu Aug 07, 2014 5:58 am Post subject: |
|
|
Apprentice
Joined: 25 Dec 2013
Posts: 30
|
Broker Version 5.00
Oracle ODBC Driver version 3.51 |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Aug 07, 2014 6:05 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
@mqjeff
Is mqsiversion part of the V10 command set?
on my V9 and prior I get the version with mqsiservice -v 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 07, 2014 6:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Right. So, again. I still don't care about the version of Oracle.
You should be using the ODBC drivers that come with your version of Broker. They are labelled "Merant" drivers at that hideously out of date version of the product.
You should not be using Oracle drivers. *THEY WON'T WORK*.
You should be using the ODBC drivers that come with your version of Broker.
If, somehow, magically, the Oracle database has been updated to a version that is too new for the drivers that come with your version of Broker, then....
Guess what? You're out of luck. Go find a new job that won't have you trying to patch together obsolete kit.
I guarantee the company you're working for takes it's employees exactly as seriously as it takes it's investment in current software. |
|
| Back to top |
|
|
|
|
