Using SQL to generate SQL is a very valuable time-budgeting method of 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 GRANT ENABLE TO. The use of single quotation marks allows whatever is between the marks (including spaces) to be literal. Remember that literal values can be selected from tables, the same as columns from a table. USERNAME is the column in the system catalog table SYS.DBA_USERS. The double pipe signs () are used to concatenate the columns . The use of double pipes followed by ';' concatenates the semicolon to the end of the username, thus completing the statement. The results of the SQL statement look like the following: GRANT ENABLE TO RRPLEW; GRANT ENABLE TO RKSTEP; These results should be spooled to a file, which can be sent to the database. The database, in turn , executes each SQL statement in the file, saving you many keystrokes and much time. The GRANT ENABLE TO USERNAME; statement is repeated once for every user in the database. Next time you are writing SQL statements and have repeated the same statement several times, allow your imagination to take hold and let SQL do the work for you. |