DB2 FOR LUW / DB2FORLUW / 980 - DB2

If you compile a PL/SQL routine containing a JDBC-style '?' 
parameter marker, DB2 treats the appearance of the question mark 
as a syntax error, SQLCODE -104. 
 
For example, the following procedure dynamically prepares a 
PL/SQL anonymous block with an output parameter marker: 
 
create procedure test(out result varchar(10)) 
begin 
  declare stmt_text varchar(255); 
  declare S1 statement; 
 
  set stmt_text = 
    'declare test varchar(10); begin test := ''ABC''; ? := test; 
end;'; 
 
  prepare S1 from stmt_text; 
  execute S1 into result; 
end % 
 
call test(?) % 
SQL0104N  An unexpected token "?" was found following "declare 
test 
varchar(10); begin test := 'ABC'; ? := test; end;".  Expected 
tokens may 
include:  "SELE".  LINE NUMBER=1.  SQLSTATE=42601

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users compiling PL/SQL routine containing a JDBC-style '?'   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* JDBC-style '?' parameter markers not supported in PL/SQL     * 
* routines                                                     * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to V9.8 FP3                                          * 
****************************************************************

As a workaround, submit the dynamic statement in DB2 SQL PL, if 
possible, instead of PL/SQL.  This will allow the use of the '?' 
parameter marker. 
 
Using the error description example, you would change the value 
of STMT_TEXT to use an SQL PL compiled compound statement, like 
this: 
 
create procedure test(out result varchar(10)) 
begin 
  declare stmt_text varchar(255); 
  declare S1 statement; 
 
  set stmt_text = 
    'begin declare test varchar(10); set test = ''ABC''; set ? = 
test; end'; 
 
  prepare S1 from stmt_text; 
  execute S1 into result; 
end % 
 
call sysproc.set_routine_opts('') % 
 
call test(?) % 
 
Value of output parameters 
-------------------------- 
Parameter Name  : RESULT 
Parameter Value : ABC 
 
Return Status = 0

DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

24.02.2010
12.01.2011
12.01.2011

9.8.FP3