Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    76

    Unanswered: Cursor into DB2 UDF

    Hi All,

    I'm trying to user cursor into DB2 UDF but it gives me compile time error. Code and error is mentioned below:

    Please note that my current version of DB2 UDB is 8.1.3

    Code:
    =====START=====
    CREATE FUNCTION DB2ADMIN.UDF_CURSOR_TEST()
    RETURNS VARCAHR(254)
    P1: BEGIN

    DECLARE CUR_TEST CURSOR FOR
    SELECT COL1, COL2 FROM DB2ADMIN.TAB_TEST;

    OPEN CUR_TEST;

    RETURN 'TESTED OK!!!!';

    END P1
    @
    =====END=====
    Above code is saved into "UDF_CURSOR_TEST.DB2" file.

    From DB2 Command Line Processor I execute following commeand

    db2 -td@ -f UDF_CURSOR_TEST.DB2

    and it returns me following error........

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "VARCAHR" was found following "URSOR_TEST()
    RETURNS". Expected tokens may include: "VARCHAR". LINE NUMBER=2.
    SQLSTATE=42601D

    If I remove the cursor then UDF gets compile and it runs properly.


    Can any one PLEASE PLEASE PLEASE help me in that>>> I've BIG doubt whether we can user cursor into DB2 UDB UDB or not.

    With BIG hope,
    Jai

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    First of all, you may want to check the spelling of "VARCAHR". Regarding what's allowed within the function body, here's what the manual has to say:
    The following list of SQL-control-statements can be used within the dynamic compound statement:

    * FOR Statement
    * GET DIAGNOSTICS Statement
    * IF Statement
    * ITERATE Statement
    * LEAVE Statement
    * SIGNAL Statement
    * WHILE Statement

    The SQL statements that can be issued are:

    * fullselect (A common-table-expression can precede the fullselect.)
    * Searched UPDATE
    * Searched DELETE
    * INSERT
    * SET variable statement

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    I think the DECALRE CURSOR is not allowed in UDF - However, DECLARE {variable} is allowed.

    Again I maybe wrong!

    As n_i pointed out only a subset of stored procedure commands are allowed in UDF.

    You are the creator of your own destiny!

Posting Permissions

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