Oracle SQL Plus The Definitive Guide- P30:Every day, computer professionals the world over wake up, travel to the office, sit down in front of a computer, and begin another day working with that database called Oracle. Programmers write queries and stored procedures. Database administrators monitor performance, make database changes, and perform other maintenance tasks. Operations people may need to back up or recover a database. | previous page page_260 next page Page 260 SQL BEGIN 2 ANALYZE TABLE employee COMPUTE STATISTICS 3 END 4 ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS ERROR at line 2 ORA-06550 line 2 column 11 PLS-00103 Encountered the symbol TABLE when expecting one of the following . @ With the earliest versions of PL SQL this was a hard-and-fast limitation but now Oracle provides a PL SQL package for executing dynamic SQL statements that you can also use to execute DDL. A dynamic SQL statement is one you build up programmatically and often the exact statement isn t known until runtime. The package name is DBMS_SQL and you can read about it in the Oracle8 Server Application Developer s Guide. There are five steps to using the DBMS_SQL package to execute a DDL statement 1. Allocate a cursor for the statement. 2. Put the statement to be executed in a VARCHAR2 variable. 3. Parse the statement. 4. Execute the statement. 5. Deallocate the cursor. Steps 2 through 4 are repeated for as many statements as you need to execute. The first step is to allocate the cursor. Even though you aren t selecting data all SQL statements are executed using a cursor. The code to do that looks like this ddl_cursor_id The variable ddl_cursor_id should be declared as an INTEGER. This integer is a cursor ID used to refer to the actual cursor which the DBMS_SQL package maintains internally. The call to should go outside the loop. You can use the same cursor for multiple SQL statements so you only need to allocate it once. The next step is to create the SQL statement you want executed. The following statement placed inside the loop will do that analyze_command ANALYZE TABLE II COMPUTE STATISTICS Another good source of information on the DBMS_SQL package is Oracle Built-in Packages by Steve Feuerstein Charles Dye and John Beresniewicz O Reilly Associates 1998 . Not only does this book cover DBMS_SQL it also covers all the other standard packages .
