Oracle Stored Procedure

Sample of Oracle stored procedure with in & out params & exceptions.

CREATE OR REPLACE PROCEDURE proc_name (
  p1 IN VARCHAR2, 

  p2 OUT VARCHAR2
) 
AS 
  msg VARCHAR2(30) := 'hello ';
  ex_some_exception EXCEPTION;

BEGIN 

  IF length(p1) < 2 THEN RAISE ex_some_exception;
  END IF;
  p2 := msg || p1;
EXCEPTION 
  WHEN ex_some_exception THEN raise_application_error(-20001, 'name to short');
  WHEN OTHERS THEN raise_application_error(-20002, 'An error was encountered');
END proc_name;
/

Testing in & out params.

SET SERVEROUTPUT ON
 
DECLARE 
  full_hello VARCHAR(50);
BEGIN 
  proc_name('Mike', full_hello);
  DBMS_OUTPUT.put_line(full_hello);
END;
/