DB2 - L

 View Only

Db2 12 for z/OS Using an array parameter in a stored procedure CALL

  • 1.  Db2 12 for z/OS Using an array parameter in a stored procedure CALL

    Posted Oct 27, 2021 02:57 PM

    OK, I have a stored procedure that accepts an array of character strings as input. I can call it from IBM Data Studio quite easily by right clicking on the stored procedure in the explorer and choosing "run". I input the parameter as

     

    ['123','123','123']

     

    And it runs just fine. Now I'd like to call the stored procedure from a SQL script, something like this

     

    CALL MY.SP(['123','123','123'])

     

    Or

     

    CALL MY.SP(ARRAY['123','123','123'])

     

    I even tried a global variable.

     

    SET MY.VARIABLE = ARRAY['123','123','123']

    CALL MY.SP(MY.VARIABLE)

     

    The set works but the call fails.

     

    Has anyone attempted this? The documentation seems to suggest that you can only pass an array from an array structure in a host variable, but that would be disappointing. I'll be trying the call from Java in the coming weeks but thought I'd reach out to see if anyone has had this experience.

     

    Thanks,

    Dan Luksetich