Systems Engineering and RDBMS

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)
AS
BEGIN
SELECT object_id
INTO p_object_ID
FROM user_objects
WHERE object_name = p_object_Desc;
EXCEPTION
WHEN OTHERS THEN
p_object_ID := 0;
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END test_proc;
/

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

V_OBJECT_ID
———–
377239

We can even use an anonymous PL/SQL block as well as shown below.

DECLARE
v_object_id NUMBER;
BEGIN
test_proc(‘TEST’,:v_object_id);
dbms_output.put_line(:v_object_id);
END;
/

Command line familiarity is required especially in production environments where most of the times, the GUI tools are not installed.

References:

  • PL/SQL User’s Guide and Reference – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: