Handy use of Oracle Metadata

Need to run an sql statement on multiple tables in your database?  Nauseous about having to copy/modify/paste/run again and again?  Using an Oracle database?!  Well perhaps the following will help to significantly mitigate your woes.

The idea is to use Oracle Metadata to select the tables of interest inside a string containing the SQL you wish to run.  The output of this query will be the list of SQL statements that you otherwise would have had to painstakingly construct by means of copy/pasting.  You can then copy this output, paste it back into sqldeveloper, hit run and kick back.

Consider the following example.  I wish to grant Select permissions on every table under my user name to another user:

1. Run the following:
• SELECT 'GRANT SELECT ON ' || TABLE_NAME || ' TO Other_User;' FROM USER_TABLES;
• "Other_User" is the name of the database you wish to grant select permissions to.
2. Copy the output of the above run into SQLDeveloper and hit run again.

Simple and useful.  Cheers to Renae Carr for the example.