IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL Code 302 while executing stored procedure


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Wed Aug 01, 2018 7:54 pm
Reply with quote

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
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Aug 01, 2018 7:57 pm
Reply with quote

This might help

www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/codes/src/tpc/db2z_n.html
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Wed Aug 01, 2018 8:22 pm
Reply with quote

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
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Wed Aug 01, 2018 8:37 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Aug 02, 2018 12:12 am
Reply with quote

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
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Thu Aug 02, 2018 2:57 pm
Reply with quote

Same SP is running successfully if I deploy as a native( by just removing keyword 'Fenced')
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Aug 02, 2018 7:22 pm
Reply with quote

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
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Thu Aug 02, 2018 9:48 pm
Reply with quote

By using 'PARAMETER CCSID ASCII' while defining SP in DB solved the issue.

Thanks Everyone.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Aug 02, 2018 9:53 pm
Reply with quote

Ashishpanpaliya wrote:
By using 'PARAMETER CCSID ASCII' while defining SP in DB solved the issue.

Thanks Everyone.
Thanks for posting!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts problem in select max when executing ... IMS DB/DC 6
No new posts How to convert RESP code 12 to text? CICS 9
No new posts Execute step regardless of previous s... JCL & VSAM 2
No new posts Error 0C1 Reason Code 1 with branch i... PL/I & Assembler 3
No new posts JCL for replacing code in Cobol JCL & VSAM 7
Search our Forums:

Back to Top