1-3. Storing Code in a Script
Problem
Rather than typing your PL/SQL code into the SQL*Plus utility each time you want to run it, you want to store the code in an executable script.
Solution
Open your favorite text editor or development environment; type the PL/SQL code into a new file, and save the file using the .sql extension. The script can contain any number of PL/SQL statements, but the last line of the script must be a forward slash (/).
For example, you could place the following lines into a file named count_down.sql:
SET SERVEROUTPUT ON;
DECLARE
counter NUMBER;
BEGIN
FOR counter IN REVERSE 0..10 LOOP
DBMS_OUTPUT.PUT_LINE (counter);
END LOOP;
END;
Now you have a file that you can execute from SQL*Plus any time you want to count down from ten to zero.
How It Works
You can basically use any text editor or development environment to create and save your script. The key is to ensure that the file extension on the saved script is .sql so that SQL development environments and other developers recognize it as a stored SQL script. SQL Developer supports a number of additional extensions for more specific types of PL/SQL. To learn more about using SQL Developer, please see Chapter 12. Once the script has been stored, it can be executed within SQL*Plus. See the next recipe for details on doing that.
Note The line SET SERVEROUTPUT ON at the beginning of the script is an important detail. That command instructs SQL*Plus to look for and display any output from DBMS_OUTPUT.PUT_LINE. A common mistake is to omit the SET SERVEROUTPUT ON command and then be left wondering why you don't see any output.