How to execute procedure in oracle

Procedure is PLSQL code stored in Oracle database. Here in this post, let’s see how to execute the procedure in oracle, how to execute procedure in oracle with output parameter

How to execute procedure in oracle

Let’s first create a simple procedure to demonstrate with an example

create or replace procedure proc1 is
l_count number;
begin
select count(*) into l_count from DEPT_MASTER;
dbms_output.put_line( 'Count is '|| l_count);
end;
/

This procedure is a simple procedure that just prints the number of rows in the DEPT_MASTER Table. You can execute this in the below ways in SQLPLUS

exec proc1;

or

begin
proc1;
end;
/

It will not show the output from dbms_output.put_line, You will need to set server output on before executing the procedure

set serveroutput on
exec proc1; 
or 
begin 
proc1; 
end; 
/

How to execute procedure in oracle with parameters

Let’s first create a simple procedure with parameters to demonstrate it

create or replace procedure proc2 (p_dept_id Number) is
l_count number;
begin
select count(*) into l_count from DEPT_MASTER where dept_id=p_dept_id;
dbms_output.put_line( 'Count is '|| l_count);
end;
/

This procedure is a simple procedure that just prints the number of rows in the DEPT_MASTER Table where dept_id is the input parameter given. This procedure proc2 can be executed as

set serveroutput on 
exec proc2(10); 
 or  
begin  
proc2;  
end;  
/

How to execute procedure in oracle with out parameters

The earlier procedure requires the IN parameter. Sometime procedures might be having OUT parameters also. Let’s see an example to demonstrate it

CREATE OR REPLACE PROCEDURE PROC3(p_user_id IN NUMBER, p_username OUT VARCHAR2(30))
AS 
BEGIN
SELECT username INTO p_username FROM FND_USERS WHERE user_id = p_user_id;
END;
/

Desc proc3
proc3
_______________________________________
p_user_id          NUMBER  IN
p_username         VARCHAR2(30) OUT
_______________________________________

This procedure is a simple procedure that just prints the username for the given user_id from the FND_USERS table in Oracle Apps. You can execute this procedure as

SQL>variable v1 varchar2(30)
SQL> exec proc3(10,:v1);
or
begin  
proc3(10,:v1);  
 end;  
 /
print :v1;

Or you can execute an anonymous procedure as given below

set serveroutput on
DECLARE
p_username VARCHAR2(30);
BEGIN
proc3(100, p_username);
dbms_output.put_line(p_username);
END;
/

How to execute procedure in SQL Developer

You can search the Procedure in the Procedure drop-down and then right-click and you will get the option to run it

Hope you like this post on executing procedures in oracle and it helps in your day-to-day activities. Please do provide the feedback to Improve

Related Articles

1. Oracle PLSQL Block Structure and Oracle PLSQL Variable
2. PLSQL records
3. PLSQL Tables (Associative array or index-by table)
4. How to use google translate URL in Oracle plsql
5. Oracle cursor
6. Most commonly asked 25 oracle plsql interview questions