Howdy DB2 users...

Here is the error I get running as the instance owner:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0204N "ECORE.V_TABLE" is an undefined name. LINE NUMBER=27.
SQLSTATE=42704

Here is the procedure:
CREATE PROCEDURE ADMINISTRATOR.GRANT_PRIVILEGES(IN OS_USER VARCHAR(20))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE V_TABLE VARCHAR(60);
DECLARE AT_END INT DEFAULT 0;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE TABS_CURSOR CURSOR FOR
SELECT
CAST(TABNAME AS VARCHAR(30))
FROM
SYSCAT.TABLES
WHERE
TABSCHEMA = 'ECORE'
;
DECLARE CONTINUE HANDLER FOR NOT_FOUND
SET AT_END = 1;
OPEN TABS_CURSOR;
GRANT_LOOP:
LOOP
FETCH TABS_CURSOR INTO V_TABLE;
IF AT_END = 1 THEN
LEAVE GRANT_LOOP;
ELSE
ITERATE GRANT_LOOP;
END IF;
GRANT SELECT ON ECORE.V_TABLE TO OS_USER;
END LOOP;
CLOSE TABS_CURSOR;
END @

Server Info:
SunOS 5.9 sun4u sparc SUNW,Ultra-Enterprise

DB2 Version:
Product Name = "DB2 Enterprise Server Edition"
Version Information = "8.2"
Product Name = "DB2 High Availability Disaster Recovery Option"
Version Information = "8.2"
Product Name = "DB2 Advanced Security Option"
Version Information = "8.2"

DB2 v8.1.0.64
FixPak 7
Type ESE

Thanks in advance for taking a look,

Bill

As the error says: You don't have a table name ECORE.V_TABLE in your database.

The misunderstanding here is that you assume that the value of the variable V_TABLE is used in the GRANT statement. However, you have a dynamic component here and need dynamic SQL for that. Thus, you should build a string with the GRANT statement and execute the string using the EXECUTE IMMEDIATE statement. (You can also drastically simplify the loop.)

Knut,

That is very much simpler...thanks...

Bill