Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL Code 302 while executing stored procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 29
Location: India

PostPosted: Wed Aug 01, 2018 7:54 pm    Post subject: SQL Code 302 while executing stored procedure
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: 8748
Location: Welsh Wales

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

This might help

https://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

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Wed Aug 01, 2018 8:22 pm    Post subject:
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: 29
Location: India

PostPosted: Wed Aug 01, 2018 8:37 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Thu Aug 02, 2018 12:12 am    Post subject:
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: 29
Location: India

PostPosted: Thu Aug 02, 2018 2:57 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Thu Aug 02, 2018 7:22 pm    Post subject:
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: 29
Location: India

PostPosted: Thu Aug 02, 2018 9:48 pm    Post subject:
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: 2448
Location: @my desk

PostPosted: Thu Aug 02, 2018 9:53 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts calling java code(on mainframe from c... elixir1986 COBOL Programming 1 Fri Nov 30, 2018 1:57 am
No new posts Code numeric format to display both '... Time2Live COBOL Programming 7 Wed Nov 21, 2018 10:20 pm
No new posts Will this code extract 1 byte from fi... pkmurali CLIST & REXX 8 Sat Nov 10, 2018 11:13 pm
No new posts SQL CODE 206 Ashishpanpaliya DB2 12 Wed Oct 10, 2018 9:20 pm
No new posts Facing issue while executing multi ro... aagarwal88 DB2 6 Tue Oct 02, 2018 8:11 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us