View previous topic :: View next topic
|
Author |
Message |
Ashishpanpaliya
New User
Joined: 13 Oct 2017 Posts: 34 Location: India
|
|
|
|
Hi Everyone,
I am trying to execute below external stored procedure written in SQL and getting an error message
({? = call IBMUSER.ORDERNEXT(?,?)}
THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER 1 IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE. SQLCODE=-302, SQLSTATE=22001, DRIVER=4.22.29
Run of routine failed.)
Stored Procedure :-
Code: |
CREATE PROCEDURE ORDERNEXT
(IN NAME_IN CHAR(10)
,OUT SURNAME_OUT CHAR(10))
LANGUAGE SQL
FENCED
P1: BEGIN
DECLARE C_DLR CURSOR FOR
-- SELECT
SELECT SURNAME FROM ORDERNEXT
WHERE NAME = NAME_IN;
--FETCH
OPEN C_DLR;
FETCH C_DLR INTO SURNAME_OUT; |
Table Definition of ORDERNEXT
CREATE TABLE ORDERNEXT
(
NAME CHAR(10)
,SURNAME CHAR(10)
);
while executing, I am passing 'Ashish' ( also tried passing Ashish, "ASHISH")
but all failing.(I have created entry in table with name as Ashish.
I am using RDz to deploy and execute SP. |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
first , I suggest you to copy the CREATE definition parameters from already working SP in your project than defining by yourself with no parameters . Second check if you have trailing spaces in RDz Name field which makes you to exceeds 10 bytes. |
|
Back to top |
|
|
Ashishpanpaliya
New User
Joined: 13 Oct 2017 Posts: 34 Location: India
|
|
|
|
Quote: |
copy the CREATE definition parameters from already working SP in your project |
This is first time we are creating SP.
Quote: |
Second check if you have trailing spaces in RDz Name field which makes you to exceeds 10 bytes. |
yes, i have verified that. Infact same SP run perfectly if I make it native by removing parameter(FENCED). |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
If its not one of the below then , You need to contact DBA at your site.
Code: |
•The column is defined as a string and the host variable or parameter contains a string that is too long for the column.
•The column is defined as numeric and the host variable or parameter contains a numeric value too large for the definition of the column.
•The host variable is defined as decimal, but contains invalid decimal data.
•The target value is a string constant and the host variable or parameter contains a string that is too long for the target value.
•The target value is a numeric constant and the host variable or parameter contains a numeric value that is too large for the target value. |
|
|
Back to top |
|
|
Ashishpanpaliya
New User
Joined: 13 Oct 2017 Posts: 34 Location: India
|
|
|
|
Same SP is running successfully if I deploy as a native( by just removing keyword 'Fenced') |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
You need to contact DBA at your site.
Quote: |
Same SP is running successfully if I deploy as a native( by just removing keyword 'Fenced') |
It don't matter , take a suggestion from DBA and move further they knows what to provide when you create any SP. Try "Unfenced" last option. |
|
Back to top |
|
|
Ashishpanpaliya
New User
Joined: 13 Oct 2017 Posts: 34 Location: India
|
|
|
|
By using 'PARAMETER CCSID ASCII' while defining SP in DB solved the issue.
Thanks Everyone. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Ashishpanpaliya wrote: |
By using 'PARAMETER CCSID ASCII' while defining SP in DB solved the issue.
Thanks Everyone. |
Thanks for posting! |
|
Back to top |
|
|
|