Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Answered: Retrieving a Cursor as OUT Parameter from a SP in .net (db2 v9.7 FP 5 on Linux)

    Hello Experts,
    I am stuck again and need to pick your brains. I have a simple stored procedure that has a CURSOR as an OUT parameter. The Code is as given below:-
    CREATE OR REPLACE PROCEDURE "MYTEST1"(OUT p_op_rc CURSOR)
    LANGUAGE SQL
    BEGIN
    SET p_op_rc = CURSOR FOR select * from table;
    OPEN p_op_rc;
    END @
    This procedure compiled without any errors (using DataStudio client), and gives the required output also, however the trouble starts when we use any other client (.net), the procedure call fails with the below error message:-

    SQL20441N A "CURSOR" data type is not supported in the context where it is being used.

    I get the same error when I try to use Putty to run the procedure from the Linux command line.
    Any thoughts or insights would be highly appreciated.

    Regrads
    Ashwin

  2. Best Answer
    Posted by mark.b


  3. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Cursor variables are not supported for use in applications. Cursor variables can only be declared and referenced in SQL PL contexts.
    In your case, the command line (clp) is the application.
    But if you call your sproc from another sproc (i.e. from an SQLPL context), you should get what you need if you call the second (parent) sproc from the clp.
    Last edited by db2mor; 03-27-15 at 06:30.

  4. #3
    Join Date
    Sep 2009
    Posts
    12
    Hi db2mor,
    Thanks for the response, the challenge I am facing is that I am trying to migrate Oracle Stored Procedures to DB2 using the Oracle compatibility vector, now since Oracle enables to use CURSOR as OUT variable, any idea what is the best way to tackle this?

    Regards
    Ashwin

  5. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Without seeing the real code, I cannot advise further.
    Note that DB2 supports CURSOR as an OUT parameter - with the restriction that the *caller* has to be an SQLPL context.
    There's always more than one way to solve a problem however...

  6. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Regards,
    Mark.

  7. #6
    Join Date
    Sep 2009
    Posts
    12
    Hi,
    Thank You to all who helped me out on this topic. I was finally able to figure out the reason behind the error I was facing, it was due to the DB2 Express C that was installed on the .net server. Since Express C could not parse the cursor, the error was passed on to the calling program. When I removed the Express C installation and used a direct call to the db2 server, the code is working as expected. With db2 9.7 we can use CURSOR as an OUT parameter with both Java and .net, I just wanted to update the thread so that someone else facing the same problem can get a better answer.

    Cheers
    Ashwin

Posting Permissions

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