How to Run Stored Procedure in SQL Developer

Here is a sample of Stored procedures showing how to run it in Oracle. They store in the database. The return keyword you must not use in stored procedures. Why because only functions support the return keyword.

The stored procedure starts with the ‘create or replace procedure [procedure name]. In three steps, you will know how to create/verify/run the procedure.

Stored procedure example

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';

How to call a 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.