Oracle SQL Plus The Definitive Guide- P29: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_251 next page previous page page_252 next page Page 252 --Now execute the query that we just spooled. @user_security_choice --Reset all the settings back to their defaults SET FEEDBACK ON CLEAR COLUMNS TTITLE OFF You have to be very careful when using this technique to turn off anything that could cause extraneous text to be written to the temporary command file. This includes page headings column headings and verification. That s why the script in the example included these commands SET TERMOUT OFF SET PAGESIZE 0 SET HEADING OFF SET VERIFY OFF Terminal output was turned off to prevent the user from seeing the results of the SELECT on the display. One last thing you have to worry about is the filename itself. In the example shown above the filename is hardwired into the script and does not include a path. Because no path is specified the file will be written to the current directory. That s why a single ampersand or @ was used to run the intermediate file. Using @ causes SQL Plus to look in the current directory for the script. Having the filename hardwired into the script can cause problems if multiple users execute the script at the same time and from the same directory. If you are concerned about this you could write some SQL or PL SQL code to generate a unique filename based on the Oracle username or perhaps the session identifier SID from the V SESSION view. Be creative with this technique. You don t need to limit yourself to writing SQL Plus scripts either. You can use SQL Plus to generate shell script files SQL PLoader files DOS batch files or any other type of text file. Using PL SQL Always consider the possibility of using PL SQL to implement any type of complex procedural logic. After all that s the reason PL SQL was invented in the first place. If you can manage to prompt the user up front for any needed information and if you don t need to interact with the user during the operation PL SQL is the way to go. page_252 previous page .
