Sams Teach Yourself SQL in 24 Hours, Second Edition

Using SQL to Generate SQL

Using SQL to generate SQL is very valuable time-budgeting when writing SQL statements. Assume you have 100 users in the database already. A new role, ENABLE (a user-defined object that is granted privileges), has been created and must be granted to those 100 users. Instead of manually creating 100 GRANT statements, the following SQL statement generates each of those statements for you:

SELECT 'GRANT ENABLE TO '|| USERNAME||';'
FROM SYS.DBA_USERS;

This example uses Oracle's system catalog view (which contains information for users).

Notice the use of single quotation marks around the GRANT ENABLE TO. The use of single quotation marks allows whatever is between the marks to be literal. Remember that literal values can ...