Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2014
    Posts
    6

    Unanswered: Call Procedure in cursor declaration

    Hi,

    How do I use a Call procedure instead of a select statement in DB2 cursor declaration??

    This is what I did but it didn't work.. Any sugestions..

    declare C1 cursor with return for
    call ACHINST1.ReportD010 (MISDATE,start_date,end_date,sender_name,receiver_ name);

    open c1;

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    more details would be welcome
    why not changing the called sp (with return) and fetch after the call all rows not fetched by sp
    look in sample directory : there are some..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jun 2014
    Posts
    6
    I have a procedure Proc1 which executes some DML command and it has a cursorwhich selects some data. Now I cant use the Proc1 as out application does not support Procedures which have DML.

    So am trying to call the Proc1 from another procedure Proc2. Which should just call Proc1 and through a cursor and leave that cursor open so that it displays the output.

    Would using a out parameter(ref cursor) in Proc1 and returning the value help??

    But then I get error in the declare line of cursor in Proc2 when I try to use a Procedure there instead of select query.

    declare C1 cursor with return for
    call ACHINST1.ReportD010 (MISDATE,start_date,end_date,sender_name,receiver_ name);

    This is the line that gives me the error

    DB2 Database Error: ERROR [42601] [IBM][DB2/LINUXX8664] SQL0104N An unexpected token "call" was found following "r with return for ". Expected tokens may include: "<values>". LINE NUMBER=12. SQLSTATE=42601

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Cursor can only be declared for a SELECT statement, therefore you cannot use a CALL statement in a cursor declaration. You could rewrite your SP as a table function, which could be then used in a SELECT.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Last edited by dr_te_z; 06-24-14 at 03:11.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Jun 2014
    Posts
    6
    Thanks Scott,

    I tried result set allocator ysterday itslf and it workd for me. Sorry that I dint post it back

Posting Permissions

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