Results 1 to 11 of 11
  1. #1
    Join Date
    May 2017
    Posts
    6

    Unanswered: Error calling stored procedure when the parameter is user defined array type

    For example I create two user define data types as shown below..

    CREATE TYPE "NMS"."TAX_ARRAY_TYPE" AS VARCHAR(10) ARRAY [20];
    CREATE TYPE "NMS"."NPI_ARRAY_TYPE" AS VARCHAR(50) ARRAY [20];

    I have created a stored procedure which is shown below..

    CREATE OR REPLACE PROCEDURE NMS.ISTHISRELATEDTEST
    ( IN taxArr NMS.TAX_ARRAY_TYPE
    , IN npiArr NMS.NPI_ARRAY_TYPE
    )
    DYNAMIC RESULT SETS 9999

    P1: BEGIN
    .....
    .....
    END P1

    We have given access to execute this stored proc to some other users to execute.

    When I execute this stored proc in IBM Data studio I dont get error using my ID ( I am the one who created these user types and the stored proc with Schema NMS )
    Now when I try to login as a different user and execute I get this error below.. ( This user has permissions to execute the stored proc, since the access is given to users in a given group )

    Run: NMS.ISTHISRELATEDTEST(TAX_ARRAY_TYPE, NPI_ARRAY_TYPE)

    {call NMS.ISTHISRELATEDTEST(?,?)}
    A value is not compatible with the data type of its assignment target. Target name is "TAXARR".. SQLCODE=-408, SQLSTATE=42821, DRIVER=3.68.61
    Run of routine failed.
    - Roll back completed successfully.

    Any idea why I am getting this error. Any help is appreciated.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Works fine for me on DB2 V11.1.1.1 on Linux. An SQL PL sproc can call a nested SQL PL sproc passing two array-type variables as input parameters . Once I grant execute on both sprocs to public then the call completes without errors.

  3. #3
    Join Date
    May 2017
    Posts
    6

    Error still exists

    Quote Originally Posted by db2mor View Post
    Works fine for me on DB2 V11.1.1.1 on Linux. An SQL PL sproc can call a nested SQL PL sproc passing two array-type variables as input parameters . Once I grant execute on both sprocs to public then the call completes without errors.
    I gave access to the stored proc to public. But I still get error. Here is the issue I have.

    I have created a stored proc with 2 user defined array types. When I run the stored proc in IBM Data Studio using my ID ( I created the stored proc in different schema NMS as shown above ) it works fine. When I log off and log back in with a different userId and try to execute I get an error.

    A value is not compatible with the data type of its assignment target. Target name is "TAXARR".. SQLCODE=-408, SQLSTATE=42821

    Another way of calling the stored proc another way

    Also I see that its calling NMS.ISTHISRELATEDTEST(TAX_ARRAY_TYPE, NPI_ARRAY_TYPE)

    The parms should have been NMS.TAX_ARRAY_TYPE and NMS.NPI_ARRAY_TYPE.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    You've got given basic facts about your environment: versions/operating-systems/fixpacks, have you done any problem determination, have you tried to get it working at the CLP on the DB2-server only (i.e. not using data-studio)?

  5. #5
    Join Date
    May 2017
    Posts
    6

    DB2 Version

    Quote Originally Posted by db2mor View Post
    You've got given basic facts about your environment: versions/operating-systems/fixpacks, have you done any problem determination, have you tried to get it working at the CLP on the DB2-server only (i.e. not using data-studio)?
    We are using 10.1 luw version. I created and tested the stored proc using IBM Data Studio.

    I tested on unix server and I also got the same error.

    I am passing 2 arrays but for now only single elements.

    I get this error when I do "call NMS.ISTHISRELATEDTEST('[123344]','[2323443]');" in Data studio using a script and when I run that script.

    A value is not compatible with the data type of its assignment target. Target name is "TAXARR".. SQLCODE=-408, SQLSTATE=42821, DRIVER=3.68.61

  6. #6
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    In your first post for this thread, you wrote "{call NMS.ISTHISRELATEDTEST(?,?)}" suggesting you were using host variables.
    In your post above you wrote "call NMS.ISTHISRELATEDTEST('[123344]','[2323443]');" - , and the error message is correct because you are passing two string literals to an sproc that requires two arrays.
    Try "call nms.ISTHISRELATEDTEST(array['123344'], array['2323443'])"

  7. #7
    Join Date
    May 2017
    Posts
    6
    Actually I am calling from the server and I am getting this error..

    db2 "call NMS.ISTHISRELATEDTEST(ARRAY['123344','34343434'],ARRAY['2323443','6767676'])";

    SQL0408N A value is not compatible with the data type of its assignment
    target. Target name is "TAXIDARR". SQLSTATE=42821

    Just as a note. If I login with my ID into the server and run this one it works fine. If I login using a different ID I get this error.

    Its has to do with the user defined types I created. It works fine with my ID.

  8. #8
    Join Date
    May 2017
    Posts
    6

    Error calling stored prcedure

    Actually I am calling from the server and I am getting this error..

    db2 "call NMS.ISTHISRELATEDTEST(ARRAY['123344','34343434'],ARRAY['2323443','6767676'])";

    SQL0408N A value is not compatible with the data type of its assignment
    target. Target name is "TAXIDARR". SQLSTATE=42821

    Just as a note. If I login with my ID into the server and run this one it works fine. If I login using a different ID I get this error.

    Its has to do with the user defined types I created. It works fine with my ID. With any other ID I get this error. We have granted the execute permission on the stored procs for the group and all the ID's belong to the same group.

  9. #9
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    As DB2 10.1 goes out of support on 30/September/2017, and as this works fine on DB2 V11.1.1.1, you can either open a PMR with IBM, or test with a higher DB2 version V10.5 or V11.1. You can also trace db2 with db2trc and investigate what is happening under the covers when testing locally at the CLP. This may be related to http://www-01.ibm.com/support/docvie...id=swg1IT08814
    Last edited by db2mor; 06-05-17 at 12:11.

  10. #10
    Join Date
    May 2017
    Posts
    6
    Thanks Again. But the link also shows local fix. How do I execute my stored proc with array type from command line ?

    Call the parameter marker with cast to the array data type:
    BEGIN ? := TEST_SCHEMA.TEST_PKG.F_USE_ARRAY(cast(? as
    test_schema.test_pkg.g_array_typ)

  11. #11
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    The local fix of the APAR is not relevant for array-literals for the CLP. Only by opening a PMR can IBM confirm if the APAR is relevant. Ensure you are at the highest fixpack of the V10.1 tree. Follow the advice re db2trc and testing on higher versions of DB2.

Posting Permissions

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