If you are working with Oracle, you will often need to execute PL/SQL blocks to test logic, fetch data, or print results. Running a PL/SQL block is easy, and you can do it in different ways depending on which tool you are using. Here are a few quick examples and extra details to help you run PL/SQL code blocks in SQL*Plus, Toad, and SQL Developer.
1. Execute PL/SQL Block in SQL*Plus
SQLPlus is the traditional command-line tool for Oracle. To run a PL/SQL block here, you just need to put a forward slash (/) on a new line after your code and press Enter. This tells SQLPlus to execute everything above the slash as a single block.
Here is a simple example that fetches and prints an employee name:
set serveroutput on;
DECLARE
v_empno emp.empno%TYPE := 7369;
v_ename emp.ename%TYPE;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = v_empno;
DBMS_OUTPUT.put_line(v_ename);
END;
/
When you run this, you will get output like:
SMITH PL/SQL procedure successfully completed.
The set serveroutput on; command is important because it enables the output from DBMS_OUTPUT.put_line to show up in your session. If you forget this, nothing will print even if your code is correct.
You can run any PL/SQL block this way—just remember the slash at the end. If you have more than one block, put a slash after each one.
2. Execute PL/SQL Block in Toad for Oracle
Toad for Oracle is a popular tool with a nice graphical interface. Running PL/SQL code in Toad is even easier. You just have to write or paste your code in the editor window, put your cursor anywhere in the block, and press the right key.
- Press F5 to run the block as a script. You will see the output in the Script Output panel.
- Press F9 to run the block as a single statement. The result will show in the DBMS Output panel (if enabled).
To see the output from DBMS_OUTPUT.put_line, make sure the DBMS Output window is open. Just click on the DBMS Output tab and click the green plus button to start capturing output for your session. Now, when you run your PL/SQL block, you’ll see the output text.
You can use the same PL/SQL code as in the SQL*Plus example. Toad will run it the same way.
3. Execute PL/SQL Block in SQL Developer
Oracle SQL Developer is another free and widely used tool for working with Oracle databases. It works a lot like Toad, but the keys are a little different.
- Press Ctrl+Enter to run the current PL/SQL block.
- Press F5 to run the entire worksheet as a script, which is helpful if you have more than one block of code.
As in Toad, open the DBMS Output panel (you can find it under the View menu if it’s not already open) and click the green plus icon to start capturing output for your session. Now, when your code hits a DBMS_OUTPUT.put_line, you will see the result in this panel.
Here is an example of what you might run:
set serveroutput on;
DECLARE
v_empno emp.empno%TYPE := 7566;
v_ename emp.ename%TYPE;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = v_empno;
DBMS_OUTPUT.put_line(v_ename);
END;
/
You can change the employee number to check for different employees, or add more logic as needed.
Some Extra Tips
- Always use
set serveroutput on;in SQL*Plus to see output. In Toad and SQL Developer, make sure DBMS Output is enabled. - Use meaningful variable names to keep your code easy to read.
- You can include exception blocks to handle errors, like this:
BEGIN
-- your code
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Something went wrong: ' || SQLERRM);
END;
/
- Remember, in SQL*Plus, the slash (
/) is needed to run your block, but in Toad and SQL Developer, you just use the shortcut keys.
Running PL/SQL blocks is a basic but important part of using Oracle. Whether you use SQL*Plus, Toad, or SQL Developer, you can quickly test your code, print results, and troubleshoot problems using these simple steps. Once you are comfortable with this, you can start writing more complex blocks, procedures, and functions to do even more with your data.
