Re: dynamic SQL with dbms_sql, example?

sorry,
actually these two should be reversed

> DBMS_SQL.COLUMN_VALUE(vCursorID, 1, [var]);
> IF DBMS_SQL.FETCH_ROWS(vCursorID) = 0 THEN
> EXIT;
> END IF;
...

 IF DBMS_SQL.FETCH_ROWS(vCursorID) = 0 THEN

    EXIT;
 END IF;
 DBMS_SQL.COLUMN_VALUE(vCursorID, 1, [var]);

second, I had a closer look at your problem and found that it can't be that easily solved. As the number and type of columns the select statement retrieves are unknown at compilation-time it is difficult to involve a correct DBMS_SQL.DEFINE_COLUMN. A solution could be to use DBMS_SQL.DESCRIBE_COLUMNS. Though this would still mean to handle an unknown number and type of variables... I used Dynamic SQL often to include a 3rd GL generated where-clause in PL/SQL in which case you know your columns...

Regards,

Thomas

PS: Matthias, bei weiteren Fragen kannst Du mich natürlich gerne per Email kontakten Received on Sat May 15 1999 - 09:06:09 CDT