Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2015
    Posts
    5

    Answered: 256 Char limit in Stored Procedure?

    I am trying to write a stored procedure. It call a cursor and the value of one of the columns in it is varchar(1000) and fetching the value into a variable of data type varchar(1000).
    But I get only 256 characters in the variable. Is Db2 restricting the value to only 256 characters? How can I overcome this restriction?
    I tried with CLOB, BLOB, but no luck.
    Any help will be appreciated. Thanks.
    DB2 Version : 9.7

  2. Best Answer
    Posted by ARWinner

    "In looking at the code, I notice these issues.

    The statement: "SET V_SQL = REPLACE(V_SELECT_STMT,'?',V_RETENTION_PERIOD);" will cause truncation. V_SQL is VARCHAR(1000)

    You also have at least one logic error. The outer loop will only execute once, because at least one of the inner loops will fire the NOT FOUND condition handler.

    I also would replace this:

    SET STMTTXT = REPLACE(V_DELETE_STMT,'?',''''||V_VARCHAR_ID||'''' );
    EXECUTE IMMEDIATE STMTTXT;

    With this:
    PREPARE DELETE_STMT FROM V_DELETE_STMT;
    EXECUTE DELETE_STMT USING V_VARCHAR_ID;


    Andy"


  3. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which OS?

    It should be working as desired, but I cannot be sure because I cannot see what you are doing. How about pasting the code, data and results?

    Andy

  4. #3
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    Which OS?

    It should be working as desired, but I cannot be sure because I cannot see what you are doing. How about pasting the code, data and results?

    Andy
    OS is AIX.
    Attached the code.
    The SELECT_STMT column in the table CPS_ADMIN.TBL_PURGE_TABLES_INFO is defined as VARCHAR(5000).
    When I fetch the value into the variable V_SELECT_STMT, it is getting truncated to 256 characters.
    V_SELECT_STMT is defined as VARCHAR(5000.
    Attached Files Attached Files

  5. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It looks fine to me. How do you know it is getting truncated?

    Andy

  6. #5
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    It looks fine to me. How do you know it is getting truncated?

    Andy
    When I debug, I see only 256 chars for the variable V_SELECT_STMT after fetch.
    And the stored procedure errors out.
    Is it something like a configuration property that is set for DB2?

  7. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How are you debugging it?

  8. #7
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    How are you debugging it?
    Debugging it through IBM Data Studio 3.1.1
    I ran the stored procedure in TOAD too, it gave the same error.

  9. #8
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Just for info, DS is now at 4.1.1. I suggest you get off the 3.1.1 level as it has a lot of issues
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  10. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    In looking at the code, I notice these issues.

    The statement: "SET V_SQL = REPLACE(V_SELECT_STMT,'?',V_RETENTION_PERIOD);" will cause truncation. V_SQL is VARCHAR(1000)

    You also have at least one logic error. The outer loop will only execute once, because at least one of the inner loops will fire the NOT FOUND condition handler.

    I also would replace this:

    SET STMTTXT = REPLACE(V_DELETE_STMT,'?',''''||V_VARCHAR_ID||'''' );
    EXECUTE IMMEDIATE STMTTXT;

    With this:
    PREPARE DELETE_STMT FROM V_DELETE_STMT;
    EXECUTE DELETE_STMT USING V_VARCHAR_ID;


    Andy

  11. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, you keep mentioning it erroring out in your last two responses but not in your first 2 what is the error you are getting?
    Dave

  12. #11
    Join Date
    Sep 2015
    Posts
    5
    It is working now. As you said the problem is with the Data Studio 3.1.1.
    Also fixed the inner cursor exit handlers.
    Thanks a lot.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •