You are interested in creating an executable block of PL/SQL code.

Write the keywords BEGIN and END. Place your lines of code between those two keywords. Here's an example:

If you want to introduce variables for your PL/SQL block, you must precede your block with a DECLARE section. Here's an example:

You want to execute a block of PL/SQL code within the SQL*Plus command-line utility.

The solution to this recipe is multitiered, in that executing a block of code in SQL*Plus incorporates at least two steps:

Enter the PL/SQL code into the SQL*Plus utility.

Execute the code block by simply placing a backslash (/) as the last line of code, and then press the Enter key.

The following is an example displaying a code block that has been typed into SQL*Plus:

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.

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:

Now you have a file that you can execute from SQL*Plus any time you want to count down from ten to zero.

You have stored an SQL script to your file system and want to execute it in SQL*Plus.

Assume you have a stored script named my_stored_script.sql and that it is saved within a directory named /Oracle/scripts/. You can execute that script using any one of the following approaches:

• Traverse into the directory containing the script, then connect to a database via SQL*Plus, and finally issue the following command:@my_stored_script.sql
• Open the command line or terminal, connect to the database via SQL*Plus, and issue the following command:@/Oracle/scripts/my_stored_script.sql
• Open command line or terminal, and issue the following command:sqlplus username/password@database my_stored_script.sql
• 
  
  

You want to write a script that prompts the user for some input. You want your PL/SQL code to then use that input to generate some results.

SQL*Plus provides a facility to accept user input. Use ampersand (&) character to indicate that a particular value should be entered from the keyboard. Here's an example:

If the previous block is executed from SQL*Plus, you will see the following text, which prompts you to enter a department ID. In this case, the department ID of 40 is used.