Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unanswered: SQL0204N From Stored Procedure

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Code:
    CREATE PROCEDURE ADMINISTRATOR.GRANT_PRIVILEGES (
          IN OS_USER VARCHAR(20) )
       LANGUAGE SQL
       BEGIN
          DECLARE grantStmt VARCHAR(500);
    
          FOR tables AS c CURSOR FOR
                SELECT tabname
                FROM   syscat.tables
                WHERE  tabschema = 'ECORE' DO
             SET grantStmt = 'GRANT SELECT ON ECORE."' || tables.tabname ||
                '" TO "' || OS_USER || '"';
             EXECUTE IMMEDIATE grantStmt;
          END FOR;
    END @
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2007
    Posts
    16

    SQL0204N From Stored Procedure

    Knut,

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

    Bill

Posting Permissions

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