Back to the basics: Testing procedure with output parameters using SQL*Plus
Posted by decipherinfosys on March 18, 2009
Yesterday at a client site, one of the junior developers asked how can she execute a stored procedure with output parameter(s) from a SQL*Plus session. She was familiar using GUI interface like TOAD or PL/SQL Developer but none of those tools were available to her in this company. In this blog post, we are going to show a couple of ways one can test the procedure that has an output parameter in it’s definition. Let us first create a procedure:
CREATE OR REPLACE PROCEDURE test_proc(p_object_desc IN VARCHAR2, p_object_ID OUT NUMBER)
WHERE object_name = p_object_Desc;
WHEN OTHERS THEN
p_object_ID := 0;
For output variable, we have to first declare a variable in the current SQL*Plus session and then execute the procedure. In order to see the value of an output variable, we can use the print command.
SQL> variable v_object_id NUMBER;
SQL> EXEC test_proc(‘TEST’,:v_object_id);
PL/SQL procedure successfully completed.
SQL> print :v_object_id
We can even use an anonymous PL/SQL block as well as shown below.
Command line familiarity is required especially in production environments where most of the times, the GUI tools are not installed.
- PL/SQL User’s Guide and Reference – here.