SQLPlus Create Procedure

An example to Create a Procedure

SHOW ERRORS

CREATE OR REPLACE PROCEDURE Get_Customer (

– create date:
– create by:
– description:
i_ID IN CUSTOMERS.ID%TYPE,
o_NO OUT IN NUMBER,
o_MODE OUT CUSTOMERS.MODE%TYPE)
IS
TID COM_CUSTOMERS.CCUST_ID%TYPE;

BEGIN

SELECT TID, NO INTO t_ID, o_NO FROM CUSTOMERS WHERE ID = i_ID;
SELECT T.MODE INTO o_MODE FROM OTHER_TABLE T WHERE ID = t_ID;
EXCEPTION WHEN OTHERS THEN DECLARE error_code NUMBER := SQLCODE;
error_msg VARCHAR2 ( 300 ) := SQLERRM;
BEGIN RAISE_APPLICATION_ERROR ( – 20096, ‘SELECT of proc Get_Customer failed: ‘ || ‘(NO: ‘ || i_NO || ‘ ‘ || error_code || ‘ ‘ || error_msg || ‘)’ );

END;

END Get_Customer; /
–