ora-00911 - invalid character - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: ora-00911 - invalid character

  1. #11
    Join Date
    May 2002
    Posts
    2,645
    Remove the semicolon at the end. Statements don't need that when using exec immediate. If your insert statement were something like the other example I posted, note there is not a semicolon at the end.

    Practice using this, turn the comment into an insert statement on a test table (i.e., get a normal insert to work first).

    Code:
    DECLARE
      error_code       VARCHAR2(10);
      error_message    VARCHAR2(2000);
      insert_statement VARCHAR2(4000);
      v_owner          metadb_tables.schema_owner%type;
      v_table          metadb_tables.table_name%type;
      v_comments       metadb_tables.table_comments%type;
    
      CURSOR c IS
      select t.schema_owner,
             t.table_name,
             t.table_comments
      from metadb_tables t
      where t.schema_owner = 'SOMETHING'
      and t.table_name like '%HDR%'
      order by 1,2,3;
    
    
    BEGIN
    
      FOR r in c LOOP
    
      insert_statement := 
        'COMMENT ON TABLE ' || r.schema_owner || '.' || r.table_name
                            || ' is ' || '''' ||r.table_comments|| '''' || ';';
                                             --remove this------------------^
    
      DBMS_OUTPUT.PUT_LINE (insert_statement);
    
      EXECUTE IMMEDIATE (insert_statement);
      COMMIT;
    
      END LOOP;
    
    EXCEPTION 
      WHEN OTHERS THEN
        error_code := SQLCODE;
        error_message := SQLERRM;
        DBMS_OUTPUT.PUT_LINE (error_code || error_message);
    
    END;
    /
    Last edited by stecal; 04-28-2010 at 11:18 AM.

  2. #12
    Join Date
    Apr 2010
    Location
    Indianapolis
    Posts
    10

    Thumbs up

    Thank you, It worked!

Posting Permissions

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



Click Here to Expand Forum to Full Width