Hi All,

has anyone got a working example of a SQL-PL dynamic cursor.

TIA

Bill

An unexpected token "FOR" was found following "c5')". Expected tokens
may include: "
OPEN c5".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.12.79
________________________________________
Doesn't Compile - UDB Linux v9.7
________________________________________
DECLARE v5_dyn_sql VARCHAR(254) DEFAULT '';
DECLARE c5 SYS_REFCURSOR;

SET v5_dyn_sql = 'select table_schem, table_name, column_name '
CONCAT ' from sysibm.sqlcolumns '
CONCAT ' where table_schem like 1 '
CONCAT ' and table_name like 2 '
CONCAT ' order by 1, 2, 3 '
CONCAT ' fetch first 500 rows only ';

OPEN c5 FOR v5_dyn_sql USING ip_schema_name, ip_table_name;
(the ip’s are INPUT PARAMETERS)

CLOSE c5;

On 2012-01-19 21:41, wfs wrote:

Have you successfully compiled an example from the docs? (such as:

CREATE OR REPLACE PROCEDURE dept_query
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' ||
' AND sal >= 1500';
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_refcur;
END

)

Reason I ask is that I could not make that compile out of the box, so I
assume that one need to do some configuration (enable some compability
mode?). I don't have time to look into it now, but I would focus on
getting one of the examples to work.


/Lennart

On Jan 20, 8:50*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
You probably need to take a look at the DB2_COMPATIBILITY_VECTOR
register variable.

--
Frederik Engelen

On Jan 20, 2:50*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
No, I couldn't get the example in the manual to compile either, using
the 'OPEN xx FOR' syntax,

As someone else suggested, I'll check out the
DB2_COMPATABILITY_VECTOR, but at first glance, there appears to be no
options for SQL-PL, the nearest is for ORACLE PL/SQL compatability.

Thanks

Bill

On 20 jan, 13:09, wfs <wfs1... (AT) gmail (DOT) com> wrote:
SYS_REFCURSOR looks to be PL/SQL, not SQL-PL, so I'd take a look at
that variable.

--
Frederik Engelen

On Jan 20, 1:15*pm, Frederik Engelen <engelenfrede... (AT) gmail (DOT) com>
wrote:
I'll check it out. All I want to do is create a dynamic SQL-PL cursor
using a parameterized query. Strange that only example in the IBM
manual(s) is for PL/SQL.

On 2012-01-20 19:27, bill schofield wrote:
[...]
Bill, what version/fixpak are you running?

#> db2level
#> db2licm -l

/Lennart

On 20 jan, 21:34, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Would this suit you? I would be hesitant to call it dynamic (that
would use EXECUTE IMMEDIATE) but given your example I think this might
be what you're looking for:

CREATE PROCEDURE TEST (tabpattern varchar(255))
LANGUAGE SQL
BEGIN
FOR t as select tabname as matchedtabname from syscat.tables where
tabname like tabpattern
DO
INSERT INTO TEST (TESTTABNAME)
VALUES (matchedtabname);
END FOR;
END@

CALL TEST('TAB%')@

--
Frederik Engelen