How to Run Stored Procedure in SQL Developer

Here are the steps to run the stored procedure in Oracle. They reside in the database. The return keyword you must not use in stored procedures. Why because only functions support the return keyword.

The stored procedure begins with create or replace procedure [procedure name]. You’ll learn in three steps create/verify/run the procedure.

Create Stored procedure

create or replace procedure mytestproc (in_dt DATE)
is
    v_out_tx VARCHAR2(10);
    v_flag_b BOOLEAN;
begin
    if to_char(in_dt,'d')=1 then
        v_out_tx:='Y';
        dbms_output.put_line(v_out_tx);
    else
        v_out_tx:='N';
        dbms_output.put_line(v_out_tx);
    end if;
end;

Input for the procedure, you can supply in the parenthesis ( the variables and their data types). The other variables you can declare in the declaration section ( after the ‘is’).

Verify stored procedure is created

Here is a sample SQL query to verify if the procedure is created.

select * 
  from user_procedures 
 where object_type='PROCEDURE'; 

Call stored procedure

Now, this is the time to call. The CALL keyword is the first one you need to give. Then supply the date. The to_char function checks the day of the week supplied is numeric (since the ‘d’ checks for day of the week).

If the Date is numeric, you can see Y else N.

SQL query to call procedure

 CALL mytestproc('27-NOV-21');

The output

N

Related posts