Oracle

How to call Oracle stored procedure and how to use OUT parameter

This article explains how to call Oracle stored procedure and how to use OUT parameter. We can call stored procedure in SQL*Plus and some other database tools such as Oracle SQL Developer.

Since there are two ways to call stored procedure, explain both ways. One way is just call stored procedure and the other way is call stored procedure by utilizing anonymous PL/SQL block.

Create a test stored procedure

In order to call stored procedure, let's create a new simple stored procedure. This stored procedure doubles a given value and return the doubled value. The procedure takes IN parameter (PARAM_IN), then the result is stored into OUT parameter (PARAM_OUT).

CREATE OR REPLACE PROCEDURE sample_proc
  (PARAM_IN IN NUMBER, PARAM_OUT OUT NUMBER)
AS
BEGIN
  PARAM_OUT := PARAM_IN * 2;
END;
/

Call stored procedure by using EXEC command

In order to call the stored procedure, we need to define a variable to receive OUT parameter value (VARIABLE x NUMBER). Then, we call the stored procedure by using EXEC command. Once the stored procedure call completed, we display OUT parameter value by using PRINT statement.

-- Define4 variable to receive OUT parameter value
VARIABLE x NUMBER

-- We can initialize the defined variable if necessary
EXEC :x := -1

-- Call stored procedure
EXEC sample_proc(10, :x)

-- Print OUT parameter value
PRINT x

20

Call stored procedure by using anonymous PL/SQL block

We can call stored procedure by using anonymous PL/SQL block. We can put all the commands into anonymous PL/SQL block, this is more handy.

-- Enable standard output of PL/SQL
SET SERVEROUTPUT ON

-- Call stored procedure by using anonymous PL/SQL block
DECLARE
  x NUMBER;
BEGIN
  sample_proc(20, x);
  dbms_output.put_line( x );
END;
/

40

Everything you do is practice

How to call Oracle stored procedure and how to use OUT parameter