Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...