Results 1 to 5 of 5

Thread: ora-00911 error

  1. #1
    Join Date
    Mar 2003
    Posts
    45

    Unanswered: ora-00911 error

    Hi,

    I am getting these errors when I run this pl/sql

    BEGIN
    FOR r IN (SELECT table_name FROM user_tables)
    LOOP
    EXECUTE IMMEDIATE 'GRANT ALL ON '||r.table_name||' TO PUBLIC';
    END LOOP;
    END;
    /

    error at line 1:
    ora-00911: invalid character
    ora-06512: at line 4

    I thought it might have been the quotes, or maybe thes spaces between the quote and the pipe sign. all that is correct. what could be wrong with line 4? I cannot see it?

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Alex,

    I haven't tried your code but you're missing a semi colon from this line....

    FOR r IN (SELECT table_name FROM user_tables)

    I would imagine you've already tried that, but if not I'll try running the code...

    HTH
    Bill

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Dear billm,

    I guess there is no problem in the code you provided

    thanks,

    Hings.

    =====

    Still Learning...

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Alex,

    Hings is right, your code (not my code:-) ) works fine here on 8.1.7

    HTH
    Bill

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ora-00911 error

    Try changing EXECUTE IMMEDIATE to DBMS_OUTPUT.PUT_LINE and see what you get. Maybe you have a table with a non-standard name that was defined using double quotes, e.g.

    SQL> CREATE TABLE "`" (ID NUMBER);

    Table created.

    This would then cause ORA-00911 in your grant statement which is:

    SQL> GRANT ALL ON ` TO PUBLIC;
    GRANT ALL ON ` TO PUBLIC
    *
    ERROR at line 1:
    ORA-00911: invalid character

Posting Permissions

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