Sorry, you do not have permission to ask a question, You must login to ask a question.

Sorry, you do not have permission to ask a question.

brainchime.com

brainchime.com

brainchime.com Navigation

  • Home
  • About Us
  • Contact Us

Mobile menu

Close
  • Home
  • Categories
  • Questions
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions
  • Polls
  • Tags

brainchime.com Latest Questions

Admin
Admin
Asked: October 6, 20242024-10-06T19:36:25+05:30 2024-10-06T19:36:25+05:30In: SQL

SQL0469 on calling a SQL procedure containing an INOUT parameter

I have created a SQL stored procedure that takes 2 input parameters and 1 output parameter means a return value. The script of the SQL stored procedure is as follows:

CREATE OR REPLACE PROCEDURE STORED33A(                  
        IN P_NAME CHAR(20),                             
        IN P_GENDER CHAR(1),                            
        OUT SUCCESS CHAR(1)                             
         )                                              
SPECIFIC STORED33A                                      
BEGIN                                                   
DECLARE SQLSTATE CHAR(5) DEFAULT ' ';                   
DECLARE SQLCODE INTEGER DEFAULT 0;                      
INSERT INTO PF8_D(NAME,GENDER) VALUES(P_NAME,P_GENDER); 
IF SQLCODE = 0 THEN                                     
  SET SUCCESS = '1';                                    
ELSE                                                    
 SET SUCCESS = '0';                                     
END IF;                                                 
END            

I have created it using RUNSQLSTM statement that creates a *PGM object of attribute CLE. However, when I call that SQL stored procedure from STRSQL on IBM i to test whether it is working fine or not I get the error SQL0469. This is the call statement to execute the SQL stored procedure where I passed NULL for the OUT parameter.
call testlib.STORED33A('name1','M',NULL)

Below is the error message

Message ID . . . . . . : SQL0469 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Message . . . . : IN, OUT, or INOUT not valid for parameter 3 in procedure STORED33A in TESTLIB. Cause . . . . . : The IN, INOUT, or OUT attribute specified for parameter 3 when the procedure was defined is not valid. The parameter name is SUCCESS. One of the following errors occurred: β€” A default cannot be specified for an OUT parameter. β€” The attribute is not consistent with the parameter on the CALL statement. If the parameter was declared INOUT or OUT, the parameter on the CALL statement must be specified as a host variable or a global variable. β€” The attribute was specified as INOUT or OUT and REXX was specified as the language. The attribute must be IN if REXX is specified. Recovery . . . : Either change the attribute of the parameter on the DECLARE PROCEDURE, CREATE PROCEDURE, or ALTER PROCEDURE statement or change the parameter. Try the request again. I then tried passing a blank value for the OUT parameter but got the same SQL0469 message id.

call testlib.STORED33A('name1', 'M',' ')
what is wrong here in the call statement of the SQL procedure that contains the INOUT parameter?

as400db2stored-procedure
  • 0
  • 0
  • 11
  • 2
  • Share
    • Share on Facebook
    • Share on Twitter
    • Share on LinkedIn
    • Share on WhatsApp

Related Questions

  • what is level check error and how to resolve level check error?
  • What are AS400 systems. Please provide a introduction of AS400 systems?
  • Is there any online server available for practicing on AS400 system?
  • What are the menus available in AS400?
  • What is subsystem in AS400?
  • How to copy a save file from IFS to a library?
  • How to transfer savf from as400 to pc?
  • How to copy ifs file to another directory?
  • How to copy savf from ifs to pc?
  • How to copy ifs file to physical file?
  • How to copy save file to ifs?
  • How to copy spool file to ifs?
  • How to copy physical file to ifs?
  • How to copy file from pc to IFS?
  • How to download spool file from AS400?
  • How do I delete a library in AS400?
  • How do I copy data from AS400 to excel?
  • What is library in AS400?
  • How to find all the source physical file available in AS400?
  • How to find all libraries in AS400?
  • How to change the library list in AS400?
  • What is access path in AS400?
  • What is the difference between source physical file and physical file in as400?
  • how to find the source file of an object in as400?
  • how to change record length of source physical file in as400?
  • What is cpf4174 error in as400?
  • What is the use of varying keyword in rpgle?
  • What is DDS in AS400?
  • What is the difference between PF and LF in as400?
  • Why do we use CHGPF command in AS400?
  • how to create physical file in as400?
  • What are the data types supported by physical files in AS400?
  • how to add data in physical file in as400?
  • how to view journal entries in as400?
  • what is the use of ovrdbf in as400?
  • What is an array in AS400?
  • what is a data queue in as400 and why do we use data queue?
  • How to run stored procedure in AS400?
  • How to resolve session and device error in AS400?
  • how to check as400 system values?
  • How to check triggers on a file in as400?
  • How to find damaged objects in AS400?
  • what is module in as400?
  • How to create binding directory in as400?
  • how to create ifs folder in as400?
  • What is ASP in AS400?
  • What is JOBQ and how to create a JOBQ in AS400?
  • What is PSDS in AS400?
  • What is SEU in AS400 and why do we use it?
  • What is the multi-format logical file in AS400?
Leave an answer

Leave an answer
Cancel reply

Browse
Browse

Choose from here the video type.

Put Video ID here: https://www.youtube.com/watch?v=sdUUx5FdySs Ex: "sdUUx5FdySs".

1 Answer

  • Voted
  • Oldest
  • Recent
  • Random
  1. Admin
    Admin
    2024-10-06T19:46:41+05:30Added an answer on October 6, 2024 at 7:46 pm

    When we call a SQL stored procedure using a constant value instead of a parameter marker (?) sqlcode -469 is returned.

    You can call a SQL stored procedure by passing a parameter marker in an INOUT parameter like this.

    call testlib.STORED33A('name1','M','?')

    However, you cannot even call using a parameters marker in the STRSQL session on an IBM i machine as the Use of parameter marker, NULL, or UNKNOWN is not valid.

    However, You can call it by passing any value to the OUT or INOUT parameter from the Run SQL script like
    call testlib.STORED33A('name1','M',NULL);
    call testlib.STORED33A('name1','M',' ');
    call testlib.STORED33A('name1','M',5);
    call testlib.STORED33A('name1','M',?);

    You will get the return output.

    [ 26/08/2024, 01:11:27 am ]  Run Selected...
    call testlib.STORED33A('name1','M',NULL)
    Return Code = 0
    Output Parameter #3 (SUCCESS) = 1
    Statement ran successfully   (339 ms)
    
    [ 26/08/2024, 01:14:45 am ]  Run Selected...
    call testlib.STORED33A('name1','M',' ')
    Return Code = 0
    Output Parameter #3 (SUCCESS) = 1
    Statement ran successfully   (294 ms)
    
    [ 26/08/2024, 01:16:55 am ]  Run Selected...
    call testlib.STORED33A('name1','M',5)
    Return Code = 0
    Output Parameter #3 (SUCCESS) = 1
    Statement ran successfully   (295 ms)
    
    [ 26/08/2024, 01:30:05 am ]  Run Selected...
    call testlib.STORED33A('name1','M',?)
    Return Code = 0
    Output Parameter #3 (SUCCESS) = 1
    Statement ran successfully   (293 ms)

    No such issue will occur when you call the SQL stored procedure from the Run SQL Script. This issue will only occur in the STRSQL session. Therefore, to call it from the STRSQL session you first have to create a SQL global variable using CREATE VARIABLE statement like below.

    create variable testlib.sqlout char(1) default ' '

    The variableSQLOUT was created in testlib. It’s an *SRVPGM object type of attribute CLE.

    Now again try to call the SQL stored procedure from the STRSQL session on IBM i but this time passed the SQL global variable for the OUT or INOUT parameter in the procedure as below.

    call testlib.STORED33A('name1','M',testlib.sqlout)

    CALL statement complete. SQL7985 which means Call to procedure STORED33A completed successfully.

    You can check the return value in the SQL global variable as follows:

    select testlib.sqlout from sysibm.sysdummy1

    the output return value in SQL global variable from SQL stored procedure call.

    SQLOUT 
    1 
      • 0
    • Reply
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
      • Share on LinkedIn
      • Share on WhatsApp

Sidebar

Statistics

  • Questions 143
  • Answers 177
  • Comments 0
  • Popular
  • Answers
  • Admin

    Why do we use OVERLAY keyword in data structure subfields ...

    • 12 Answers
  • Admin

    How to call sql stored procedure with output parameter from ...

    • 6 Answers
  • Admin

    How to use declare global temporary table statement in RPGLE?

    • 5 Answers
  • Admin
    Admin added an answer CPF4131 is a record format level check error. This indicates… October 18, 2024 at 1:58 am
  • Admin
    Admin added an answer To open the command prompt with administrator rights you can… October 17, 2024 at 12:27 am
  • Admin
    Admin added an answer In AS400, "AS" stands for Application system. This article discusses… October 13, 2024 at 12:49 pm

Related Questions

  • Admin

    what is level check error and how to resolve level ...

    • 1 Answer
  • Admin

    What are AS400 systems. Please provide a introduction of AS400 ...

    • 1 Answer
  • Admin

    Is there any online server available for practicing on AS400 ...

    • 1 Answer
  • Admin

    What are the menus available in AS400?

    • 1 Answer
  • Admin

    What is subsystem in AS400?

    • 1 Answer

Trending Tags

.htaccess (1) as400 (123) bing-webmaster (2) control-language (12) db2 (33) ftp (8) google-adsense (1) google-search-console (3) https-redirect (1) iasp (4) ifs (22) jar (4) operations (3) php-my-admin (1) qshell (3) robots.txt (4) rpg (26) stored-procedure (3) stroed procedure (1) triggers (1) yoast (4)

Explore

  • Home
  • Categories
  • Questions
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions
  • Polls
  • Tags

Footer

BrainChime

BrainChime is a blog that posts question-and-answer-based format articles on diverse topics and engages in discussions by allowing people to provide answers/comments without the need to register and log in.

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Terms of Use
  • Privacy Policy
  • Cookie Policy

Help

  • FAQs
  • Categories
  • Tags

© 2024 BrainChime. All Rights Reserved
by BrainChime.

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.