12-5. Creating and Executing a Script
Problem
You are interested in creating a PL/SQL script using Oracle SQL Developer that will run against your database. Once it has been created, you want to save it and then execute it.
Solution
Establish a connection to the database for which you want to create a script. By default, the SQL worksheet for the selected database will open. To create a script, choose New from the File menu or select the first icon on the left side of the toolbar that resembles a piece of paper with a plus sign. Next, select the SQL File option from the Create a New window. When the Create SQL File window opens, type in a file name for your script, and choose a directory in which to store it. For the purposes of this demonstration, choose the file name select_employees, browse and choose the desired storage location, and click OK. At this point, a new tab opens in the Oracle SQL Developer editor. This tab represents the SQL file you have just created. Type the following script into the editor for demonstration purposes:
DECLARE
CURSOR emp_cur IS
SELECT * FROM employees;
emp_rec emp_cur%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
END LOOP;
END;
After the script has been typed into the editor, your Oracle SQL Developer editor should resemble that shown in Figure 12-11. Save your script by clicking the Save icon that looks like a disk, or choose Save from the File menu.
Figure 12-11. Typing a script into the SQL editor
To execute the script, click the Run Script icon that is the second icon from the left above the editor, or press the F5 function key. You will be prompted to select a database connection. At this point, you can choose an existing connection, create a new connection, or edit an existing connection. Choose the database connection that coincides with the schema for this book. Once you select the connection, the script will execute against the database, and you will see another pane appear in the lower half of the Oracle SQL Developer window. This is the Script Output pane, and you should see a message that states “anonymous block completed.” The editor should now look like Figure 12-12.
Figure 12-12. Anonymous block completed
How It Works
In the solution to this recipe, you learned how to create and execute a script using Oracle SQL Developer. As you were typing the script, you may have noticed that the text being typed is color-coded. Oracle SQL Developer places PL/SQL and SQL keywords into a different color text that can be chosen from within the preferences window, which is located within the Tools menu. The default color for keywords is blue.
When the script is executed, it prompts for a database connection to use. Once that connection has been selected and established, then the script is run against the database. The script may not display any useful results by default, unless the SERVEROUTPUT has been enabled via the Dbms Output pane. To learn more about enabling DBMS_OUTPUT, please see Recipe 12-3.
When you select the Save option, the script is written to disk to a file having the name you specified earlier. To execute a saved script, open the File menu, and then select the Open option. A dialog box will open that allows you to browse your file system for the script that you want to open. Once you have found the script and opened it, a new tab is opened, and the script is loaded into that tab along with all the options of an ordinary SQL worksheet (see Figure 12-13).
Figure 12-13. Loaded script