Using SQL to Generate SQL

Posted by arlene

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 ‘11 USERNAMEll’;’

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 (I ) 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 ENABLEstatement is repeated once for every user in the database. TO USERNAME;

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.

Direct Versus Embedded SQL

Direct SQL is where a SQL statement is executed from some form of an interactive terminal. The SQL results are returned directly to the terminal that issued the statement. Direct SQL is also referred to as interactive invocation or direct invocation.

NEW TERM Embedded SQL is SQL code used within other programs, such as Pascal,

FORTRAN, COBOL, and C. SQL code is actually embedded in a host programming language, as discussed previously, with a call-level interface. Embedded SQL statements in host programming language codes are commonly preceded by EXEC SQL and erminated by a semicolon in many cases. Other termination characters include END-EXEC and the right parenthesis.

The following is an example of embedded SQL in a host program, such as the ANSI C language:

{host programming commands} EXEC SQL {SQL statement};

{more host programming commands}

Possibly related posts: (automatically generated)
Using SQL to Generate SQL

• Network Access Control Databases
• Website Hosting Sever, some Pitfalls you need to avoid part 1
• Scripting Database Objects
• Network and Servers Technical Compare continue...
• Network and Programming: the .Net framework
• Client-Side Scripting
• Using the New Wizards in SQL Server 2000
• System Security Scanner
• What Is Contained in the System Catalog?
• Java Database, J2EE Framework